Restore data to self-managed PostgreSQL using a CSV or SQL file from RDS for PostgreSQL - ApsaraDB RDS

When you need to move data out of ApsaraDB RDS for PostgreSQL — for example, to decommission an instance, archive data, or sync to a development environment — you can download a snapshot backup as a CSV or SQL file and restore it to a self-managed PostgreSQL instance using a provided Python script.

Choose a restore method

Before you begin, confirm this method fits your situation:

Method	Best for	Guide
CSV or SQL file (this guide)	Full snapshot restore from a cloud disk instance	This page
Logical backup with pg_restore	Partial restore, such as a single table	Restore data from a logical backup file
Data Transmission Service (DTS)	Minimal manual effort, ongoing sync	Migrate data from a PostgreSQL database

For a full overview of all restore options, see Overview of data restoration methods.

Prerequisites

Before you begin, make sure that:

Your RDS instance uses cloud disks — this method applies only to cloud disk instances
Python 3 is installed on the target machine:
```
python3 --version
```
The target self-managed PostgreSQL instance does not contain a database with the same name as the one you are importing — if it does, the import fails; drop or rename the conflicting database before proceeding

The steps below use a 64-bit CentOS 7.8 environment as an example. If your operating system differs, use the equivalent commands for your system.

Restore data from a backup file

Log on to the ApsaraDB RDS console and download the backup file of your instance as a CSV or SQL file. For instructions, see Download backup files.

Decompress the downloaded package:

tar -zxvf <package-name>.tar.gz -C <target-directory>

For example, to decompress backup.tar.gz into /home/ecs-test-user:

tar -zxvf backup.tar.gz -C /home/ecs-test-user

(Optional) Confirm that the files were extracted to the target directory:
```
ls -al /home/ecs-test-user
```
Download the PostgreSQL Python script (restore_from_downloads.py) to the same machine.
Grant execute permissions on the script:
```
chmod +x ./restore_from_downloads.py
```

Run the script to restore data to the self-managed PostgreSQL instance:

Parameter	Description	Example
`<data-directory>`	Directory containing the extracted CSV or SQL files	`/home/ecs-test-user`
`<host>`	Host address of the self-managed PostgreSQL instance	`127.0.0.1`
`<port>`	Port number	`5432`
`<username>`	Database account username. If the username contains special characters such as `#` or spaces, enclose it in double quotation marks (`"`).	`postgres`
`<password>`	Database account password. If the password contains special characters such as `#` or spaces, enclose it in double quotation marks (`"`).	`"#Tes********"`

python3 restore_from_downloads.py <data-directory> <host> <port> <username> <password>

Example:

python3 restore_from_downloads.py /home/ecs-test-user 127.0.0.1 5432 postgres "#Tes********"