Pglogical Replication

What is pglogical?

pglogical is an extension for PostgreSQL that provides logical replication capabilities. It allows you to replicate data changes (inserts, updates, deletes) at the row level between PostgreSQL databases. Unlike physical replication, which replicates at the block level, logical replication works at a higher level, replicating changes based on database objects (tables, schemas) and their changes.

Similarities with Logical Replication:

1. Logical Change Tracking: Both pglogical and native logical replication track changes made to database objects (tables) using a logical decoding mechanism.

2. Row-Level Replication: Both methods support replicating changes at the row level, meaning individual rows inserted, updated, or deleted in a table are replicated to the subscriber.

3. Transactional Integrity: Both methods maintain transactional integrity, ensuring that changes are replicated in a transactionally consistent manner.

Differences from Native Logical Replication:

1. Flexibility: pglogical offers more flexibility in terms of replication configurations. It allows for multi-master replication setups and selective replication of tables or databases.

2. Additional Features: pglogical provides additional features like conflict resolution mechanisms, replication sets for selective replication, DDL replication, and support for replicating to different versions of PostgreSQL.

3. Complexity: Setting up pglogical replication may be more complex compared to native logical replication due to its additional features and configuration options.

4. Dependencies: pglogical requires the installation of the pglogical extension, whereas native logical replication is built into PostgreSQL starting from version 10.

1. Enable Logical Replication Parameters:

   • Ensure that logical replication parameters are enabled in both the source and target databases. These parameters include rds.logical_replication, shared_preload_libraries, wal_level, max_worker_processes, max_replication_slots, and max_wal_senders.
   • On the source RDS instance (common.cluster-cx1cvruqmq2b.us-east-1.rds.amazonaws.com), modify the parameter group to set these values:
      

     rds.logical_replication = 1 shared_preload_libraries = 'pglogical' wal_level = 'logical' max_worker_processes = 10 #always according to Environment Needs max_replication_slots = 10 #always according to Environment Needs max_wal_senders = 10 #always according to Environment Needs

     1 2 3 4 5 6 7 8

     rds.logical_replication = 1 shared_preload_libraries = 'pglogical' wal_level = 'logical' max_worker_processes = 10 #always according to Environment Needs max_replication_slots = 10 #always according to Environment Needs max_wal_senders = 10 #always according to Environment Needs
   • Similarly, modify the parameter group for the target RDS instance (sgds.cluster-cx1cvruqmq2b.us-east-1.rds.amazonaws.com).

2. Create Roles and Extensions:

   • Create a user and grant the necessary privileges on both the source and target databases.
      

     -- Source Database CREATE USER pguser WITH PASSWORD 'pguser123'; GRANT rds_superuser TO pguser; -- Target Database CREATE USER pguser WITH PASSWORD 'pguser123'; GRANT rds_superuser TO pguser;

     1 2 3 4 5 6 7

     -- Source Database CREATE USER pguser WITH PASSWORD 'pguser123'; GRANT rds_superuser TO pguser;   -- Target Database CREATE USER pguser WITH PASSWORD 'pguser123'; GRANT rds_superuser TO pguser;

   • Then, create the pglogical extension in both databases.
      

     -- Source CREATE EXTENSION pglogical; -- Target CREATE EXTENSION pglogical;

     1 2 3 4

     -- Source CREATE EXTENSION pglogical; -- Target CREATE EXTENSION pglogical;

3. Create Nodes:

   • On the source database, create a node for the provider (source) database.
      

     SELECT pglogical.create_node( node_name := 'provider_node', dsn := 'host=common.cluster-cx1cvruqmq2b.us-east-1.rds.amazonaws.com port=5432 dbname=classicdba_src user=postgres password=postgres123' );

     1 2 3 4 5 6 7 8 9 10

     SELECT pglogical.create_node( node_name := 'provider_node', dsn := 'host=common.cluster-cx1cvruqmq2b.us-east-1.rds.amazonaws.com port=5432 dbname=classicdba_src user=postgres password=postgres123' );

   • On the target database, create a node for the subscriber (target) database.
      

     SELECT pglogical.create_node( node_name := 'subscriber_node', dsn := 'host=sgds.cluster-cx1cvruqmq2b.us-east-1.rds.amazonaws.com port=5432 dbname=classicdba_tgt user=postgres password=postgres123' );

     1 2 3 4 5 6 7 8 9 10

     SELECT pglogical.create_node( node_name := 'subscriber_node', dsn := 'host=sgds.cluster-cx1cvruqmq2b.us-east-1.rds.amazonaws.com port=5432 dbname=classicdba_tgt user=postgres password=postgres123' );

4. Create Replication Set:

   • Create a replication set to define which tables will be replicated.
      

     -- Source Database SELECT pglogical.create_replication_set('my_replication_set');

     1 2 3 4 5

     -- Source Database   SELECT pglogical.create_replication_set('my_replication_set');

   • Add tables to the replication set. For example:
      

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

     1 2 3

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

5. Create Subscription:

   • On the target database, create a subscription to receive replicated data from the source database.
     SELECT pglogical.create_subscription( subscription_name := 'my_subscription', provider_dsn := 'host=common.cluster-cx1cvruqmq2b.us-east-1.rds.amazonaws.com port=5432 dbname=commondb user=postgres password=postgres123', replication_sets := ARRAY['my_replication_set'], synchronize_data := true );

     1 2 3 4 5 6 7 8 9 10 11

     SELECT pglogical.create_subscription( subscription_name := 'my_subscription', provider_dsn := 'host=common.cluster-cx1cvruqmq2b.us-east-1.rds.amazonaws.com port=5432 dbname=commondb user=postgres password=postgres123', replication_sets := ARRAY['my_replication_set'], synchronize_data := true );

6. Verify Subscription:

   • Verify the subscription status to ensure replication is functioning as expected.
      

     SELECT * FROM pglogical.show_subscription_status();

     1 2 3

     SELECT * FROM pglogical.show_subscription_status();

These steps should set up unidirectional pglogical replication from the source database (common.cluster-cx1cvruqmq2b.us-east-1.rds.amazonaws.com) to the target database (sgds.cluster-cx1cvruqmq2b.us-east-1.rds.amazonaws.com). Make sure to replace placeholder values like usernames, passwords, and node names with your actual configuration details.