Basic PostgreSQL commands and PostgreSQL commands in Linux

(1) User PracticalityProgram:

Createdb creates a new PostgreSQL database (same as SQL statement: Create Database)

Createuser creates a new PostgreSQL user (same as SQL statement: create user)

Dropdb deletes a database

Dropuser: delete a user

Pg_dump exports the PostgreSQL database to a script file

Pg_dumpall: export all PostgreSQL databases to one script file.

Pg_restore restores the PostgreSQL database from a script file exported by pg_dump or pg_dumpall.

Psql: a PostgreSQL Interactive Client Program Based on command line

Vacuumdb clears and analyzes a PostgreSQL database. It is the shell script encapsulation of the SQL statement vacuum in the Psql environment of the client program. The two functions are identical.

(2) system utilities

Initdb creates a PostgreSQL data directory for storing the database, and creates a predefined template database template0 and template1 to generate a shared directory table catalog. This program is generally run only once when PostgreSQL is installed.

Initlocation creates a secondary PostgreSQL database storage Region

Ipcclean removes internal and isolated signal sharing signs from the stopped PostgreSQL Server

Pg_ctl start, stop, and restart the postgresql service (for example, pg_ctl start starts the postgresql service, which is the same as service PostgreSQL start)

Pg_controldata displays the internal control information of the postgresql service

Postgres PostgreSQL single-user database service

Postmaster PostgreSQL multi-user database service

4. Psql is the most important client program. To enable Psql for a client program, follow these steps:

Switch to PostgreSQL's pre-defined database superuser Postgres, enable the client program Psql, and connect to the desired database, for example:

Psql template1

The following interface is displayed, indicating that you have entered the desired database and can perform the desired operation.

Template1 = #

5. Some commands in the database:

Template1 =#\ l view existing databases in the system

Template1 =#\ Q exit the client program Psql

Template1 =#\ C transfers data from one database to another, for example, template1 =#\ C sales transfers data from template1 to sales

Template1 =#\ dt

Template1 =#\ d view the table structure

Template1 =#\ di View index
[Basic database operations] ============================
* Create a database:
Create Database [database name];

* View the Database List:
\ D

* Delete A database:
Drop database [database name];

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 the table name list:
\ 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 operations in a table] ============================
* Add fields in the existing table:
Alter table [Table name] add column [field name] [type];

* Delete 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 the default value for a field:
Alter table [Table name] alter column [field name] Set default [new default value];

* Remove the default value:
Alter table [Table name] alter column [field name] Drop default;

Insert data into the table:
Insert into Table Name ([field name M], [field name N],...) values ([column M value], [column N value],...);

Modify the data of a column in a row in a table:
Update [Table name] Set [target field name] = [target value] Where [feature of this row];

Delete a row of data in a table:
Delete from [Table name] Where [features of this row];
Delete from [Table name]; -- delete the entire table

6. Be sure to clean up the database, reclaim disk space, and update statistics at any time. Use the following command!

Vaccumdb-D sales-z

-A: All Database Operations

-Z ensures that invalid rows are constantly deleted, saves disk space, and updates statistics to the latest status.

7. PostgreSQL User Authentication

Pg_cmd.conf In the PostgreSQL data directory is used for user authentication, which can be found in/var/lib/pgsql/data.

You can take a look at the following examples:

(1) allow any identity on the local machine to connect to any database

Type Database User IP-ADDRESS IP-MASK Method

Local all trust (unconditional connection)

(2) allow any host whose IP address is 192.168.1.x to connect to the sales database

Type Database User IP-ADDRESS IP-MASK Method

Host sales all 192.168.1.0 255.255.255.0 ident sameuser (indicating that any operating system user can connect to a database user with the same name)

8. After reading so much, let's take a complete example of creating a PostgreSQL database user.

(1) Access 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, you need to edit pg_mirror.conf to synchronize the user and the configuration file.

Add MD5 to the original record

Local all hellen MD5

(4) log on to the database with a new user

Template1 =#\ Q

Psql-u hellen-D template1

PS: To switch users in a database, run the following command:

Template1 = #\! Psql-u TK-D template1

9. Set specific User Permissions

The following example is used to describe:

Create a user group:

Sales = # create group sale;

Add several users to 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. Back up the database

You can use pg_dump and pg_dumpall. For example, back up the sales database:

Pg_dump sales>/home/tk/pgsql/backup/1.bak