Back up a single database with pg_dump - PolarDB - Alibaba Cloud Documentation Center

pg_dump is a logical backup tool provided by PolarDB for PostgreSQL (Compatible with Oracle). You can use it to back up a single database in a cluster as a script file or other archive files.

Introduction

pg_dump is used to back up a single database. It creates a consistent backup even while the database is being accessed, and does not block other users from accessing it (for reads or writes). For more information, see the official pg_dump website.

Note

The pg_dump tool in PolarTools differs from the community version. The PolarTools version is adapted for PolarDB for PostgreSQL (Compatible with Oracle). Using the community version of pg_dump may result in unknown errors or incomplete data backups.

Backup file formats

Script file: A script file is a plain text file that contains the SQL commands to restore the database to its state at the time of the backup.
archive file: An archive file must be restored with pg_restore.
The output file formats include the custom format -Fc, the directory format -Fd, and the tar format archive file -Ft. The -Fc and -Fd formats allow you to select and reorder all archived items and are compressed by default. The -Ft format is not a compressed file, and it does not support reordering when you restore data.
Note
The directory format is the only format that supports parallel backup.
When used with one of the archive formats, pg_dump can back up an entire database. You can use pg_restore to examine the archive or select which parts of the database to restore.

Syntax

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

Table 1. Parameters

Parameter	Description
connection-option	Command-line options that control database connection parameters. For details, see Connection options.
option	Command-line options that control the output content and format. For details, see Dump options.
dbname	Name of the database to back up.

Table 2. Connection options

Command-line option	Description
-d dbname or --dbname=dbname	Specifies the name of the database to connect to.
-h host or --host=host	Specifies the hostname of the computer on which the server is running. If this value starts with a slash, it is used as the directory for the UNIX domain socket. The default value is the PGHOST environment variable.
-p port or --port=port	Specifies the TCP port or the local UNIX domain socket file extension on which the server is listening for connections. The default value is taken from the PGPORT environment variable, or a compiled-in default is used if this variable is not set.
-U username or --username=username	The username to connect as.
-w is an alias for --no-password	Never issues a password prompt.
-W is the short form of --password.	Forces pg_dump to prompt for a password before connecting to a database. Note This option is optional.
--role=rolename	Specifies a role name to use to create the backup.

Table 3. Dump options

Command-line option	Description
dbname	The name of the database to be backed up. If not specified, the PGDATABASE environment variable is used.
-a is an alias for --data-only	Backs up only the data, not the schema (data definitions). Note This option backs up table data, blobs, and sequence values.
-b is an alias for --blobs	Blobs are included in backups by default. When the --schema, --table, or --schema only option is specified, blobs are not included in the backup. Important Because blobs are considered data, they are included in a backup if you use the --data-only option, but are excluded if you use the --schema-only option.
-B is an alias for --no-blobs	Excludes blobs from the backup. Note If both -b and -B are specified, the backup will include blobs.
-c is an alias for --clean	Delete the database object before you execute the command to create it. To avoid error messages when you restore the database, we recommend that you specify --if exists. Note This option applies only to script files. For archive files, you can specify this option when you call pg_restore.
-C or --create	Creates a database and reconnects to the newly created database. If --clean is specified, the script deletes and recreates the target database, and then reconnects to it. If the --create option is specified and the --no-acl option is not specified, the backup data includes the comments, configuration information, and access privileges of the database. Note This option applies only to script files. For archive files, you can specify this option when you call pg_restore.
-E encoding or --encoding=encoding	Creates a backup by using the specified character encoding. By default, the backup is created by using the character encoding of the database that you want to back up. You can also set the value of the PGCLIENTENCODING environment variable to the desired backup encoding.
-F format or --format=format	Specifies the format of the output. The format can be one of the following: p or plain: Outputs the script file (default). c, custom: Outputs a custom format archive file suitable for input to pg_restore. By default, archive files in this format are compressed. d (directory): Outputs an archive file in the directory format, which is suitable as input for pg_restore. By default, archive files in this format are compressed and support parallel backups. t or tar: Outputs a tar-format archive file suitable for input to pg_restore. The tar format does not support compression. In addition, when you use the tar format, the relative order of table data items cannot be changed during restoration.
-f file or --file=file	Sends output to the specified file. For the directory format, this parameter is required and specifies the target directory instead of a file. For other file-based formats, if this parameter is omitted, the output is sent to standard output.
-j njobs or --jobs=njobs	This option runs backups in parallel by simultaneously backing up `njobs` tables and must be used with the directory format. Note Before you start a parallel backup, stop processes that modify the database, such as those involving DDL and DML.
-n pattern is a shorthand for --schema=pattern.	Only backs up schemas that match the pattern. If this option is not specified, all non-system schemas in the target database are backed up. Note When you specify -n, pg_dump does not back up any other database objects that the selected schema may depend on. Therefore, a successful restore of a specific schema to an empty database is not guaranteed. When you specify -n, pg_dump does not back up data for non-schema objects, such as Blobs. To back up this data, include the --blobs command-line option.
-N pattern is an alias for --exclude-schema=pattern	Backs up schemas other than pattern. Note If -n and -N are specified together, the backup matches at least one -n schema but does not match any -N schema, If only -N is specified, schemas that match -N are excluded from the backup.
-o or --oids	Backs up object identifiers (OIDs) as part of the data for every table. Use this option if your application references the OID columns, for example, in a foreign key constraint. Otherwise, do not use this option.
-O is short for --no-owner	Does not include commands to set object ownership to match the original database. Note This option applies only to script files. For archive files, you can specify this option when you call pg_restore.
-s is short for --schema-only	Backs up only the object definitions (schema), not data.
-S username is the same as --superuser=username	The superuser username used when disabling triggers. This option is used only with --disable-triggers.
-t pattern, or --table=pattern	Backs up only tables that match the pattern. You can select multiple tables by specifying the -t option multiple times or by using wildcards in the pattern. Note When you specify -t, pg_dump does not attempt to back up any other database objects that the selected table may depend on. Therefore, there is no guarantee that a backup of a specific table can be successfully restored to an empty database.
-T pattern is an alias for --exclude-table=pattern	Does not back up tables that match the pattern. You can specify the -T option multiple times to exclude tables that match multiple patterns. Note When both -t and -T are specified, the backup includes tables that match at least one -t but do not match -T. If only -T is specified, tables that match -T are excluded from the backup.
-v or --verbose	Specifies verbose mode.
-V is an alias for --version	Prints the pg_dump version and exits.
-x is an alias for --no-privileges or --no-acl	Prevents the backup of access privileges (grant/revoke commands).
-Z 0..9 is the same as --compress=0..9	Specifies the compression level to use. A value of 0 means no compression. Note For custom format archives, this specifies compression of individual table-data segments, and the default is to compress at a moderate level. For script files, the default is no compression. Setting a non-zero compression level compresses the entire output file.
--column-inserts and --attribute-inserts	Back up the data as `INSERT` commands with explicit column names. `INSERT INTO table (column, ...) VALUES ...`
--disable-dollar-quoting	Disables the use of dollar quoting for function bodies.
--disable-triggers	Temporarily disables triggers on the target table. This option is effective only when you create a data backup. When you use this option, you must specify a superuser by using -S. Note This option applies only to script files. For archive files, you can specify this option when you call pg_restore.
--enable-row-security	Backs up only the parts of tables that you have permission to access. This option is effective only when backing up the content of a table that has row-level security enabled. Important If you use this option, you may also need to use INSERT to create backups because COPY FROM does not support row-level security during data recovery.
--exclude-table-data=pattern	Does not back up the data of tables that match the pattern. You can specify --exclude-table-data multiple times to exclude tables that match multiple patterns. Note To exclude data from all tables in a database, refer to --schema only.
--if-exists	Use conditional commands, such as adding an IF EXISTS clause, to clean up database objects. You must also specify the --clean option, otherwise this option has no effect.
--inserts	Back up the data as INSERT commands. Important If you use this option, reordering data during restoration may cause the operation to fail. We recommend using --column-inserts .
--load-via-partition-root	Use the COPY or INSERT command to back up data from a table partition. Note Use parallel restoration with caution when restoring an archive file created with this option.
--lock-wait-timeout=timeout	Specifies the wait time for acquiring a shared lock.
--no-comments	Does not back up comments.
--no-publications	Does not back up publications.
--no-security-labels	Does not back up security labels.
--no-subscriptions	Does not back up subscriptions.
--no-sync	Returns without waiting to safely write all files to disk.
--no-synchronized-snapshots	Indicates that you can run pg_dump -j on the server.
--no-tablespaces	Creates all objects in the default tablespace. Note This option applies only to script files. For archive files, you can specify this option when you call pg_restore.
--no-unlogged-table-data	Does not back up the content of unlogged tables.
--quote-all-identifiers	Forces quoting of all identifiers.
--rows-per-insert=nrows	Controls the maximum number of rows per INSERT statement in a database backup.
--section=sectionname	Specifies that only the specified section is backed up. The name of the section can be pre-data, data, or post-data. You can specify this option multiple times to select multiple sections. By default, all sections are backed up. Note The data item contains actual table data, large object content, and sequence values. post-data includes the definitions of indexes, triggers, rules, and constraints, but not validated check constraints. pre-data includes all other data definition items.
--serializable-deferrable	Uses a serializable transaction for the backup. Note If read-write transactions are active when pg_dump starts, the backup may be delayed for an indeterminate length of time. This option has no effect if no read-write transactions are active when pg_dump starts.
--snapshot=snapshotname	Uses the specified synchronized snapshot when backing up the database.
--strict-names	Requires that each schema (-n or --schema) and table (-t or --table) pattern matches at least one schema or table in the source database. Note If no match is found for the schema and table qualifiers, pg_dump generates an error even without --strict-names. This option is not valid for -N/--exclude schema, -T/--exclude table, or --exclude table data. An exclusion pattern that does not match any objects is not considered an error.
--use-set-session-authorization	Outputs the SQL-standard SET SESSION AUTHORIZATION command instead of the ALTER OWNER command.
-? or --help	Displays help for pg_dump command-line arguments and then exits.

Usage notes

When you select a table that is used only for data backup and use the --disable triggers option, pg_dump issues a command to disable user table triggers before inserting data and a command to re-enable the triggers after the data is inserted. If a restore is interrupted, the system catalogs might be left in an incorrect state.
After a backup file is restored, you can run ANALYZE to ensure optimal performance.
When you back up a logical replication subscription, pg_dump generates a CREATE SUBSCRIPTION command with the connect=false option. If the host has changed, you may need to change the connection information, and you must truncate the target table before starting a new full table copy.
Because pg_dump internally executes SELECT statements, if you have problems running pg_dump, you must ensure that you can query information from the database by using a tool such as psql. In addition, ensure that any default connection settings and environment variables used by the libpq frontend library are working correctly.
The statistics collector typically collects database activities for pg_dump, but if this is not required, you can use the PGOPTIONS or ALTER USER command to set the track_counts parameter to false.

Examples

Run the following command to back up the database named mydb to an SQL script file:
```
pg_dump mydb > db.sql
```
Run the following command to reload the SQL script into the newly created database named newdb:
```
psql -d newdb -f db.sql
```
Run the following command to back up a database to a custom format archive file:
```
pg_dump -Fc mydb > db.dump
```
Run the following command to back up a database to a directory format archive file:
```
pg_dump -Fd mydb -f dumpdir
```
Run the following command to back up a database in parallel with 5 worker jobs to a directory format archive file:
```
pg_dump -Fd mydb -j 5 -f dumpdir
```
Run the following command to restore the archive file to a new database named newdb:
```
pg_restore -d newdb db.dump
```
Run the following command to restore an archive file into the same database from which the backup was taken, clearing the current contents 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 in the detroit schema whose names start with emp, except for the table named employee_log:
```
pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql
```
Run the following command to back up all schemas whose names start with east or west and end with gsm (excluding any schemas whose names contain test):
```
pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql
```
The following command uses a regular expression to achieve the same result more concisely:
```
pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql
```
Run the following command to back up all database objects except for tables whose names start with ts_:
```
pg_dump -T 'ts_*' mydb > db.sql
```
If you need to specify an uppercase or mixed-case name in the -t and related switches, you must enclose the name in double quotes. Otherwise, it will be folded to lowercase. However, double quotes are special characters for shell commands, so you must also escape them. Therefore, to dump a single table with a mixed-case name, run the following command:
```
pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql
```