PostgreSQL tutorial (18): client commands (2), postgresql tutorial

PostgreSQL tutorial (18): client commands (2), postgresql tutorial

VII. pg_dump:

Pg_dump is a tool used to back up PostgreSQL databases. It can even perform a full and consistent backup when the database is being used concurrently without blocking access to the database by other users. The dump format generated by this tool can be divided into two types: script and archive file. The script format is a plain text format that contains many SQL commands. These SQL commands can be used to reconstruct the database and restore it to the status when the script is generated, this operation requires psql. The archive format must be used together with the pg_restore tool to reconstruct the database. During the reconstruction process, you can select the objects to be restored, or even re-sort the items to be restored before restoration. The command is used as follows:
 Copy codeThe Code is as follows:
Pg_dump [option...] [dbname]
 
1. List of command line options:

Option	Description
-A (-- data-only)	Only output data, not output mode (data object definition ). This option only makes sense for plain text format. For the archive format, you can specify the option when calling pg_restore.
-B (-- blobs)	Dump contains large objects.
-C (-- clean)	Before outputting the SQL command for creating a database object, output the SQL command for deleting the database object. This option only makes sense for plain text format. For the archive format, you can specify the option when calling pg_restore.
-C (-- create)	Output the command for creating a database, and then reconnect to the newly created database. For scripts in this format, it is not so important to connect to a database before running. This option only makes sense for plain text format. For the archive format, you can specify the option when calling pg_restore.
-Eencoding	Creates the dump file with the specified character set.
-Ffile	Output to the specified file. If this option is not available, it is output to the standard output.
-Fformat	P (plain): SQL script file in plain text format (default ).C (custom): The output is applicable to the custom archiving format of pg_restore. This is the most flexible format and allows you to rearrange the loaded data and object definitions. This format is compressed by default.T (tar): Output tar archive files suitable for pg_restore. This archive allows you to re-sort and/or exclude database objects when restoring the database. At the same time as I, you may also be able to limit which data to be restored during recovery.
-N schema	Only the contents of the schema are dumped. If this option is not declared, all non-system modes in the target database will be dumped. This option can also be specified multiple times to specify different pattern patterns.
-Nschema	The contents matching the schema are not dumped. Other rules are consistent with-n.
-O (-- oids)	As a part of the data, each table outputs an object ID (OID ).
-O (-- no-owner)	SQL commands for setting object ownership are not output.
-S (-- schema-only)	Only output object definitions (modes) without output data.
-Susername	Specify the Super User name used to close the trigger. It is related only when -- disable-triggers is used.
-Ttable	Only output table data. It is very likely that there are multiple tables with the same name in different modes. If so, all matching tables will be dumped. You can dump multiple tables at a time by specifying this parameter multiple times. You can also specify the same pattern as psql to match more tables.(For pattern, the basic usage is to treat it as a unix wildcard, that is, * represents any character ,? Represents any single character, and. (dot) represents the separator between schema and object. For example, a *. B * represents the schema starting with a and the database object starting with B. If no. (dot) exists, it only indicates the database object. Here, you can also use a basic regular expression, such as [0-9] To represent numbers .)
-Ttable	Exclude the specified table. Other rules are consistent with the-t option.
-X (-- no-privileges)	Do not export access permission information (grant/revoke command ).
-Z0.9	Declare the compression level used in formats that support compression. (Currently, only custom formats support compression)
-- Column-inserts	The insert into table_name (columns_list) values (values_list) command is used to export data. This operation is relatively slow compared with other operations, but in special cases, for example, the position of fields in the data table may change or new fields may be inserted in the middle of the original field list. Columns_list is explicitly specified, so no data is imported into the error field during import.
-- Inserts	The exported data is represented by the insert command instead of the copy command. Even if insert is slower than copy, it makes sense to import data to other non-PostgreSQL databases in the future.
-- No-tablespaces	The command to set the tablespace is not output. With this option, all objects will be restored to the default tablespace when pg_restore is executed.
-- No-unlogged-table-data	Data of a data table that is not included in the log (unlogged) is not exported. Whether to export its Schema information depends on other options.
-H (-- host = host)	Specify the Host Name of the PostgreSQL server.
-P (-- port = port)	Specifies the listening port of the server. If this parameter is not specified, the default port 5432 is used.
-U (-- username = username)	The login user name for this operation. If the-O option is not specified, the Owner of this database will be the login user.
-W (-- no-password)	If the current logon user does not have a password, you can specify this option to log on directly.

2. Application Example:
Copy codeThe Code is as follows:
#-H: the PostgreSQL server host is 192.168.149.20.
#-U: the logon user is ipvs.
#-T: export a table whose name starts with "test", for example, testtable.
#-A: only export data, not the schema information of the object.
#-F: the output file is my_dump. SQL in the current directory.
# Mydatabase is the target database for this operation.
/> Pg_dump-h 192.168.149.20.- U postgres-t test *-a-f./my_dump. SQL mydatabase
#-C: first, output the SQL command for deleting database objects and then output the SQL command for creating database objects. This is very convenient for deploying a clean initial system or building a test environment.
/> Pg_dump-h 192.168.220.136-U postgres-c-f./my_dump. SQL mydatabase
# Export the information of the mydatabase. You can re-specify the database when using the psql command for import, such as:/> psql-d newdb-f my_dump. SQL
/> Pg_dump-h 192.168.220.136-U postgres-f./my_dump. SQL mydatabase
# The export mode is my_schema and the name of the database object starting with test, but does not include the my_schema.employee_log object.
/> Pg_dump-t 'my _ schema. test * '-T my_schema.employee_log mydatabase> my_dump. SQL
# Export all database objects in east and west modes. The following two commands are equivalent, but the latter uses regular expressions.
/> Pg_dump-n 'east'-n' West' mydatabase-f my_dump. SQL
/> Pg_dump-n '(east | west) 'mydatabase-f my_dump. SQL

8. pg_restore:

Pg_restore is used to restore any non-plain text files exported by pg_dump. it recreates the database and saves it. For archive files, pg_restore can choose to restore, or even rearrange the data order before recovery.

Pg_restore can be operated in two modes. If a database is specified, the archive will be restored directly to the database. Otherwise, you must create a database manually and then restore the data to the new database through pg_restore. The command is used as follows:
 Copy codeThe Code is as follows:
Pg_restore [option...] [filename]
 
1. List of command line options:

 

Option	Description
Filename	Specifies the backup file to be restored. If it is not declared, standard input is used.
-A (-- data-only)	Only data is restored, but table mode is not restored (data object definition ).
-C (-- clean)	Clear (delete) database objects before creating them.
-C (-- create)	Create a database before restoring it. (When this option is used, the DATABASE name must be specified by the-d option. This option is only used to execute the most basic create database Command. It must be noted that all data in the archive file will be restored to the specified database in the archive file ).
-Ddbname	Establish a connection with the database dbname and directly restore data to the database.
-E (-- exit-on-error)	If an error occurs when sending SQL commands to the database, exit. By default, execution continues and an error count is displayed at the end of recovery.
-Fformat	Specifies the format of the backup file. Because pg_restore automatically determines the format, the specified format is not required. If specified, it can be one of the following formats:T (tar): This format allows you to re-sort and/or exclude the table mode information when restoring the database. It is also possible to recover the data loaded within the time limit.C (custom): The custom format is from pg_dump. This is the most flexible format, because it allows you to re-Sort data and reload table mode information. By default, this format is compressed.
-I index	Only the specified index is restored.
-L (-- list)	List the backup content. The output of this operation can be used as the input of the-L option. Note that if the filter option-n or-t is used together with the-l option, they will also limit the entries listed.
-L list-file	Only the entries listed in list-file are restored. The order of recovery is the order in which each entry appears in the file. You can also manually edit the file, and re-arrange the positions of these items before resuming the restoration operation. The behavior comment lines starting with semicolons (;) will not be imported.
-N namespace	Only the database objects in the specified Schema are restored. This option can be used together with the-t option to restore the specified data object.
-O (-- no-owner)	SQL commands for setting object ownership are not output.
-Pfunction-name (argtype [,...])	Only the specified name function is restored. The name should be exactly the same as that in the dump content list.
-S (-- schema-only)	Only restore the table structure (data definition ). If no data is restored, the sequence value is reset.
-Susername	Specify the Super User name used to close the trigger. It is related only when -- disable-triggers is used.
-T table	Only the Schema and/or data of the specified table are restored. This option can also be used together with the-n option to specify the mode.
-X (-- no-privileges)	Do not restore the access permission information (grant/revoke command ).
-1 (-- single-transaction)	Execute the restore command in a single thing. This option implies the -- exit-on-error option.
-- No-tablespaces	The command to set the tablespace is not output. With this option, all objects will be restored to the default tablespace when pg_restore is executed.
-- No-data-for-failed-tables	By default, even if the table fails to be created, if the table already exists, the data loading operation will not stop, the result is that a large amount of duplicate data is easily inserted into the table. If this option is set, the data table is ignored when any errors occur to the table.
-- Role = rolename	Execute the restore operation with the specified role name. Generally, if the connection role does not have sufficient permissions for this recovery operation, you can use this option to switch to the role with sufficient permissions after the connection is established.
-H (-- host = host)	Specify the Host Name of the PostgreSQL server.
-P (-- port = port)	Specifies the listening port of the server. If this parameter is not specified, the default port 5432 is used.
-U (-- username = username)	The login user name for this operation. If the-O option is not specified, the Owner of this database will be the login user.
-W (-- no-password)	If the current logon user does not have a password, you can specify this option to log on directly.

2. Application Example:
 Copy codeThe Code is as follows:
# Use the createdb command to Log On As A myuser and create a newdb with recovered data
/> Createdb-U myuser newdb
# Use the-l option of the pg_restore command to export the detailed list of exported database objects in the my_dump.dat backup file.
/> Pg_restore-l my_dump.dat> db. list
/> Cat db. list
2; 145344 TABLE species ipvs
4; 145359 TABLE nt_header S
6; 145402 TABLE species_records postgres
8; 145416 TABLE ss_old ipvs
10; 145433 TABLE map_resolutions postgres
# Modify the content in the above list file to the following format.
# The main modification is to comment out the three database objects numbered 2, 4, and 8, and put the objects numbered 10 in the header of the file.
# When a file is imported, the objects 2, 4, and 8 will not be imported. During the restoration process, the data of the object 10 will be first imported, and then the data of object 6 will be imported.
/> Cat new_db.list
10; 145433 TABLE map_resolutions postgres
; 2; 145344 TABLE species ipvs
; 4; 145359 TABLE nt_header S
6; 145402 TABLE species_records postgres
; 8; 145416 TABLE ss_old limit s
# When the database is restored, the specified database is newdb. The imported database objects and import sequence are imported according to the rules prompted in the new_db.list file.
/> Pg_restore-d newdb-L new_db.list my_dump.dat

9. psql:

PostgreSQL interactive terminals are equivalent to sqlplus in Oracle.
1. List of common command line options:

 

Option	Description
-C command	Specify psql to execute an SQL command (enclosed in double quotation marks) and exit after execution.
-D dbname	Name of the database to be connected.
-E	Returns the actual query generated by \ d and other backslash commands.
-F filename	Use the data in the filename file as the command input source, instead of reading the query in interactive mode. After the file is processed, psql ends and exits.
-H hostname	Declare the Host Name of the running server
-L	List all available databases and exit.
-L filename	In addition to the normal output source, all query records are output to the file filename.
-O filename	Output all query redirects to the file filename.
-P port	The listener port of the PostgreSQL server.
-Q -- quiet	Let psql quietly execute the processed task. The lack of time-saving psql will print the welcome and many other information.
-T -- tuples-only	Disable printing the column name and result row counting footer.
-U username	Replace the default user with the user username to establish a connection with the database.

2. Application Example:
 Copy codeThe Code is as follows:
# Use the createdb command to Log On As A myuser and create a newdb with recovered data
/> Createdb-U myuser newdb
# Use the-l option of the pg_restore command to export the detailed list of exported database objects in the my_dump.dat backup file.
/> Pg_restore-l my_dump.dat> db. list
/> Cat db. list
2; 145344 TABLE species ipvs
4; 145359 TABLE nt_header S
6; 145402 TABLE species_records postgres
8; 145416 TABLE ss_old ipvs
10; 145433 TABLE map_resolutions postgres
# Modify the content in the above list file to the following format.
# The main modification is to comment out the three database objects numbered 2, 4, and 8, and put the objects numbered 10 in the header of the file.
# When a file is imported, the objects 2, 4, and 8 will not be imported. During the restoration process, the data of the object 10 will be first imported, and then the data of object 6 will be imported.
/> Cat new_db.list
10; 145433 TABLE map_resolutions postgres
; 2; 145344 TABLE species ipvs
; 4; 145359 TABLE nt_header S
6; 145402 TABLE species_records postgres
; 8; 145416 TABLE ss_old limit s
# When the database is restored, the specified database is newdb. The imported database objects and import sequence are imported according to the rules prompted in the new_db.list file.
/> Pg_restore-d newdb-L new_db.list my_dump.dat

3. built-in command list:
The format of the built-in psql command is the backslash followed by a command verb, followed by any parameter. Parameters can be separated from command verbs and other parameters. If a parameter contains a blank character, the parameter must be enclosed by single quotation marks. If the parameter contains single quotation marks, escape using a backslash. In addition, parameters in single quotes also support escape keywords such as \ t and \ n supported by C-language printf functions.

Command	Description
\	If the current table output format is not aligned, switch to alignment. If it is alignment, switch to non-alignment.
\ Cd [directory]	Switch the current working directory to directory. If no parameter exists, switch to the current user's home directory.
\ C [title]	Add a header (title) to the query result. If no parameter exists, cancel the current header.
\ C [dbname [username]	Connect to the new database and disconnect the current database. If the dbname parameter is-, the database is still connected. If the username is ignored, the current user name is used.
\ Copy	Its parameters are similar to SQL copy, and the function is almost equivalent to SQL copy. An important difference is that the built-in command can export the table content locally, or import data from a local database to a specified table, while SQL copy exports data from the table to a file on the server, or imports data from a file on the server to a data table. Therefore, SQL copy is more efficient than the built-in command.
\ D [pattern]	Displays database objects that match pattern, such as tables, views, indexes, or sequences. Displays all columns, their types, tablespaces (if not the default), and any special attributes.
\ Db [pattern]	List all available tablespaces. If pattern is declared, only the tablespaces matching the pattern are displayed.
\ Db + [pattern]	Compared with the previous command, the permission information for each tablespace is displayed.
\ Df [pattern]	Lists all available functions, their parameters, and returned data types. If pattern is declared, only the matching (Regular Expression) function is displayed.
\ Df + [pattern]	Compared with the previous command, additional information about each function is displayed, including the language and description.
\ DistvS [pattern]	This is not a separate command name: the letters I, s, t, v, and S represent indexes, sequences, tables, and views respectively) and system table ). You can declare some or all of these letters in any order to obtain a list of these objects.
\ Dn [pattern]	Lists all available modes. If pattern is declared, only the pattern names matching the pattern are listed.
\ Dn + [pattern]	Compared with the previous command, the permissions and comments for each object are added.
\ Dp [pattern]	Generate a list of available tables and their related permissions. If pattern is declared, only tables with matching names are listed.
\ DT [pattern]	Lists All data types or only those matching pattern.
\ Du [pattern]	List all configured users or only those users that match pattern.
\ Echotext [...]	Print parameters to the standard output, separate them with a space, and follow a new line. For example, \ echo 'date'
\ G [{filename | command}]	Send the content of the current query result buffer to the server and store the query output to the optional filename or direct the output to an independent Unix shell executing the command.
\ Ifilename	Read the file filename and run the query as input from the keyboard.
\ L	Lists the names of all databases on the server, their owners, and character set encoding.
\ O [{filename | command}]	Save the subsequent query results to the filename file or direct the subsequent query results to an independent shell command.
\ P	Print the current query buffer to the standard output.
\ Q	Exit the psql program.
\ R	Resets (Clears) the query buffer.
\ S [filename]	Print the command line history or store it in filename. If filename is omitted, the history is output to the standard output.
\ T	Switch whether to output the information header and the row footer of the column/field name.
\ W {filename | command}	Output the current query buffer to the file filename or direct it to the Unix command.
\ Z [pattern]	Generates a list Of all tables, views, and sequences in the database with an access permission list. If any pattern is given, it is treated as a rule expression that only displays matched tables, views, and sequences.
\! [Command]	Return to an independent Unix shell or run the Unix command. The parameters are not further explained. shell will see all the parameters.

4. Example of a built-in command application:

In psql, most of the built-in commands are easy to understand, so here are just a few commands that I personally think are relatively confusing.
 Copy codeThe Code is as follows:
# \ C: The hyphen (-) indicates that the database is still connected, and myuser is the new user name.
Postgres = # \ c-myuser
Password for user myuser:
Postgres => SELECT user;
Current_user
--------------
Myuser
(1 row)
# Execute any SQL statement.
S = # SELECT * FROM testtable WHERE I = 2;
I
---
2
(1 row)
# The \ g command will output the result of the previous SQL command to the specified file.
Postgres = # \ g my_file_for_command_g
Postgres = #\! Cat my_file_for_command_g
I
---
2
(1 row)
# The \ g command outputs the result of the previous SQL command from the pipeline to the specified Shell command, such as cat.
Postgres = # \ g | cat
I
---
2
(1 row)
# \ P print the previous SQL command.
Postgres = # \ p
SELECT * FROM testtable WHERE I = 2;
# \ W outputs the previous SQL command to the specified file.
Postgres = # \ w my_file_for_option_w
Postgres = #\! Cat my_file_for_option_w
SELECT * FROM testtable WHERE I = 2;
# \ O and \ g are opposite. This command will output the output result of the following psql command to the specified file until the next independent \ o,
# Subsequent command results will not be output to this file.
Postgres = # \ o my_file_for_option_o
S = # SELECT * FROM testtable WHERE I = 1;
# The command result after terminating is also output to the my_file_for_option_o file.
Postgres = # \ o
Postgres = #\! Cat my_file_for_option_o
I
---
1
(1 row)