General Operation and Management of postgresql Database
Database connection:
Method 1:
Psql-U test_huishishuju-d test_huishishuju
Parameter description:-U database user name-d database name
Method 2:
Su-postgres-bash-4.2$ psql enters the postgres database
Database user Management:
Create a user
Postgres=# create user dev_huishishuju; or create role dev_huishishuju; create a database user
Postgres=# alter user dev_huishishuju with password '123456; change the password of the database user to 123456
Set user permissions
Basic grammatical format
CREATE or ALTER ROLE role_name WITH optional_permissions (user's authorization)
Example:
Postgres=# alter user dev_huishishuju with superuser; modifies the attributes of the user and sets the database user dev_huishishuju to superuser
Postgres=# alter user dev_huishishuju with login; sets login permissions to database users
The syntax format for setting user permissions is as follows:
GRANT permission_type ON table_name TO role_name
Example:
Dev_huishishuju=# grant UPDATE ON t_sms TO rd_huishishuju; give the user rd_huishishuju update permissions on the t_sms table
Postgres=# grant SELECT on ALL tables in schema public to rd_huishishuju; give rd_huishishuju users query permissions on all tables
Dev_huishishuju=# grant ALL ON t_admin to rd_huishishuju; give rd_huishishuju users full permissions on t_admin database tables
Common operations to enter the database:
Test_huishishuju= >\ help help command
Test_huishishuju= >\ l displays detailed database information
Test_huishishuju= >\ c postgres switch postgres database
Test_huishishuju= >\ dt or\ d enumerate the tables in the database, which is equivalent to show databases
Test_huishishuju= >\ d t_sms to view the structure of the tSMs table
Test_huishishuju= >\ di view the index of the database table
Test_huishishuju-#\ du lists all user information
Test_huishishuju-#\ connect lists the connection information for the current database
Test_huishishuju-#\ conninfo lists the current database and connection details
Test_huishishuju=#\ Q exit the current operation
Dev_huishishuju=#\ dp or\ z displays the user's detailed access to all database tables
Database Management:
Postgres=# create database beiyi; creates a database beiyi
Postgres=# drop database beiyi; delete database beiyi
Postgres=# alter database dev_huishishuju OWNER TO dev_huishishuju; modify the user ownership of the database
Table Management:
Postgres=#\ c beiyi switch to beiyi database
Beiyi=# create table user_beiyi (name VARCHAR (20), signup_date DATE); create user_beiyi table
Beiyi=# INSERT INTO user_beiyi (name, signup_date) VALUES ('Zhang San', '2013-12-22'); insert data into user_ Beiyi table
Test_huishishuju-# alter table test_huishishuju add column name character varying (16); add a field to the table test_huishishuju, indicating that name is the field name and character varying (16) is the field type
Beiyi-# dorp table user_beiyi deletes the database table user_beiyi
Backup and recovery of database tables:
# pg_dump-h 192.168.2.242-U postgres-p 5432-t t_sms dev_huishishuju > t_sms.db
Pg_dump-h 192.168.2.242-U postgres-p 5432-c-- if-exists-t t_sms dev_huishishuju > t_sms.db remote backup
-bash-4.2$ psql-f t_sms.db-d test_huishishuju switch postgres users. You must have permission to restore.
Backup and restore of Database
Pg_dump-h 192.168.2.242-U postgres-p 5432-c-- if-exists dev_huishishuju > dev_huishishuju.sql backup dev_huishishuju database on 192.168.2.242 database
-bash-4.2$ psql-f dev_huishishuju.sql-d dev_huishishuju restore the database, dev_huishishuju.sql is the backup file, dev_huishishuju is the newly created database
