Perform logical backups with pg_dump - PolarDB

pg_dump is the logical backup tool for PolarDB for PostgreSQL (Compatible with Oracle). Use it to export a single database to a script file or archive file while the database remains fully online.

How it works

pg_dump takes a consistent snapshot of a single database, even while other clients are reading and writing. The backup never blocks other users.

All examples in this document use the pg_dump tool from PolarTools, which is adapted for PolarDB for PostgreSQL (Compatible with Oracle). Using the pg_dump tool from PostgreSQL Community instead may cause unknown errors or incomplete backups.

For the upstream reference, see pg_dump in the PostgreSQL documentation.

Prerequisites

Before you begin, ensure that you have:

The pg_dump binary from PolarTools (not the PostgreSQL Community binary)
A PolarDB cluster endpoint, port, and database credentials
Sufficient disk space for the backup output

Choose an output format

pg_dump supports four output formats. Choose based on what you plan to do with the backup:

Format	Flag	Compressed	Parallel backup	Flexible restore	Best for
Plain (script file)	`-Fp`	No	No	No	Simple restores with `psql`
Custom (archive file)	`-Fc`	Yes	No	Yes	Selective or reordered restores with `pg_restore`
Directory (archive file)	`-Fd`	Yes	Yes	Yes	Large databases that benefit from parallel backup and restore
Tar (archive file)	`-Ft`	No	No	No	Compatibility with tar-based workflows

Key decisions:

To run a parallel backup with -j, use -Fd. Directory format is the only format that supports parallel jobs.
To selectively restore individual tables or schemas, use -Fc or -Fd. Both support item selection and reordering in pg_restore.
To load a backup directly with psql, use -Fp (plain text).
Avoid -Ft if you need to reorder items at restore time — tar format does not support reordering.

Note

Only directory format (-Fd) supports parallel backup.

Syntax

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

Parameters

Parameter	Description
`connection-option`	Options controlling the database connection. See Connection options.
`option`	Options controlling output content and format. See Dump options.
`dbname`	Name of the database to back up.

Connection options

Option	Description
`-d dbname` / `--dbname=dbname`	Database name to connect to.
`-h host` / `--host=host`	Server hostname or, if the value starts with a slash, the directory of a UNIX domain socket. Defaults to the `PGHOST` environment variable.
`-p port` / `--port=port`	TCP port or UNIX domain socket file extension. Defaults to the `PGPORT` environment variable, or the program default.
`-U username` / `--username=username`	Username for the database connection.
`-w` / `--no-password`	Never prompt for a password.
`-W` / `--password`	Force a password prompt.
`--role=rolename`	Role name used when creating the backup.

Dump options

Option	Description
`dbname`	Database to back up. If omitted, the value of `PGDATABASE` is used.
`-a` / `--data-only`	Dump data only, not schema. Includes table data, blobs, and sequence values.
`-b` / `--blobs`	Include blobs. Blobs are included by default unless `--schema`, `--table`, or `--schema-only` is specified.
`-B` / `--no-blobs`	Exclude blobs. If both `-b` and `-B` are specified, blobs are included.
`-c` / `--clean`	Add SQL commands to drop database objects before recreating them. Use with `--if-exists` to avoid errors. Applies to script files only; for archive files, pass this option to `pg_restore`.
`-C` / `--create`	Add commands to create and connect to the database. When combined with `--clean`, the script drops and recreates the database. Without `--no-acl`, comments, configuration information, and access control information of the database are also backed up. Applies to script files only; for archive files, pass this option to `pg_restore`.
`-E encoding` / `--encoding=encoding`	Create the dump using the specified character encoding. Defaults to the database encoding or `PGCLIENTENCODING`.
`-F format` / `--format=format`	Output format. Default: `p`. See Choose an output format for guidance. Options: `p` (plain), `c` (custom), `d` (directory), `t` (tar).
`-f file` / `--file=file`	Output file or directory name. Required for directory format. Optional for other formats (default: standard output).
`-j njobs` / `--jobs=njobs`	Number of parallel jobs for dumping tables. Requires directory format (`-Fd`). Stop all DDL and DML processes that modify the database before running a parallel backup.
`-n pattern` / `--schema=pattern`	Dump only objects in schemas matching the pattern. If omitted, all non-system schemas are included. Blobs are not included with `-n`; add `--blobs` explicitly if needed. Note pg_dump does not back up database objects on which the specified schema may depend, so it cannot be guaranteed that a specific-schema backup can be successfully restored to an empty database.
`-N pattern` / `--exclude-schema=pattern`	Exclude schemas matching the pattern. If both `-n` and `-N` are specified, schemas matching `-n` are included except those also matching `-N`.
`-o` / `--oids`	Include each table's object identifier (OID). Specify this only if your application uses OID columns, for example in foreign key constraints.
`-O` / `--no-owner`	Do not include commands that set object ownership. Applies to script files only; for archive files, pass this option to `pg_restore`.
`-s` / `--schema-only`	Dump schema definitions only, not data.
`-S username` / `--superuser=username`	Superuser name for disabling triggers. Use with `--disable-triggers`.
`-t pattern` / `--table=pattern`	Dump only tables matching the pattern. Specify multiple `-t` options or use wildcards to select multiple tables. Note pg_dump does not back up other database objects on which the selected tables may depend, so it cannot be guaranteed that a specific-table backup can be successfully restored to an empty database.
`-T pattern` / `--exclude-table=pattern`	Exclude tables matching the pattern. If both `-t` and `-T` are specified, tables matching `-t` are included except those also matching `-T`.
`-v` / `--verbose`	Run in verbose mode.
`-V` / `--version`	Print pg_dump version and exit.
`-x` / `--no-privileges` / `--no-acl`	Do not dump access privileges (GRANT and REVOKE statements).
`-Z 0..9` / `--compress=0..9`	Compression level. `0` disables compression. For custom archive files, individual table-data segments are compressed (default: moderate level). Script files are not compressed by default; a non-zero level compresses the entire output file.
`--column-inserts` / `--attribute-inserts`	Dump data as INSERT commands with explicit column names: `INSERT INTO table (column, ...) VALUES ...`.
`--disable-dollar-quoting`	Disable dollar quoting for function bodies; use standard SQL quoting instead.
`--disable-triggers`	Disable triggers on target tables during data-only restores. Requires specifying a superuser with `-S`. Applies to script files only; for archive files, pass this option to `pg_restore`.
`--enable-row-security`	Dump only the rows that the current user is allowed to access, based on row-level security policies. Use INSERT-based backups (`--inserts` or `--column-inserts`) when restoring, because COPY FROM does not support row-level security.
`--exclude-table-data=pattern`	Skip data for tables matching the pattern but include their schema definitions. To skip all table data, use `--schema-only`.
`--if-exists`	Use `IF EXISTS` when dropping objects. Requires `--clean`.
`--inserts`	Dump data as INSERT commands rather than COPY. Restore order may differ; prefer `--column-inserts` for more reliable restores.
`--load-via-partition-root`	Use COPY or INSERT targeting the partition root when dumping data from table partitions. Exercise caution when running parallel restores of archives created with this option.
`--lock-wait-timeout=timeout`	Timeout for acquiring a shared lock at the start of the dump.
`--no-comments`	Do not dump comments.
`--no-publications`	Do not dump publications.
`--no-security-labels`	Do not dump security labels.
`--no-subscriptions`	Do not dump subscriptions.
`--no-sync`	Return immediately without waiting for all output files to be written safely to disk.
`--no-synchronized-snapshots`	Allow `pg_dump -j` to run on the server without synchronized snapshot support.
`--no-tablespaces`	Create all objects in their default tablespaces. Applies to script files only; for archive files, pass this option to `pg_restore`.
`--no-unlogged-table-data`	Do not dump contents of unlogged tables.
`--quote-all-identifiers`	Force quoting of all identifiers, even those that are not reserved words.
`--rows-per-insert=nrows`	Maximum number of rows per INSERT command when dumping data as INSERT commands.
`--section=sectionname`	Dump only the named section: `pre-data`, `data`, or `post-data`. Specify multiple `--section` options to include multiple sections. By default, all sections are dumped. See section definitions in Usage notes.
`--serializable-deferrable`	Use a serializable transaction for the dump. If active read-write transactions are present, the dump starts after a delay of indeterminate length; if none are present, this option has no effect.
`--snapshot=snapshotname`	Use the named synchronized snapshot when dumping the database.
`--strict-names`	Require each `-n`/`--schema` and `-t`/`--table` pattern to match at least one schema or table. Does not affect exclusion patterns (`-N`, `-T`, `--exclude-table-data`). Note pg_dump returns an error if no schema or table is matched, even if `--strict-names` is not specified.
`--use-set-session-authorization`	Use `SET SESSION AUTHORIZATION` instead of `ALTER OWNER` to set object ownership.
`-?` / `--help`	Print help for pg_dump and exit.

Examples

The examples below follow a complete workflow: back up a database, then restore it.

Back up to a script file

pg_dump mydb > db.sql

mydb: Database to back up.

Restore from a script file

psql -d newdb -f db.sql

-d newdb: Target database to restore into.
-f db.sql: The script file produced by pg_dump.

Back up to a custom-format archive

pg_dump -Fc mydb > db.dump

-Fc: Custom format archive, compressed by default. Use pg_restore to restore.

Back up to a directory-format archive

pg_dump -Fd mydb -f dumpdir

-Fd: Directory format, compressed by default. Supports parallel backup and restore.
-f dumpdir: Output directory name.

Back up in parallel using directory format

pg_dump -Fd mydb -j 5 -f dumpdir

-Fd: Required for parallel backup.
-j 5: Run 5 parallel jobs. Stop all DDL and DML operations on the database before running.
-f dumpdir: Output directory.

Restore from a custom-format archive

pg_restore -d newdb db.dump

-d newdb: Target database.
db.dump: Archive file created by pg_dump.

Restore to the original database and clear its contents

pg_restore -d postgres --clean --create db.dump

--clean: Drop existing objects before recreating them.
--create: Drop and recreate the database, then reconnect.

Back up a single table

pg_dump -t mytab mydb > db.sql

-t mytab: Dump only the mytab table.

Back up tables matching a pattern, excluding one table

pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

-t 'detroit.emp*': Include all tables in the detroit schema whose names start with emp.
-T detroit.employee_log: Exclude the employee_log table even if it matches the -t pattern.

Back up schemas matching multiple patterns, excluding one

pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

Or equivalently, using regular expression notation to combine the patterns:

pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

Back up everything except tables with a specific name prefix

pg_dump -T 'ts_*' mydb > db.sql

Back up a table with a mixed-case name

Shell commands interpret double quotation marks, so use nested quoting:

pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql

Without the inner quotes, PostgreSQL folds the name to lowercase and no table is matched.

Usage notes

Run ANALYZE after restore. After restoring a backup, run ANALYZE to update table statistics and ensure optimal query performance.

Section definitions. The --section option accepts the following values:

Section	Contents
`pre-data`	All object definitions other than indexes, triggers, rules, constraints, and check constraints
`data`	Table data, blobs, and sequence values
`post-data`	Indexes, triggers, rules, and constraint definitions (excluding validated check constraints)

Disabling triggers with `--data-only`. When you use --data-only together with --disable-triggers, pg_dump emits commands to disable triggers before inserting data and re-enable them after. If the restore is interrupted, the system catalog may be left in an inconsistent state.

Logical replication subscriptions. When pg_dump backs up logical replication subscription information, it generates CREATE SUBSCRIPTION with the connect=false option. If the host addresses have changed after the backup, update the connection information manually. Also consider truncating the target tables before starting a new full table copy.

Diagnosing pg_dump failures. pg_dump runs SELECT statements internally. If it fails, verify that you can connect to and query the database using psql. Also check that the default connection settings and libpq environment variables are configured correctly.

Statistics collection. pg_dump activity is recorded by the statistics collector. To suppress this, set track_counts to false using PGOPTIONS or ALTER USER.

What's next

pg_restore — restore an archive file created by pg_dump
pg_dumpall — dump all databases in a cluster