Setting up Logical Replication with PostgreSQL

PostgreSQL is a powerful open-source database management system that supports many advanced features such as logical replication. Logical replication allows for the replication of individual tables or entire databases between servers, making it an ideal solution for scenarios where you need to replicate only a subset of data or when you need to replicate data to multiple destinations.

In this tutorial, we will walk through the steps required to set up logical replication between a source PostgreSQL database and a target PostgreSQL database using the pglogical extension.

Prerequisites

• A source PostgreSQL database
• A target PostgreSQL database
• Access to both databases
• PostgreSQL version 10 or higher

Configuration

Before we begin setting up logical replication, there are a few configurations we need to make on both the source and target databases.

Source Database Configuration

We need to enable logical replication on the source database by setting the rds.logical_replication parameter to 1. We also need to configure the shared_preload_libraries, max_worker_processes, max_replication_slots, max_wal_senders, and track_commit_timestamp parameters in the postgresql.conf file.

rds.logical_replication=1
shared_preload_libraries = 'pglogical'
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
track_commit_timestamp = 1

Next, we need to create an extension for pglogical:

CREATE EXTENSION pglogical;

Then, we need to create a node on the source database using the pglogical.create_node function:

SELECT pglogical.create_node(
    node_name := 'rp_publisher',
    dsn := 'host=<source_db_host> port=5432 dbname=postgres user=postgres password=<source_db_password>'
);

Finally, we need to add all tables and sequences in the public schema to the replication set using the pglogical.replication_set_add_all_tables and pglogical.replication_set_add_all_sequences functions:

SELECT pglogical.replication_set_add_all_tables('default', '{public}'::text[]);
SELECT pglogical.replication_set_add_all_sequences(set_name := 'default', schema_names := '{public}'::text[], synchronize_data := true );

Target Database Configuration

On the target database, we need to configure the wal_level, max_worker_processes, max_replication_slots, max_wal_senders, shared_preload_libraries, track_commit_timestamp, session_replication_role, azure.extensions, and require_secure_transport parameters in the postgresql.conf file.

wal_level = 'logical'
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'
track_commit_timestamp = on
session_replication_role = 'replica'
azure.extensions = 'pglogical'
require_secure_transport = off

Next, we need to create an extension for pglogical:

CREATE EXTENSION pglogical;

Then, we need to create a node on the target database using the pglogical.create_node function:

SELECT pglogical.create_node(
    node_name := 'rp_subscriber',
    dsn := 'host=<target_db_host> port=5432 dbname=postgres user=postgres password=<target_db_password>'
);

Finally, we need to create a subscription on the target database using the pglogical.create_subscription function:

SELECT pglogical.create_subscription(
    subscription_name := 'rp_subscription',
    provider_dsn := 'host=<source_db_host> port=5432

With these steps complete, you should now have logical replication set up between your two PostgreSQL databases. Any changes made to the source database will be automatically replicated in the target database.