Use pg_dump to create a logical backup for PostgreSQL - ApsaraDB RDS

Use pg_dump to create a logical backup of an ApsaraDB RDS for PostgreSQL instance and export the backup file for local storage or migration.

pg_dump backs up one database at a time. For the official reference, see pg_dump.

Prerequisites

Before you begin, make sure that:

The IP address of your Elastic Compute Service (ECS) instance or on-premises host is added to the IP address whitelist of the RDS instance. For more information, see Configure an IP address whitelist.
Your ECS instance or on-premises host runs the same PostgreSQL version as your RDS instance.

The examples in this topic use CentOS 7 and PostgreSQL 15.

Use the privileged account of the RDS instance to run pg_dump. For instructions on creating a privileged account, see Create an account.

Endpoint selection

All backup commands require a connection endpoint (-h). Choose based on your network setup:

Internal endpoint: The ECS instance and the RDS instance must use the same network type. If both use Virtual Private Cloud (VPC), they must reside in the same VPC. For more information, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.
Public endpoint: A public endpoint must be allocated to the RDS instance before use. For more information, see View and change the endpoints and port numbers and Apply for or release a public endpoint.

pg_dump -h '<hostname>' -U <username> -p <port> -Fc <dbname> > <dumpdir>

Parameters

Parameter	Description
`hostname`	The endpoint of the RDS instance.
`username`	The username of the privileged account.
`port`	The port of the RDS instance.
`-Fc`	Outputs in custom format. Required for `pg_restore` and supports selective table restore.
`dbname`	The name of the database to back up.
`dumpdir`	The output path and filename for the backup file.

Example

pg_dump -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -Fc zht01 > /tmp/testdb.dump

Enter the privileged account password when prompted, then press Enter.

To verify the backup file was created (Linux):

ll /tmp/testdb.dump

pg_dump -h '<hostname>' -U <username> -p <port> -t <Schema>.<table> -Fc <dbname> > <dumpdir>

Add -t <Schema>.<table> multiple times to include additional tables.

Additional parameter

Parameter	Description
`-t <Schema>.<table>`	The table to back up, specified as `schema.table`. The default schema is Public. Omit the schema prefix for tables in the Public schema.

For the remaining parameters (hostname, username, port, -Fc, dbname, dumpdir), see Back up a database.

Example

pg_dump -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -t zhttest0808 -Fc zht01 > /tmp/testdb2.dump

Enter the privileged account password when prompted, then press Enter.

To verify the backup file was created (Linux):

ll /tmp/testdb2.dump

pg_dump -h '<hostname>' -U <username> -p <port> -T <table> -Fc <dbname> > <dumpdir>

Add -T <table> multiple times to exclude additional tables.

Additional parameter

Parameter	Description
`-T <table>`	The table to exclude from the backup.

For the remaining parameters, see Back up a database.

Example

pg_dump -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -T zhttest0808 -Fc zht01 > /tmp/testdb3.dump

Enter the privileged account password when prompted, then press Enter.

To verify the backup file was created (Linux):

ll /tmp/testdb3.dump

pg_dump -h '<hostname>' -U <username> -p <port> -s -Fc <dbname> > <dumpdir>

Additional parameter

Parameter	Description
`-s`	Backs up the database schema only. Data is not included.

For the remaining parameters, see Back up a database.

Example

pg_dump -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -s -Fc zht01 > /tmp/testdb4.dump

Enter the privileged account password when prompted, then press Enter.

To verify the backup file was created (Linux):

ll /tmp/testdb4.dump

To restore data from a logical backup file, see Restore data from a logical backup file.