PostgreSQL basic commands and Linux under PostgreSQL commands

Transferred from: http://blog.csdn.net/qpengyanting123/article/details/7497556

(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.
template1=#
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:
Create DATABASE [DB name];
* View Database list:
\d
* 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:
\d
* 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 192.168.1.0 255.255.255.0 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