Use rds_dbsync to migrate or synchronize data from a PostgreSQL database to an AnalyticDB for PostgreSQL database - AnalyticDB for PostgreSQL

pgsql2pgsql is built in the open source tool rds_dbsync and can be used to migrate PostgreSQL data. You can use the pgsql2pgsql tool to migrate or synchronize data between PostgreSQL, PPAS, Greenplum Database, and AnalyticDB for PostgreSQL.

Features of pgsql2pgsql

pgsql2pgsql supports the following features:

Full data migration from a PostgreSQL database, PPAS database, Greenplum Database, or AnalyticDB for PostgreSQL database to another PostgreSQL database, PPAS database, Greenplum Database, or AnalyticDB for PostgreSQL database.
Full and incremental data migration from a PostgreSQL or PPAS database (version 9.4 or later) to another PostgreSQL or PPAS database.

Parameter configuration

Modify the postgresql.conf file and configure the connection information of the source and destination databases.

Connection information of the source database:
Important
We recommend that you set the user parameter to the owner of the source PostgreSQL database in the connection information of the database.
```
[src.pgsql]
connect_string = "host=192.168.1.1 dbname=test port=3432 user=test password=pgsql"
```

Connection information of the on-premises temporary database:

[local.pgsql]
connect_string = "host=192.168.1.2 dbname=test port=3432 user=test2 password=pgsql"

Connection information of the destination database:
Important
You must have the write permission on the destination table in the destination database.
```
[desc.pgsql]
connect_string = "host=192.168.1.3 dbname=test port=3432 user=test3 password=pgsql"
```

Important

If you want to synchronize incremental data, you must have the permissions to create replication slots in the source database.
PostgreSQL 9.4 and later support the logic flow replication feature. You can migrate incremental data from source databases of these versions. To enable the logic flow replication feature, configure the following parameters:
```
wal_level = logical
max_wal_senders = 6
max_replication_slots = 6
```

pgsql2pgsql usage

Full database migration

Run the following command to perform a full database migration:

./pgsql2pgsql

By default, the migration program migrates the table data of all users from the source PostgreSQL database to the destination PostgreSQL database.

Status query

You can connect to the on-premises temporary database to query the status information of a migration job. The status information is stored in the db_sync_status table and includes the start and end time of full data migration, the start time of incremental data migration, and the status of incremental data synchronization.

Download and instructions

Download the binary installation package of rds_dbsync.
View the instructions for compiling the source code of rds_dbsync.