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

Background information

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

In this example, an RDS instance that runs PostgreSQL 10 and a host that runs CentOS 7 are used.

Prerequisites

Precautions

We recommend that you use the privileged account of the RDS instance. This ensures 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 of the RDS instance:
    pg_dump -h '<hostname>' -U <username> -p <port> -Fc <dbname> > <dumpdir>
    Parameter Description
    hostname The endpoint that is used to connect to the RDS instance.
    Note
    username The username of the privileged account of the RDS instance.
    port The port that is used to connect to the RDS instance.
    -Fc The format of the output file. -Fc specifies the use of the custom format. This format 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 that 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 on the command line, enter the password of the privileged account of the RDS 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 RDS instance:
    pg_dump -h '<hostname>' -U <username> -p <port> -t <Schema>.<table> -Fc <dbname> > <dumpdir>
    Parameter Description
    hostname The endpoint that is used to connect to the RDS instance.
    Note
    username The username of the privileged account of the RDS instance.
    port The port that is used to connect to the RDS instance.
    Schema The schema of the table that you want to back up. The default value of this parameter is Public. If the schema of the table is Public, you do not need to specify this parameter.
    table The name of the table that you want to back up. You can use -t <Schema>.<table> to specify multiple tables.
    -Fc The format of the output file. -Fc specifies the use of the custom format. This format 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 that 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 on the command line, enter the password of the privileged account of the RDS instance and press the Enter key.
    Back up one or more tables

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 that is used to connect to the RDS instance.
    Note
    username The username of the privileged account of the RDS instance.
    port The port that is used to connect to the RDS instance.
    table The name of the table that you want to exclude. You can use -T <table> to exclude multiple tables.
    -Fc The format of the output file. -Fc specifies the use of the custom format. This format 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 that 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 on the command line, enter the password of the privileged account of the RDS instance and press the Enter key.
    Back up a database with one or more tables excluded

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 that is used to connect to the RDS instance.
    Note
    username The username of the privileged account of the RDS instance.
    port The port that is used to connect to the RDS instance.
    -s Specifies that only the schema of the database is backed up. The data of the database is not backed up. For more information, see pg_dump.
    -Fc The format of the output file. -Fc specifies the use of the custom format. This format 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 that 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 on the command line, enter the password of the privileged account of the RDS instance and press the Enter key.
    Back up the schema of a database with data excluded

References

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