Part 2: pglogical – Flexible Logical Replication for PostgreSQL

In Part 1, we explored what pglogical is, how it works internally, and why it plays such an important role in modern PostgreSQL architectures. In this part, we move from concepts to practice, covering setup basics and a real-world bidirectional replication example where pglogical truly shines.

pglogical Setup Overview

Setting up pglogical is relatively straightforward, provided a few key prerequisites are met. While pglogical offers advanced replication capabilities, its initial setup follows a clear and structured process.

At a high level, the setup involves:

Preparing PostgreSQL for logical replication
Enabling the pglogical extension
Registering databases as replication nodes
Configuring bidirectional replication

Prerequisites

Before configuring pglogical, ensure the following requirements are in place:

PostgreSQL is installed on both the provider and subscriber servers
Superuser or replication-level privileges
Network connectivity between the PostgreSQL servers
wal_level set to logical

Meeting these prerequisites ensures PostgreSQL can generate and stream logical changes correctly.

PostgreSQL Configuration

Logical replication requires specific PostgreSQL parameters to be configured on the provider (and often on the subscriber as well):

wal_level = logical

max_replication_slots = 10

max_wal_senders = 10

shared_preload_libraries = ‘pglogical’

After applying these changes, restart PostgreSQL for them to take effect.

Create a database and create a table on both servers.

postgres=# CREATE DATABASE mydb;
CREATE DATABASE
postgres=# \c mydb
You are now connected to the database "mydb" as user "postgres".

Create the extensions pglogical and pgcrypto on both servers

mydb=# create extension pglogical;
CREATE EXTENSION
mydb=#create extension pgcrypto;
CREATE EXTENSION

Create table

 mydb=# CREATE TABLE employees (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50),
    salary NUMERIC(10,2),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE

Creating node 1 on one server:

mydb=# SELECT pglogical.create_node(
    node_name := 'node1',
    dsn := 'host=172.31.6.116 port=5432 dbname=mydb user=postgres password=password'
);
 create_node
-------------
  1148549230
(1 row)

Creating node 2 on another server

mydb=# SELECT pglogical.create_node(
    node_name := 'node2',
    dsn := 'host=172.31.13.164 port=5432 dbname=mydb user=postgres password=password'
);
 create_node
-------------
  3367056606
(1 row)

Then create the replication set and add the table to the replication set on both node 1 and node 2.

mydb=# SELECT pglogical.create_replication_set(
    set_name := 'my_replication_set'
);
 create_replication_set
------------------------
             2443124447
(1 row)

mydb=# SELECT pglogical.replication_set_add_table(
    set_name := 'my_replication_set',
    relation := 'employees',
    synchronize_data := true
);
 replication_set_add_table
---------------------------
 t
(1 row)

Create subscriptions on node 1 and node 2

On node 1:

mydb=# SELECT pglogical.create_subscription(
    subscription_name := 'sub_node1_to_node2',
    provider_dsn := 'host=172.31.13.164 port=5432 dbname=mydb user=postgres password=password',
    replication_sets := ARRAY['my_replication_set']
);
 create_subscription
---------------------
          3951437680
(1 row)

On node 2:

mydb=# SELECT pglogical.create_subscription(
    subscription_name := 'sub_node2_to_node1',
    provider_dsn := 'host=172.31.6.116 port=5432 dbname=mydb user=postgres password=password',
    replication_sets := ARRAY['my_replication_set']
);
 create_subscription
---------------------
          1060992920
(1 row)

Test the Replication

On node 1, insert the data

mydb=# INSERT INTO employees (name, salary)
VALUES ('Bob', 60000.00);
INSERT 0 1

On node 2

mydb=# SELECT name, salary FROM employees;
name | salary
------+----------
Bob  | 60000.00
(1 row)

Insert from node 2

mydb=# INSERT INTO employees (name, salary)
VALUES ('Alice', 230000.00);
INSERT 0 1

Check on node 1

mydb=# SELECT name, salary FROM employees; 
 name  | salary
-------+----------
Bob   | 60000.00
Alice | 230000.00
(2 rows)

Do UPDATE, DELETE, and test the replication

For monitoring

To check the replication status

On node 1

mydb=# SELECT * FROM pglogical.show_subscription_status();
-[ RECORD 1 ]-----+-------------------------------------------------------------------------
subscription_name | sub_node1_to_node2
status            | replicating
provider_node     | node2
provider_dsn      | host=172.31.13.164 port=5432 dbname=mydb user=postgres password=password
slot_name         | pgl_mydb_node2_sub_nodeeb86277
replication_sets  | {my_replication_set}
forward_origins   | {all}

On node 2

mydb=# SELECT * FROM pglogical.show_subscription_status();
-[ RECORD 1 ]-----+------------------------------------------------------------------------
subscription_name | sub_node2_to_node1
status            | replicating
provider_node     | node1
provider_dsn      | host=172.31.6.116 port=5432 dbname=mydb user=postgres password=password
slot_name         | pgl_mydb_node1_sub_node3f3d779
replication_sets  | {my_replication_set}
forward_origins   | {all}

Advantages of pglogical

pglogical provides several advantages over traditional replication approaches:

Fine-grained, table-level replication
Cross-version PostgreSQL support
Minimal-downtime migrations
Flexible and advanced replication topologies
Support for bidirectional and active-active replication architectures
Logical-level data portability

Limitations and Considerations

Despite its flexibility, pglogical introduces additional complexity:

More operational overhead than physical replication
Replication slots must be monitored to avoid WAL bloat
Conflict handling is required in multi-master setups
High write workloads may require careful tuning

Understanding these trade-offs is critical for successful adoption.

Conclusion:

pglogical significantly extends PostgreSQL’s replication capabilities by enabling flexible, logical, and purpose-driven data replication. While it introduces more operational complexity than physical replication, it remains a mature and production-proven solution for scenarios where native replication falls short.

For PostgreSQL users working on migrations, selective data sharing, or distributed architectures, pglogical continues to be a powerful and reliable choice.