Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.