This topic describes how to create a logical backup for an RDS PostgreSQL instance and export the generated logical backup file to your computer by using the pg_dump utility.

Background information

The pg_dump utility provided with PostgreSQL is used to back up individual databases. For more information, visit pg_dump.

This example uses an RDS PostgreSQL instance that runs Linux 7 and PostgreSQL 10.

Prerequisites

  • The IP address of your ECS instance or on-premises host is added to a whitelist of the RDS 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 RDS instance.

Precautions

We recommend that you use the privileged account of the RDS 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 RDS instance: 
    pg_dump -h '<hostname>' -U <username> -p <port> -Fc <dbname> > <dumpdir>
    Parameter Description
    hostname The endpoint used to connect to the RDS instance.
    Note
    • If your ECS instance connects to the RDS instance by using an internal endpoint, you must make sure that the ECS and RDS instances 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.
    • If your on-premises host or ECS instance connects to the RDS instance by using a public endpoint, you must make sure that a public endpoint has been allocated to the RDS instance. For more information, see Apply for a public endpoint for an RDS PostgreSQL instance.
    username The username of the privileged account of the RDS instance.
    port The port used to connect to the RDS instance.
    -Fc The output file format. -Fc specifies to use the custom format, which is ideal for you to import logical backup files and restore databases by using pg_restore. 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 RDS instance and press Enter.

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 RDS instance: 
    pg_dump -h '<hostname>' -U <username> -p <port> -t <table> -Fc <dbname> > <dumpdir>
    Parameter Description
    hostname The endpoint used to connect to the RDS instance.
    Note
    • If your ECS instance connects to the RDS instance by using an internal endpoint, you must make sure that the ECS and RDS instances 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.
    • If your on-premises host or ECS instance connects to the RDS instance by using a public endpoint, you must make sure that a public endpoint has been allocated to the RDS instance. For more information, see Apply for a public endpoint for an RDS PostgreSQL instance.
    username The username of the privileged account of the RDS instance.
    port The port used to connect to the RDS 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 for you to import logical backup files and restore databases by using pg_restore. For more information, visit pg_dump.
    dbname The name of the database where the table you want to back up resides.
    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 RDS instance and press Enter.

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 RDS 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 RDS instance.
    Note
    • If your ECS instance connects to the RDS instance by using an internal endpoint, you must make sure that the ECS and RDS instances 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.
    • If your on-premises host or ECS instance connects to the RDS instance by using a public endpoint, you must make sure that a public endpoint has been allocated to the RDS instance. For more information, see Apply for a public endpoint for an RDS PostgreSQL instance.
    username The username of the privileged account of the RDS instance.
    port The port used to connect to the RDS 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 for you to import logical backup files and restore databases by using pg_restore. 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-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 RDS instance and press Enter.

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 RDS instance: 
    pg_dump -h '<hostname>' -U <username> -p <port> -s -Fc <dbname> > <dumpdir>
    Parameter Description
    hostname The endpoint used to connect to the RDS instance.
    Note
    • If your ECS instance connects to the RDS instance by using an internal endpoint, you must make sure that the ECS and RDS instances 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.
    • If your on-premises host or ECS instance connects to the RDS instance by using a public endpoint, you must make sure that a public endpoint has been allocated to the RDS instance. For more information, see Apply for a public endpoint for an RDS PostgreSQL instance.
    username The username of the privileged account of the RDS instance.
    port The port used to connect to the RDS instance.
    -s Specifies to only back up the schema of the database. The data of the database is not backed up. For more information, visit pg_dump.
    -Fc The output file format. -Fc specifies to use the custom format, which is ideal for you to import logical backup files and restore databases by using pg_restore. For more information, visit pg_dump.
    dbname The name of the database whose schema 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 RDS instance and press Enter.
    Back up the schema of a database with data excluded

What to do next

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