This topic describes how to use pg_dump to create a logical backup for an ApsaraDB RDS for PostgreSQL instance and export the backup file to your computer.
Background information
This topic uses an ApsaraDB RDS for PostgreSQL instance that runs Linux 7 and PostgreSQL 10.
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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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.