How to use pg_dump to back up a single database - PolarDB

pg_dump is a logical backup tool provided by PolarDB for PostgreSQL (Compatible with Oracle). It is used to back up a database in a cluster into script files or archive files.

Background information

pg_dump is used to back up a single database. Even if the current database is being accessed, a consistent backup is made for the accessed data. The backup process does not prevent other users from reading or writing the database. For more information, see pg_dump.

Note

The pg_dump tool in PolarTools is different from that in PostgreSQL Community. The pg_dump tool in PolarTools is adapted to PolarDB for PostgreSQL (Compatible with Oracle). Unknown exceptions may occur or incomplete data may be backed up when you use the pg_dump tool in PostgreSQL Community.

Backup file formats

Script file: the plain text file that contains the SQL statements used to manage databases, including the SQL statements used to rebuild and back up a database.
Archive file: the file that is required to work with pg_restore to rebuild a database.
Output files are in the following formats: -Fc, -Fd, and -Ft compressed in tar. -Fc is a custom format. -Fd is a directory format. -Ft is an archive format. You can select and re-sort all -Fc and -Fd archive files by project. By default, -Fc and -Fd files are compressed. -Ft archive files are not compressed. You cannot re-sort -Ft files when you restore data.
Note
Only -Fd files support parallel backups.
You can use pg_dump with an archive file to back up the entire database. You can use pg_restore to check an archive file or select the data that needs to be restored.

Syntax

pg_dump [connection-option...] [option...] [dbname]

Table 1. Parameters

Parameter	Description
connection-option	Specifies the command-line options that control the parameters used to connect to the database. For more information, see connection-option.
option	Specifies the command-line options that are used to control the output content and the format. For more information, see option.
dbname	Specifies the name of the database you want to back up.

Table 2. connection-option

Command-line option	Description
-d dbname (--dbname=dbname)	Specifies the name of the database to which you want to connect.
-h host (--host=host)	Specifies the hostname of the computer that runs the server. If this value starts with a slash, the value is used as the directory of a UNIX domain socket. The default value is the value of the PGHOST environment variable.
-p port (--port=port)	Specifies the TCP port or the local UNIX domain socket file extension on which the server is listening for connections. By default, the port number is contained in the value of the PGPORT environment variable. If the port number in the value is not used, the default value in the program is used.
-U username (--username=username)	Specifies the username of the account that is used to connect to the database.
-w (--no-password)	Specifies that you are not prompted to enter the password when pg_dump connects to the database.
-W (--password)	Specifies that you are prompted to enter the password when pg_dump connects to the database. Note This option is optional.
--role=rolename	Specifies the role name used to create a backup.

Table 3. option

Command-line option	Description
dbname	Specifies the name of the database you want to back up. If you do not specify this parameter, the value of the PGDATABASE environment variable is used.
-a (--data-only)	Specifies that only data is backed up. Schemas are not backed up. Note This option specifies the table data, blobs, and sequence values that are to be backed up.
-b (--blobs)	Specifies that blobs are backed up by default. If you specify --schema, -table, or --schema only, blobs are not backed up. Important Blobs are viewed as data. If you specify --data only, data can be backed up. If you specify --schema only, data cannot be backed up.
-B (--no-blobs)	Specifies that blobs are not backed up. Note If both -b and -B are specified, blobs are backed up.
-c (--clean)	Specifies that existing database objects are deleted before the command used to create a database object is run. To prevent errors during restoration, we recommend that you specify the --if exists option. Note This option applies only to script files. You can specify this option for archive files when you call pg_restore.
-C (--create)	Specifies that the command is run to create and connect to a database. If you specify --clean, this script is run to delete the existing database, create a database, and re-establish a connection to the database. If you specify --create without specifying --no-acl, the comments, configuration information, and the access control information of the database are also backed up when the system backs up data. Note This option applies only to script files. You can specify this option for archive files when you call pg_restore.
-E encoding (--encoding=encoding)	Specifies that a backup is created by using the specified character encoding method. By default, a backup is created by using the database character encoding method. You can also use the value of the PGCLIENTENCODING environment variable as the encoding method for the backup.
-F format (--format=format)	Specifies the format of backup files. Default value: p. You can select one of the following formats: p(plain): Script files are generated. c (custom): Archive files in a custom format that can be used as the input content of pg_restore are generated. By default, archive files in this format are compressed. d (directory): Archive files in a directory format that can be used as the input content of pg_restore are generated. By default, archive files in this format are compressed. In addition, this format supports parallel backups. t (tar): Archive files in the tar format that can be used as the input content of pg_restore are generated. Archive files in the tar format cannot be compressed. In addition, when you use the tar format, you cannot modify the relative sequence of table data items when the system restores data.
-f file (--file=file)	Specifies the name of the file to which backup data is sent. This parameter is required if the backup is performed by using a directory format and the required directory instead of a file is specified. This parameter is optional if the backup is performed by using another file format. By default, a standard file format is used for backups.
-j njobs (--jobs=njobs)	Specifies the number of jobs used to back up tables in parallel. This option must be used together with the directory format. Note Before the system backs up tables in parallel, you must stop the processes for DDL and DML that are used to modify the database.
-n pattern (--schema=pattern)	Specifies that only data whose schemas match the pattern value is backed up. If this option is not specified, data that uses all non-system schemas in the database is backed up. Note If you specify -n, pg_dump does not back up database objects on which the specified schema may depend. Therefore, it cannot be guaranteed that the results of a specific-schema backup can be successfully restored to an empty database. If you specify -n, pg_dump does not back up data such as blobs whose schemas do not match the specified schema. To back up blobs, you can add the --blobs command-line option during the backup.
-N pattern (--exclude-schema=pattern)	Specifies that data whose schemas do not match the pattern value is backed up. Note If you specify both -n and -N, data whose schemas match at least one schema specified by -n is backed up but data whose schemas match schemas specified by -N is not backed up. If you specify only -N, data whose schemas match the schemas specified by -N is not backed up.
-o (--oids)	Specifies that the object identifier (OID) of each table is backed up. Specify this option if your application uses a method to import the OID column in scenarios such as foreign key constraints. Otherwise, do not specify this option.
-O (--no-owner)	Specifies that the ownership of an object is specified when the system tries to match the source database. Note This option applies only to script files. You can specify this option for archive files when you call pg_restore.
-s (--schema-only)	Specifies that only object schemas are backed up. Data is not backed up.
-S username (--superuser=username)	Specifies the superuser name used to disable a trigger. You can specify this option when you use --disable-triggers.
-t pattern (--table=pattern)	Specifies that only tables whose schemas match the pattern value are backed up. To select multiple tables, you can specify multiple -t options or set the schema parameter to a wildcard. Note When you specify -t, pg_dump does not back up other database objects on which the selected tables may depend. Therefore, it cannot be guaranteed that the results of a specific-schema backup can be successfully restored to an empty database.
-T pattern (--exclude-table=pattern)	Specifies that tables whose schemas match the pattern value are not backed up. You can specify multiple -T options to exclude backups of tables that match specified schemas. Note If you specify both -t and -T, tables whose schemas match at least one -t value are backed up but tables whose schemas match the -T value are not backed up. If you specify only -T, tables whose schemas match the -T value are not backed up.
-v (--verbose)	Specifies that the verbose mode is used during the backup.
-V (--version)	Specifies that the pg_dump version is displayed and the system instructs you to exit.
-x (--no-privileges or --no-acl)	Specifies that the access permissions to back up files are revoked, which is equivalent to the GRANT or REVOKE statement.
-Z 0..9 (--compress=0..9)	Specifies the compression level. 0 specifies that files are not compressed. Note For custom archive files, the single table-data segments are compressed. By default, a moderate compression level is used. By default, script files are not compressed. If you set the compression level to a value other than 0, the entire output file is compressed.
--column-inserts and --attribute-inserts	Specifies that data is backed up as INSERT commands with explicit column names. `INSERT INTO table (column, ...) VALUES ...`
--disable-dollar-quoting	Specifies that dollar quoting for function bodies is disabled.
--disable-triggers	Specifies that triggers on the required table are disabled. This option applies only when you create data backups. When you use this option, you must use -S to specify a superuser. Note This option applies only to script files. You can specify this option for archive files when you call pg_restore.
--enable-row-security	Specifies that only some content in tables you are allowed to access can be backed up. This option applies only when the content of tables you want to back up is of row-level security. Important To use this option, you may also need to use INSERT to perform backups, because COPY FROM does not support row-level security when the system restores data.
--exclude-table-data=pattern	Specifies that the data in the tables whose schemas that match the pattern value is not backed up. You can specify multiple --exclude-table-data options to prevent tables that match specified schemas from being backed up. Note For more information about how to prevent all table data in the database from being backed up, see --schema only.
--if-exists	Specifies that conditional commands such as the IF EXISTS clause are used to clear database objects. When you specify this option, you must also specify --clean to make the configuration take effect.
--inserts	Specifies that data is backed up as INSERT commands. Important After you specify this option, data may fail to be re-sorted when the system restores data. We recommend that you use --column-inserts.
--load-via-partition-root	Specifies that the COPY or INSERT command is run when the system backs up data in table partitions. Note When you restore archive files created by using this option, we recommend that you exercise caution when you perform parallel backups.
--lock-wait-timeout=timeout	Specifies the waiting time used to obtain a shared lock.
--no-comments	Specifies that comments are not backed up.
--no-publications	Specifies that publications are not backed up.
--no-security-labels	Specifies that security labels are not backed up.
--no-subscriptions	Specifies that subscription information is not backed up.
--no-sync	Specifies that pg_dump immediately returns data. You do not need to wait for all files to be securely written to disks.
--no-synchronized-snapshots	Specifies that pg_dump -j can be run on the server.
--no-tablespaces	Specifies that all objects are created in default tablespaces. Note This option applies only to script files. You can specify this option for archive files when you call pg_restore.
--no-unlogged-table-data	Specifies that the content of unlogged tables is not backed up.
--quote-all-identifiers	Specifies that the system forcefully quotes all identifiers.
--rows-per-insert=nrows	Specifies the maximum number of rows for each INSERT command when the database is backed up as INSERT commands.
--section=sectionname	Specifies that only the data specified by section is backed up. The section name can be pre-data, data, or post-data. You can specify multiple section options. By default, data specified by all section options is backed up. Note The data option specifies the actual table data, blobs, and sequence values. The post-data option specifies indexes, triggers, and definitions of rules and constraints. Validated check constraints are not specified. The pre-data option specifies all other data definition items.
--serializable-deferrable	Specifies that a serializable transaction is used for backups. Note If read and write transactions are in the active state when you use pg_dump, the system starts to back up data after a delayed indeterminate period of time. If read and write transactions are not in the active state when you use pg_dump, this option makes no difference.
--snapshot=snapshotname	Specifies that specified synchronized snapshots are used when the system backs up the database.
--strict-names	Specifies that each schema specified by -n/--schema and each table qualifier specified by -t/--table is used to match at least one schema and table in the source database. Note If no schema or table is matched, pg_dump returns an error even if --strict-names is not specified. This option has no impact on -N/--exclude schema, -T/--exclude table, or --exclude table data. An exclusion pattern that fails to match any objects is not considered an error.
--use-set-session-authorization	Specifies that the standard SET SESSION AUTHORIZATION SQL statement instead of ALTER OWNER is generated.
-? (--help)	Specifies that the help information of the pg_dump tool is displayed.

Notes

When you specify the -a option to back up only data and specify --disable triggers to disable the triggers on a table, pg_dump emits commands to disable triggers on user tables before pg_dump inserts data. After data is inserted, pg_dump emits commands to enable the triggers. If the restoration is interrupted, the system catalog may stay in an error state.
After backup files are restored, you can run ANALYZE to ensure the optimal performance.
When pg_dump backs up logical replication subscription information, pg_dump generates CREATE SUBSCRIPTION that uses the connect=false option. If the involved hosts have changed, the connection information may have to be changed. It may also be appropriate to truncate the required tables before a new full table copy task is initiated.
pg_dump internally executes the SELECT statement. If an error occurs when you run pg_dump, make sure that you can select information from the database by using a tool such as psql. In addition, make sure that default connection settings and environment variables used by the libpq frontend library are correct.
The database activity of pg_dump is normally collected by the statistics collector. If this is undesirable, you can use PGOPTIONS or ALTER USER to set track_counts to false.

Examples

Run the following command to back up a database named mydb to an SQL script file:
```
pg_dump mydb > db.sql
```
Run the following command to load the SQL script to a newly created database named newdb:
```
psql -d newdb -f db.sql
```
Run the following command to back up a database to the archive file in the custom format:
```
pg_dump -Fc mydb > db.dump
```
Run the following command to back up a database to the archive file in the directory format:
```
pg_dump -Fd mydb -f dumpdir
```
Run the following command to back up a database to the archive file in the directory format in parallel by using five worker jobs:
```
pg_dump -Fd mydb -j 5 -f dumpdir
```
Run the following command to reload the archive file to the newly created database named newdb:
```
pg_restore -d newdb db.dump
```
Run the following command to reload the archive file to the same database the file was backed up from and clear the current content of that database:
```
pg_restore -d postgres --clean --create db.dump
```
Run the following command to back up a single table named mytab:
```
pg_dump -t mytab mydb > db.sql
```
Run the following command to back up all tables whose names start with emp in the detroit schema. A table named employee_log is not backed up.
```
pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql
```
Run the following command to back up tables whose schema names start with east or west and end with gsm. Tables whose schema names are test are not backed up.
```
pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql
```
Run the following command to use regular expression notations to consolidate the switches:
```
pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql
```
Run the following command to back up all database objects except tables whose names start with ts_:
```
pg_dump -T 'ts_*' mydb > db.sql
```
To specify an uppercase or mixed-case name in -t and related switches, you need to enclose the name in double quotation marks ("). Otherwise, the name is folded as lowercase. However, double quotation marks (") are used in a different way in Shell commands. Therefore, the name must be quoted in turn. To back up a single table whose name contains both uppercase and lowercase letters, run the following command:
```
pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql
```