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

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

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

  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
    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
    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 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 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
    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 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 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
    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.
    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.