PostgreSQL basic commands and Linux under PostgreSQL commands

Source: Internet
Author: User
Tags postgresql commands psql

Transferred from:

(1) User utility program:
Createdb Create a new PostgreSQL database (and the SQL statement: Create DATABASE is the same)
CreateUser Create a new PostgreSQL user (and SQL statement: Create user is the same)
DROPDB Deleting a database
Dropuser Deleting a user
Pg_dump to export the PostgreSQL database to a script file
Pg_dumpall Export all PostgreSQL databases to a script file
Pg_restore recovering a PostgreSQL database from a script file exported by the Pg_dump or Pg_dumpall program
Psql a command line-based PostgreSQL Interactive client program
Vacuumdb cleans and analyzes a PostgreSQL database, which is the shell script wrapper for the SQL statement vacuum in the client program Psql environment, and the two functions are identical
(2) System utility
INITDB Create a PostgreSQL data directory to store the database and create a predefined template database Template0 and template1 to generate a shared directory table catalog; This program is typically run only once when PostgreSQL is installed
Initlocation Creating a secondary PostgreSQL database storage area
Ipcclean clearing shared intrinsic and isolated signal flags from a stopped PostgreSQL server
Pg_ctl start, stop, restart the PostgreSQL service (for example: Pg_ctl start PostgreSQL Service, which is the same as service PostgreSQL start)
Pg_controldata displaying internal control information for PostgreSQL services
Postgres PostgreSQL Single-User mode database service
Postmaster PostgreSQL Multi-user mode database service
4. The most important thing here is that Psql is the most important client program. The way to enable client program Psql is:
Switch to PostgreSQL's pre-defined database Superuser Postgres, enable client program Psql, and connect to the database you want, such as:
Psql template1
The following interface appears, indicating that you have entered the desired database and can do the desired action.
5. Some of the commands in the database:
template1=# \l Viewing existing databases in the system
template1=# \q Exit client program Psql
template1=# \c transfer from one database to another, such as template1=# \c sales from template1 to sales
template1=# \DT View Table
template1=# \d View Table structure
template1=# \di View Index
[Basic Database Operations]========================
Create DATABASE [DB name];
* View Database list:
* Delete database:
drop database [DB name];
To create a table:
Create TABLE ([field name 1] [Type 1] <references associated table name (associated field name) >;,[field Name 2] [type 2],...... <,primary key (field name m, field name N,...) >;);
* View list of table names:
* View the status of a table:
\d [table name]
* Rename a table:
ALTER TABLE [table name a] rename to [table name B];
* Delete a table:
drop table [table name];
[Basic operation in table]==========================
* Add fields to existing tables:
ALTER TABLE [table name] Add column [Field name] [type];
* Delete the fields in the table:
ALTER TABLE [table name] Drop column [field name];
* Rename a field:
ALTER TABLE [table name] Rename column [field name a] to [field name B];
* Set a default value for a field:
ALTER TABLE [table name] ALTER column [field name] Set default [new default value];
* Remove default values:
ALTER TABLE [table name] ALTER column [Field name] drop default;
To insert data into a table:
Insert into table name ([Field name m],[field name N],......) VALUES ([column M's Value],[column N's value],......);
To modify the data for a column of a row in a table:
Update [table name] set [Target Field name]=[target value] where [character of the row];
To delete a row of data from a table:
Delete from [table name] where [characteristic of the row];
Delete from [table name];--erase entire table
6. Be careful to clean up the database at any time, reclaim disk space and update statistics, use the following command to get it done!
Vaccumdb-d sales-z
-A For all database operations
-Z guarantees continuous deletion of failed rows, saves disk space, updates statistics to the nearest state
7.PostgreSQL user authentication
The role of pg_hba.conf in the PostgreSQL data Catalog is user authentication and can be found in the/var/lib/pgsql/data.
Here are a few examples to look at:
(1) allows any database to be connected to any of the identities on this computer
TYPE DATABASE USER ip-address ip-mask METHOD
Local all All trust (unconditional connection)
(2) Allow any host with IP address 192.168.1.x to connect to the database sales
TYPE DATABASE USER ip-address ip-mask METHOD
Host sales all ident Sameuser (indicates that any operating system user can connect with a database user of the same name)
8. See so much, come up with a complete example of creating a PostgreSQL database user
(1) Access to PostgreSQL advanced users
(2) Enable the client program and enter the TEMPLATE1 database
Psql template1
(3) Create a user
template1=# CREATE USER Hellen with encryped PASSWORD ' Zhenzhen '
(4) Because the password is set, the pg_hba.conf is edited so that the user and the configuration file are synchronized.
Add MD5 to the original record
Local all Hellen MD5
(4) Log in to the database with a new user
template1=# \q
Psql-u hellen-d template1
PS: In a database if you want to switch users, use the following command:
template1=# \!psql-u tk-d template1
9. Set user-specific permissions
Or use an example to illustrate:
Create a user group:
sales=# CREATE GROUP Sale;
Add several users into the group
sales=# ALTER GROUP Sale ADD USER sale1,sale2,sale3;
Grant user-level Sale SELECT permissions for table employee and products
sales=# GRANT SELECT on employee,products to GROUP sale;
Delete user User2 in Sale
sales=# ALTER grop Sale DROP USER sale2;
10. Backing Up the database
Can be done using pg_dump and Pg_dumpall. For example, back up the sales database:
Pg_dump Sales>/home/tk/pgsql/backup/1.bak

PostgreSQL basic commands and Linux under PostgreSQL commands

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: 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.