PostgreSQL pglogical extension

In the source database, edit the postgresql.conf file to add the following parameters.

wal_level = 'logical'
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'

Restart the source PostgreSQL instance for these parameters to take effect.

To make sure that you configured parameters on your source database correctly, run the following command.

psql -h <hostname> -p 5432 -U <username> -d <database_name> -c "select name, setting from pg_settings where name in ('rds.logical_replication','shared_preload_libraries');"

-h is the name of source server where you would like to migrate your database.
-U is the name of the user present on the source server.
-d is the name of database name present on source already.

To turn on the logical replication in the target database, set the following parameters in the database parameter group. For more information, see Working with parameter groups.

rds.logical_replication=1
shared_preload_libraries = 'pglogical'

Reboot your Amazon RDS instance for these parameters to take effect.

To make sure that you configured parameters on your target database correctly, run the following command.

psql -h <hostname> -p 5432 -U <username> -d <database_name> -c "select name, setting from pg_settings where name in ('rds.logical_replication','shared_preload_libraries');"

-h is the name of target server where you would like to migrate your database.
-U is the name of the user present on the target server.
-d is the name of database name present on target already.

Download the pglogical rpm and install it on your source database.

Create the pglogical extension on your provider and subscriber.

CREATE EXTENSION pglogical;

Create the publisher node on your source database.

SELECT pglogical.create_node(
    node_name := 'publisher_name',
    dsn := 'host=<publisher_hostname> port=port_number dbname=<database_name>'
);

-publisher_name: Provide the name of the publication created at source.
-publisher_hostname: Provide the hostname of the source database.
-port_number: Provide the port on which the source database is running.
-database_name: Provide the name of the database where the publication is created.

Add tables in public schema to the default replication set.

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

Create the subscriber node on target database.

SELECT pglogical.create_node(
    node_name := 'subscriber_name',
    dsn := 'host=<subscriber_hostname> port=port_number dbname=<database_name>'
);

-subscriber_hostname: Provide the hostname of the target database.
-port_number: Provide the port on which the target database is running.
-database_name: Provide the name of the database where the subscription is created.
-subscriber_name: Provide the name of the subscription created at target.

Create the subscription on the subscriber node. This subscription starts synchronization and replication processes in background.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription_name',
    provider_dsn := 'host=<publisher_hostname> port=port_number dbname=<database_name>'
);

SELECT pglogical.wait_for_subscription_sync_complete('subscription_name');

-publisher_hostname: Provide the hostname of the source database.
-port_number: Provide the port on which the target database is running.
-database_name: Provide the name of the database where the subscription is created.
-subscription_name: Provide the name of the subscription created at target.