Migrate data from an ApsaraDB RDS for PostgreSQL database to an on-premises PostgreSQL database - RDS PostgreSQL Database| Alibaba Cloud Documentation Center

This topic describes how to migrate data from an ApsaraDB RDS for PostgreSQL database to an on-premises PostgreSQL database. You can perform the migration by using a logical backup file.

Procedure

Connect to the ApsaraDB RDS for PostgreSQL database by using the PostgreSQL client. For more information, visit the pgAdmin download page.
Run the following command to back up the ApsaraDB RDS for PostgreSQL database:
```
pg_dump -U username -h hostname -p port databasename -f filename --exclude-table=public.ha_health_check
```
Parameter description:
- username: the username that is used to log on to the ApsaraDB RDS for PostgreSQL database.
- hostname: the name of the host where the ApsaraDB RDS for PostgreSQL database resides.
- port: the port number that is used to log on to the ApsaraDB RDS for PostgreSQL database.
- databasename: the name of the ApsaraDB RDS for PostgreSQL database.
- filename: the name to use for the logical backup file.
- --exclude-table=public.ha_health_check: specifies to skip the high-availability check.
Example:
```
pg_dump -U myuser -h rds2z2tp80v3752wb455.pg.rds.aliyuncs.com -p 3433 pg001 -f pg001.sql --exclude-table=public.ha_health_check
```
Upload the logical backup file pg001.sql to the server where the on-premises PostgreSQL database resides.
Run the following command to restore data from the logical backup file to the on-premises PostgreSQL database:
```
psql -U username -h hostname -d desintationdb -p port -f dumpfilename.sql
```
Parameter description:
- username: the username that is used to log on to the on-premises PostgreSQL database.
- hostname: the name of the host where the on-premises PostgreSQL database resides.
- port: the port number that is used to log on to the on-premises PostgreSQL database.
- desintationdb: the name of the on-premises PostgreSQL database.
- dumpfilename: the name of the logical backup file from which you want to restore data.
Example:
```
psql -U myuser -h localhost -d pg001 -p 5432 -f pg001.sql
```
The ApsaraDB RDS for PostgreSQL database and the on-premises PostgreSQL database have different parameter settings. As a result, some permission-related warnings or errors may occur when you import data into the on-premises PostgreSQL database. You can ignore these warnings and errors. Examples:
```
WARNING:  no privileges could be revoked for "xxxxx"
ERROR:  role "xxxxx" does not exist
```