PostgreSQL Database General Operations Management

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