PostgreSQL Database General Operations Management

Source: Internet
Author: User
Tags postgresql psql postgres database

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.