7. pg_dump:
pg_dump is a tool for backing up PostgreSQL databases. It can even make a complete and consistent backup of the database when it is being used concurrently without blocking other users' access to the database. The dump format generated by this tool can be divided into two types, scripts and archives. The script format is a plain text format containing many SQL commands. These SQL commands can be used to rebuild the database and restore it to the state when the script was generated. This operation needs to be done using psql. As for the archive format, if you need to rebuild the database, you must use it with the pg_restore tool. During the reconstruction process, the restored objects can be selected, and even the entries that need to be restored can be reordered before recovery. This command is used as follows:
Copy the codeThe code is as follows:
pg_dump [option...] [dbname]
1. Command line options list:
| Options | illustrate |
| -a(--data-only) | Only output data, not output mode (definition of data object). This option just makes sense for plain text formats. For archive formats, you can specify options when calling pg_restore. |
| -b(--blobs) | Include large objects in dump. |
| -c(--clean) | Before outputting the SQL command to create a database object, output the SQL command to delete the database object. This option just makes sense for plain text formats. For archive formats, you can specify options when calling pg_restore. |
| -C(--create) | First output the command to create the database, and then reconnect to the newly created database. For scripts of this format, it is not so important to connect to which database to do before running. This option just makes sense for plain text formats. For archive formats, you can specify options when calling pg_restore. |
| -Eencoding | Creates the dump file with the specified character set. |
| -ffile | Output to the specified file, if there is no option, output to standard output. |
| -Fformat |
p(plain): SQL script file in plain text format (default).c(custom): Output a custom archive format suitable for pg_restore. This is the most flexible format, which allows rearrangement of loaded data and object definitions. This format is compressed by default.t(tar): Output tar archive files suitable for pg_restore. Use this archive to allow reordering and/or excluding database objects when restoring a database. At the same time, it may also be possible to limit which data to be restored during recovery. |
| -n schema | Dump only the contents of the schema. 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 patterns for different patterns. |
| -Nschema | The content matching the schema is not dumped, and other rules are consistent with -n. |
| -o(--oids) | As part of the data, an object identifier (OID) is output for each table. |
| -O(--no-owner) | No SQL commands that set object ownership are output. |
| -s(--schema-only) | Only the object definition (mode) is output, and no data is output. |
| -Susername | Specifies the superuser name you need to close the trigger. It only has something to do when using --disable-triggers. |
| -ttable | Only output the data of the table. It is very likely that there are multiple tables with the same name in different patterns, if so, then all matching tables will be dumped. By specifying this parameter multiple times, multiple tables can be dumped at once. Here you can also specify the same pattern as psql to match more tables.(About pattern, the basic way to use it is to treat it as a wildcard of unix, that is, * means any character, ? means any single character, .(dot) means the separator between schema and object, such as a*.b*, which means the schema starting with a and a database object starting with b. If there is no .(dot), it will only represent the database object. Here you can also use basic regular expressions, such as [0-9] to represent numbers.) |
| -Ttable | Excludes the specified table, and other rules are consistent with the -t option. |
| -x(--no-privileges) | Access permission information is not exported (grant/revoke command). |
| -Z0..9 | Declare the compression level used in those formats that support compression. (Currently, only custom formats support compression) |
| --column-inserts | Export data is represented by insert into table_name(columns_list) values(values_list) command. Such operations are relatively slower than other operations, but in special cases, such as the position of the data table field may change or new fields may be inserted into the middle of the original field list, etc. Since columns_list is explicitly specified, there will be no problem that data is imported into the error field during import. |
| --inserts | The exported data is represented by the insert command, not the copy command. Even though using insert is slower than copy, it is more meaningful for importing into other non-PostgreSQL databases in the future. |
| --no-tablespaces | The command to set the tablespace is not output. If this option is included, all objects will be restored to the default tablespace when pg_restore is executed. |
| --no-unlogged-table-data | For data tables that are not logged, their data will not be exported. As for whether to export its schema information, other options need to be relied on. |
| -h(--host=host) | Specifies the host name of the PostgreSQL server. |
| -p(--port=port) | Specifies the server's listening port, if not specified, the default is 5432. |
| -U(--username=username) | If the -O option is not specified, the Owner of this database will be the logged-in user. |
| -w(--no-password) | If the currently logged in user does not have a password, you can specify this option to log in directly. |
2. Application example:
Copy the codeThe code is as follows:
# -h: The host of PostgreSQL server is 192.168.149.137.
# -U: The logged in user is postgres.
# -t: Export a data table with table name starting with test, such as testtable.
# -a: Export data only, not the object's schema information.
# -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.137 -U postgres -t test* -a -f ./my_dump.sql mydatabase
#-c: First output the SQL command to delete the database object, and then output the SQL command to create the database object. 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 information of mydatabase database. When importing through the psql command, you can re-specify the database, 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 database object names starting with test, but does not include 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, except that the latter uses regularity.
/> 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 format file exported by pg_dump, which rebuilds the database to its state when it was saved. For archived files, pg_restore can perform selective recovery, and even rearrange the order of data before recovery.
pg_restore can be operated in two modes. If the database is specified, the archive will be restored directly to the database. Otherwise, you must create the database manually before recovering the data into the newly created database through pg_restore. This command is used as follows:
Copy the codeThe code is as follows:
pg_restore [option...] [filename]
1. Command line options list:
| Options | illustrate |
| filename | Specifies the backup file to be restored, and if there is no declaration, use standard input. |
| -a(--data-only) | Restore only data, not table schema (data object definition). |
| -c(--clean) | Clean (delete) database objects before creating them. |
| -C(--create) | Create the database before restoring it. (When using this option, the database name needs to be specified by the -d option, which only executes the most basic CREATE DATABASE command. It should be noted that all data in the archive file will be restored to the database specified in the archive file). |
| -ddbname | Establish a connection with the database dbname and restore data directly to the database. |
| -e(--exit-on-error) |
If an error is encountered while sending SQL commands to the database, exit. The default is to continue execution and an error count is displayed at the end of recovery. |
| -Fformat | Specifies the format of the backup file. Since pg_restore automatically determines the format, specifying the format is not necessary. If specified, it can be in one of the following formats:t(tar): Use this format to allow reordering and/or excluding table schema information when recovering the database, while also potentially limiting loaded data during recovery.c(custom):This format is a custom format from pg_dump. This is the most flexible format because it allows reordering data and also allows reloading table schema information. This format is compressed by default. |
| -I index | Restores only the specified index. |
| -l(--list) | Lists the contents in the backup, and the output of this operation can be used as input to the -L option. Note that if the filtering option -n or -t is used with the -l option, they will also limit the listed entries. |
| -L list-file | Only the entries listed in the list-file are restored in the order in which each entries appear in the file. You can also manually edit the file and rearrange the positions of these entries before performing the recovery operation. The comment lines starting with a semicolon (;) are commented, and the comment lines will not be imported. |
| -n namespace | Restores only database objects with the specified schema. This option can be used in conjunction with the -t option to restore the specified data object. |
| -O(--no-owner) | No SQL commands that set object ownership are output. |
| -Pfunction-name(argtype [, ...]) |
Restores only the specified named functions. The name should be exactly the same as the dumped content list. |
| -s(--schema-only) | Restore only the table structure (data definition). If the data is not restored, the sequence value will be reset. |
| -Susername | Specifies the superuser name you need to close the trigger. It only has something to do when using --disable-triggers. |
| -t table | Only the schema and/or data of the specified table are restored, and this option can also specify the mode along with the -n option. |
| -x(--no-privileges) | Access permission information is not restored (grant/revoke command). |
| -1(--single-transaction) | Execute recovery commands in a single thing. This option implies the --exit-on-error option. |
| --no-tablespaces | The command to set the tablespace is not output. If this option is included, 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 is created, if the table already exists, the data loading operation will not stop. The result is that it is easy to cause a large amount of duplicate data to be inserted into the table. If this option is included, loading of the data table will be ignored once any errors are occured for the table. |
| --role=rolename | Perform the restore operation with the specified role name. Generally speaking, if the connection role does not have sufficient permissions for this recovery operation, you can use this option to switch to a role with sufficient permissions after establishing the connection. |
| -h(--host=host) | Specifies the host name of the PostgreSQL server. |
| -p(--port=port) | Specifies the server's listening port, if not specified, the default is 5432. |
| -U(--username=username) | If the -O option is not specified, the Owner of this database will be the logged-in user. |
| -w(--no-password) | If the currently logged in user does not have a password, you can specify this option to log in directly. |
2. Application example:
Copy the codeThe code is as follows:
#First use the createb command, log in as myuser user, and create data with recovery newdb
/> createdb -U myuser newdb
#Use the -l option of the pg_restore command to export the detailed list of database objects in the my_dump.dat backup file.
/> pg_restore -l my_dump.dat >
/> cat
2; 145344 TABLE species postgres
4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
8; 145416 TABLE ss_old postgres
10; 145433 TABLE map_resolutions postgres
#Modify the content in the above list file to the following form.
#The main modification is to comment out the three database objects with numbers 2, 4 and 8, and put the objects with numbers 10 at the head of the file, so that they are based on the list
#When file import, three objects such as 2, 4 and 8 will not be imported. During the recovery process, the data of the object numbered 10 will be imported first, and then the data of object 6 will be imported.
/> cat new_db.list
10; 145433 TABLE map_resolutions postgres
;2; 145344 TABLE species postgres
;4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
;8; 145416 TABLE ss_old postgres
#The database specified during recovery is newdb. Which database objects imported and the import order will be 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:
The interactive terminal of PostgreSQL is equivalent to SQLplus in Oracle.
1. List of commonly used command line options:
| Options | illustrate |
| -c command | Specify psql to execute a SQL command (enclosed in double quotes), and exit after execution. |
| -d dbname | The name of the database to be connected. |
| -E | Echoes the actual query generated by \d and other backslash commands. |
| -f filename | Use data from the filename file as the command input source instead of interactive read-in queries. After processing the file, psql ends and exits. |
| -h hostname | Declare the hostname of the running server |
| -l | List all available databases and exit. |
| -L filename | Except for the normal output source, output all query records to the file filename. |
| -o filename | Redirects all queries to filename. |
| -p port | Specifies the listening port of the PostgreSQL server. |
| -q --quiet | Let psql perform the tasks processed quietly. By default psql will output print welcome and many other information. |
| -t --tuples-only | Close information such as print column name and result row count footnote. |
| -U username | Use username to establish a connection with the database instead of the default user. |
2. Application example:
Copy the codeThe code is as follows:
#First use the createb command, log in as myuser user, and create data with recovery newdb
/> createdb -U myuser newdb
#Use the -l option of the pg_restore command to export the detailed list of database objects in the my_dump.dat backup file.
/> pg_restore -l my_dump.dat >
/> cat
2; 145344 TABLE species postgres
4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
8; 145416 TABLE ss_old postgres
10; 145433 TABLE map_resolutions postgres
#Modify the content in the above list file to the following form.
#The main modification is to comment out the three database objects with numbers 2, 4 and 8, and put the objects with numbers 10 at the head of the file, so that they are based on the list
#When file import, three objects such as 2, 4 and 8 will not be imported. During the recovery process, the data of the object numbered 10 will be imported first, and then the data of object 6 will be imported.
/> cat new_db.list
10; 145433 TABLE map_resolutions postgres
;2; 145344 TABLE species postgres
;4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
;8; 145416 TABLE ss_old postgres
#The database specified during recovery is newdb. Which database objects imported and the import order will be 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 command of psql is a backslash followed by a command verb followed by any parameter. Parameters can be separated from command verbs and other parameters by whitespace. If the parameter contains whitespace, the parameter must be enclosed in single quotes. If the parameter contains single quotes, it needs to be escaped with a backslash. In addition, the parameters in single quotes also support escape keywords similar to those supported by C printf functions, such as \t, \n, etc.
| Order | illustrate |
| \a | If the current table output format is not aligned, switch to aligned. If it is aligned, switch to misalignment. |
| \cd [directory] | Switch the current working directory to directory. If there are no parameters, switch to the current user's home directory. |
| \C [title] | Add a header for the query result (title) and cancel the current header if there is no parameter. |
| \c[dbname[username] ] | Connect to the new database while disconnecting the current connection. If the dbname parameter is -, it means that the current database is still connected. If username is ignored, it means that the current username continues. |
| \copy | Its parameters are similar to SQL copy, and its functions are almost equivalent to SQL copy. An important difference is that the built-in command can export the content of the table locally, or import it from locally to a table specified by the database. SQL copy exports the data in the table to a file on the server, or imports the file from the server to a data table. It can be seen that SQL copy is more efficient than this built-in command. |
| \d [pattern] | Displays database objects that match pattern, such as tables, views, indexes, or sequences. Shows all columns, their type, tablespace (if not default), and any special properties. |
| \db [pattern] | Lists all available tablespaces. If pattern is declared, only those tablespaces matching patterns are displayed. |
| \db+ [pattern] | Compared with the previous command, permission information for each tablespace will be added. |
| \df [pattern] | Lists all available functions, as well as their parameters and returned data types. If pattern is declared, only matching (regular expression) functions are displayed. |
| \df+ [pattern] | Compared with the previous command, additional information for each function will be added, including language and description. |
| \distvS [pattern] | This is not a separate command name: the letters i, s, t, v, and S represent index (index), sequence, table (table), view (view) and system table, respectively. You can declare parts or all of these letters in any order to get a list of these objects. |
| \dn [pattern] | List all available patterns. If pattern is declared, only the pattern names that match the pattern are listed. |
| \dn+ [pattern] | Compared with the previous command, permissions and comments for each object will be added. |
| \dp [pattern] | Generate a column of available tables and their associated permissions. If pattern is declared, then only list tables whose names can match the pattern. |
| \dT [pattern] | List all data types or only those that match pattern. |
| \du [pattern] | List all configured users or only those that match pattern. |
| \echotext [ ... ] | Print parameters to the standard output, separated by a space and finally followed by a new line. For example: \echo `date` |
| \g[{filename ||command}] | Send the contents of the current query result buffer to the server and store the query's output to an optional filename or direct the output to a separate Unix shell executing command. |
| \ifilename | Read from the file filename and perform the query as input from the keyboard. |
| \l | Lists the names of all databases on the server and their owners and character set encodings. |
| \o[{filename ||command}] | Save the subsequent query results to the filename or direct the subsequent query results to an independent shell command. |
| \p | Print the current query buffer to standard output. |
| \q | Exit the psql program. |
| \r | Reset (clear) the query buffer. |
| \s [filename] | Print out the command line history or store it in filename. If filename is omitted, history will be output to standard output. |
| \t | Toggle whether to output the header and row count footnotes for column/field names. |
| \w{filename ||command} | Output the current query buffer to filename or direct to Unix command. |
| \z [pattern] | Generate a list of all tables, views, and sequences in a database with an access list. If any pattern is given, it is treated as a regular expression, only matching tables, views, and sequences are displayed. |
| \! [command] | Return to a standalone Unix shell or execute Unix command command. The parameters will not be explained further, the shell will see all parameters. |
4. Built-in command application example:
In psql, most of the built-in commands are easier to understand, so here are just a few commands that I personally think are relatively easy to confuse.
Copy the codeThe code is as follows:
# \c: where the horizontal line (-) indicates that the current 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.
postgres=# SELECT * FROM testtable WHERE i = 2;
i
---
2
(1 row)
The # \g command will output the results 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 will output the results 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 Output 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, instead, this command will output the output result of the subsequent psql command to the specified file until the next independent \o is encountered,
#The subsequent command results will no longer be output to the file.
postgres=# \o my_file_for_option_o
postgres=# SELECT * FROM testtable WHERE i = 1;
# The result of termination of the subsequent command is also output to the my_file_for_option_o file.
postgres=# \o
postgres=# \! cat my_file_for_option_o
i
---
1
(1 row)