Use rds_dbsync to migrate data from PostgreSQL to AnalyticDB for PostgreSQL - AnalyticDB

pgsql2pgsql is a tool built into the open source rds_dbsync package. Use it to migrate or synchronize data between PostgreSQL, PPAS, Greenplum Database, and AnalyticDB for PostgreSQL instances.

Supported operations

pgsql2pgsql supports two modes:

Mode	Description	Supported source versions
Full data migration	Migrates all table data between any combination of PostgreSQL, PPAS, Greenplum Database, and AnalyticDB for PostgreSQL	Any version
Full and incremental data migration	Migrates data continuously, including changes captured after the initial load	PostgreSQL or PPAS 9.4 or later (destination must also be PostgreSQL or PPAS)

Prerequisites

For full data migration

Before configuring pgsql2pgsql, make sure you have:

Owner credentials for the source PostgreSQL database
Write permission on the destination table in the destination database

For full and incremental data migration

In addition to the full migration prerequisites, make sure you have:

Permission to create replication slots in the source database
Logical replication enabled on the source PostgreSQL instance (version 9.4 or later)

To enable logical replication, set the following parameters in postgresql.conf on the source instance:

wal_level = logical
max_wal_senders = 6
max_replication_slots = 6

Configure connections

pgsql2pgsql reads connection details from postgresql.conf. Configure the three connection sections as follows:

Source database:

[src.pgsql]
connect_string = "host=192.168.1.1 dbname=test port=3432 user=test password=pgsql"

Set user to the owner of the source PostgreSQL database.

On-premises temporary database:

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

Destination database:

[desc.pgsql]
connect_string = "host=192.168.1.3 dbname=test port=3432 user=test3 password=pgsql"

The user in the destination connection must have write permission on the destination table.

Run a full migration

After configuring postgresql.conf, run the following command to start a full migration:

./pgsql2pgsql

This migrates all user table data from the source database to the destination database.

Monitor migration status

Connect to the on-premises temporary database and query the db_sync_status table to check migration progress:

SELECT * FROM db_sync_status;

The table stores the following information:

Start and end time of full data migration
Start time of incremental data migration
Status of incremental data synchronization

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