This topic describes how to create a logical backup for your ApsaraDB RDS for PPAS instance. This topic also provides further details about how to restore data from the created logical backup.

Prerequisites

EnterpriseDB is installed on your ECS instance or on-premises server that hosts your RDS instance.

Procedure

  1. Grant an account all of the permissions that are owned by the other accounts on your RDS instance. This account is used to export data.
    In this example, three accounts, User A, User B, and User C, are created on your RDS instance. Before you export data by using User A, you must run the following commands to grant the permissions of User B and User C to User A:
    -- Log on to your RDS instance by using User B and run the following command:
     grant B to A;
     -- Log on to your RDS instance by using User C and run the following command:
     grant C to A;

    In this case, User A has the permissions to access all of the data tables on which User B and User C are authorized.

  2. Go to the directory where pg_dump is stored and run the following command (the default directory is /usr/pgsql-10/bin/):
    ./pg_dump -h <host> -p <port> -U <user> -f dump.sql <dbname>
  3. To restore data, run the following commands in the directory where psql is stored (the default directory is /usr/pgsql-10/bin/):
    ./psql  -h <host> -p <port> -U <user> -d postgres -c "drop database <dbname>"
     ./psql  -h <host> -p <port> -U <user> -d postgres -c "create database <dbname>"
     ./psql  -h <host> -p <port> -U <user> -f dump.sql -d <dbname>

FAQ

  1. What do I do if the following permission error occurs when I export data from my RDS for PPAS instance?
    ERROR:  permission denied for relation product_component_version
     LOCK TABLE sys.product_component_version IN ACCESS SHARE MODE

    You are using pg_dump provided with PostgreSQL to export data from your RDS for PPAS instance. You must use PPAS binaries. For more information about how to download PPAS binaries, see the preceding steps.

  2. What do I do if the following permission error occurs when I export data from my RDS for PPAS instance?
     ERROR:  permission denied for relation <The name of a user table>

    The account that you use to export data does not have the permissions on the specified user table. If approved, you can grant an account all of the permissions that are owned by the other accounts on your RDS for PPAS instance. Then, run the following command by using this account:

    GRANT ROLE <other roles>,<other roles> to <user for pg_dump>
  3. What do I do if the following problem occurs when I run pg_dump?
    pgdump -U xxx -h yyy -p3433 <dbname> -f my.sql
     pg_dump: A significantly large number of parameters are specified in the command line. The first parameter is -f.

    When you run pg_dump in a Windows operating system, you must affix the <dbname> parameter to the end of the command line.

  4. What do I do if a parameter error occurs when I run pg_dump?

    The parameters that you specified may be incorrect. For example, pg_dump -Uxxx -h yyy is not allowed. You must use a space to follow -U. This rule also applies to other parameters.