Create a logical backup for an ApsaraDB RDS for PostgreSQL instance

Prerequisites

• The IP address of your ECS instance or on-premises host is added to the whitelist of an ApsaraDB RDS for PostgreSQL instance. For more information, see Configure a whitelist for an RDS PostgreSQL instance.
• Your ECS instance or on-premises host runs the same version of PostgreSQL as the instance.

Precautions

We recommend that you use the privileged account of the ApsaraDB RDS for PostgreSQL instance to ensure that you have all the required permissions.

Back up a database

1. Log on to your ECS instance or on-premises host. Then, run the following command to back up a database from the ApsaraDB RDS for PostgreSQL instance:

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

   Parameter	Description
   hostname	The endpoint used to connect to the ApsaraDB RDS for PostgreSQL instance. Note If your ECS instance connects to the ApsaraDB RDS for PostgreSQL instance by using an internal endpoint, you must make sure that the ECS instance and the RDS instance have the same network type. If both instances use the VPC network type, you must also make sure that they reside in the same VPC. For more information, see View the internal and public endpoints of an instance. If your on-premises host or ECS instance connects to the ApsaraDB RDS for PostgreSQL instance by using a public endpoint, you must make sure that the public endpoint has been allocated to the RDS instance. For more information, see View the internal and public endpoints of an instance and Apply for a public endpoint for an RDS PostgreSQL instance.
   username	The username of the privileged account of the ApsaraDB RDS for PostgreSQL instance.
   port	The port used to connect to the ApsaraDB RDS for PostgreSQL instance.
   -Fc	The output file format. -Fc specifies to use the custom format, which is ideal when you use pg_restore to import logical backup files and restore databases. For more information, visit pg_dump.
   dbname	The name of the database you want to back up.
   dumpdir	The directory and name of the logical backup file to export.

   Example

   pg_dump -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U test123 -p 3433 -Fc testdb > /tmp/testdb.dump

2. When Password: appears, enter the password of the privileged account of the ApsaraDB RDS for PostgreSQL instance and press the Enter key.
   

Back up one or more tables

1. Log on to your ECS instance or on-premises host. Then, run the following command to back up one or more tables from a database in the ApsaraDB RDS for PostgreSQL instance:

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

   Parameter	Description
   hostname	The endpoint used to connect to the ApsaraDB RDS for PostgreSQL instance. Note If your ECS instance connects to the ApsaraDB RDS for PostgreSQL instance by using an internal endpoint, you must make sure that the ECS instance and the RDS instance have the same network type. If both instances use the VPC network type, you must also make sure that they reside in the same VPC. For more information, see View the internal and public endpoints of an instance. If your on-premises host or ECS instance connects to the ApsaraDB RDS for PostgreSQL instance by using a public endpoint, you must make sure that the public endpoint has been allocated to the RDS instance. For more information, see View the internal and public endpoints of an instance and Apply for a public endpoint for an RDS PostgreSQL instance.
   username	The username of the privileged account of the ApsaraDB RDS for PostgreSQL instance.
   port	The port used to connect to the ApsaraDB RDS for PostgreSQL instance.
   table	The name of the table you want to back up. You can use -t <table> to specify more than one table.
   -Fc	The output file format. -Fc specifies to use the custom format, which is ideal when you use pg_restore to import logical backup files and restore databases. For more information, visit pg_dump.
   dbname	The name of the database you want to back up.
   dumpdir	The directory and name of the logical backup file to export.

   Example

   pg_dump -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U test123 -p 3433 -t products1 -Fc testdb2 > /tmp/testdb2.dump

2. When Password: appears, enter the password of the privileged account of the ApsaraDB RDS for PostgreSQL instance and press the Enter key.
   

Back up a database with one or more tables excluded

1. Log on to your ECS instance or on-premises host. Then, run the following command to back up a database from the ApsaraDB RDS for PostgreSQL instance with one or more tables excluded:

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

   Parameter	Description
   hostname	The endpoint used to connect to the ApsaraDB RDS for PostgreSQL instance. Note If your ECS instance connects to the ApsaraDB RDS for PostgreSQL instance by using an internal endpoint, you must make sure that the ECS instance and the RDS instance have the same network type. If both instances use the VPC network type, you must also make sure that they reside in the same VPC. For more information, see View the internal and public endpoints of an instance. If your on-premises host or ECS instance connects to the ApsaraDB RDS for PostgreSQL instance by using a public endpoint, you must make sure that the public endpoint has been allocated to the RDS instance. For more information, see View the internal and public endpoints of an instance and Apply for a public endpoint for an RDS PostgreSQL instance.
   username	The username of the privileged account of the ApsaraDB RDS for PostgreSQL instance.
   port	The port used to connect to the ApsaraDB RDS for PostgreSQL instance.
   table	The name of the table you want to exclude. You can use -T <table> to specify more than one table.
   -Fc	The output file format. -Fc specifies to use the custom format, which is ideal when you use pg_restore to import logical backup files and restore databases. For more information, see pg_dump.
   dbname	The name of the database you want to back up.
   dumpdir	The directory and name of the logical backup file to export.

   Example

   pg_dump -h 'pgm-bpxxxxx.pg.rds.aliyuncs.com' -U test123 -p 3433 -T products1 -Fc testdb2 > /tmp/testdb2.dump

2. When Password: appears, enter the password of the privileged account of the ApsaraDB RDS for PostgreSQL instance and press the Enter key.
   

Back up the schema of a database with data excluded

1. Log on to your ECS instance or on-premises host. Then, run the following command to back up the schema of a database from the ApsaraDB RDS for PostgreSQL instance.

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

   Parameter	Description
   hostname	The endpoint used to connect to the ApsaraDB RDS for PostgreSQL instance. Note If your ECS instance connects to the ApsaraDB RDS for PostgreSQL instance by using an internal endpoint, you must make sure that the ECS instance and the RDS instance have the same network type. If both instances use the VPC network type, you must also make sure that they reside in the same VPC. For more information, see View the internal and public endpoints of an instance. If your on-premises host or ECS instance connects to the ApsaraDB RDS for PostgreSQL instance by using a public endpoint, you must make sure that the public endpoint has been allocated to the RDS instance. For more information, see View the internal and public endpoints of an instance and Apply for a public endpoint for an RDS PostgreSQL instance.
   username	The username of the privileged account of the ApsaraDB RDS for PostgreSQL instance.
   port	The port used to connect to the ApsaraDB RDS for PostgreSQL instance.
   -s	Specifies to only back up the schema of the database. The data of the database is not backed up. For more information, see pg_dump.
   -Fc	The output file format. -Fc specifies to use the custom format, which is ideal when you use pg_restore to import logical backup files and restore databases. For more information, see pg_dump.
   dbname	The name of the database you want to back up.
   dumpdir	The directory and name of the logical backup file to export.

   Example

   pg_dump -h 'pgm-bpxxxxx.pg.rds.aliyuncs.com' -U test123 -p 3433 -s -Fc testdb2 > /tmp/testdb2.dump

2. When Password: appears, enter the password of the privileged account of the ApsaraDB RDS for PostgreSQL instance and press the Enter key.
   

References

If you need to restore data due to a database exception, see Restore data from a logical backup file.