Database connection:
Way One:
Psql-u test_huishishuju-d Test_huishishuju
Parameter description:-u database user name-D database name
Way two:
Su–postgres
-bash-4.2$ psql 进入postgres数据库
Database User management:
Create user
postgres=# Create user Dev_huishishuju; or create role Dev_huishishuju; Create a database user
postgres=# alter user Dev_huishishuju with password ' 123456 '; Modify the password for the database user to 123456
Set user Permissions
? Basic Syntax format
Create or alter ROLE role_name with Optional_permissions (user's authorization);
Instance:
postgres=# alter user dev_huishishuju with Superuser; Modify the user's properties, set the database user Dev_huishishuju to Superuser
postgres=# alter user Dev_huishishuju with login; Set login permissions for database users
? The syntax format for setting user permissions is as follows:
GRANT Permission_type on table_name to role_name;
Instance:
dev_huishishuju=# Grant Update on t_sms to Rd_huishishuju; gives user Rd_huishishuju update permission to t_sms table
postgres=# Grant SELECT on all tables in the schema public to Rd_huishishuju; Gives the Rd_huishishuju user the ability to query for all tables
dev_huishishuju=# grant all on T_admin to Rd_huishishuju; Rd_huishishuju user is given T_ The Admin database table has all permissions
Common operations for entering a database:
test_huishishuju=> \help help command
test_huishishuju=> \l Display detailed database information
test_huishishuju=> \c postgres Switch postgres database
test_huishishuju=> \dt or \d lists the tables in the database, which is equivalent to show databases
test_huishishuju=> \d T_ SMS view the structure of the T_sms table
test_huishishuju=> \di View the index of the database table
test_huishishuju-# \DU Lists all user information
test_huishishuju-# \ Connect lists connection information for the current database
test_huishishuju-# \conninfo lists the details of the current database and connections
test_huishishuju=# \q exiting the current operation
Dev_ huishishuju=# \DP or \z displays the user's detailed access rights to all database tables
Database management:
postgres=# CREATE DATABASE Beiyi; Drop database Beiyi; Delete database Beiyi
postgres=# ALTER DATABASE Dev_huishishuju OWNER to Dev_huishishuju; Modify user Attribution
Table Management:
postgres=# \c Beiyi Switch to Beiyi database
beiyi=# CREATE TABLE User_beiyi (name VARCHAR), signup_date date); Create a User_beiyi table
beiyi=# INSERT into User_beiyi (name, signup_date) VALUES (' Zhang San ', ' 2013-12-22 '); Inserting data into the User_beiyi table
test_huishishuju-# ALTER TABLE test_huishishuju add column name character varying (16); Add field in table Test_huishishuju, Description: Name is field name, character varying (16) is field type
beiyi-# Dorp Table User_beiyi Delete database tables User_beiyi
Backup and restore 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 user, restore must be guaranteed to have permission
Backup and restore of databases
Pg_dump-h 192.168.2.242-u postgres-p 5432-c--if-exists dev_huishishuju > Dev_huishishuju.sql backup 192.168.2.242 D on the database Ev_huishishuju Database
-bash-4.2$ psql-f dev_huishishuju.sql-d Dev_huishishuju Restore the database, dev_huishishuju.sql the backup file, Dev_huishishuju the newly created database
PostgreSQL Database General Operations Management