Logical Replication in PostgreSQL Using pglogical (Docker Setup)

This guide demonstrates how to set up logical replication using the open source PostgreSQL extension pglogical, including a working example with two Docker containers. We’ll go through installation, configuration, slot creation, replication flow, and cleanup.

---

🔍 What is pglogical?

pglogical is a PostgreSQL extension that enables logical replication — replicating changes (INSERT, UPDATE, DELETE) between PostgreSQL databases using SQL-level operations instead of binary-level replication.

🧠 How It Works:

• pglogical runs inside PostgreSQL as an extension

• It uses logical decoding of WAL (Write-Ahead Log) to extract changes

• A provider node (source) sends changes

• A subscriber node (target) receives and applies them

• It supports row-level replication, cross-version replication, and filtering by table or schema

---

📘 Understanding WAL (Write-Ahead Logging)

PostgreSQL uses WAL (Write-Ahead Logging) to ensure durability and crash recovery. Every change to the database — inserts, updates, deletes — is first written to a WAL file before being applied to the data files.

But why do we do this in the first place?

Writing to a WAL file first, instead of immediately applying changes to the actual data blocks (where tables and indexes live), provides major performance and safety benefits. When a transaction is committed, PostgreSQL simply appends the change to a sequential WAL file and performs one fsync operation — which is far cheaper than flushing and syncing random data pages across the storage system.

This means:

• Fewer disk writes = better performance

• Faster commits, especially under many small transactions

• Only one fsync is needed for many concurrent transactions

Later, a background process will take those WAL changes and flush them into the actual table and index files. This deferred writing strategy helps PostgreSQL scale with high write throughput while maintaining durability.

Additionally, by storing changes in WAL files, PostgreSQL unlocks point-in-time recovery (PITR). By archiving WAL segments, you can restore a database snapshot and then replay WAL up to a specific point, giving you precise restoration in case of disaster or mistakes.

So WAL isn’t just a write-ahead log — it’s a performance accelerator, a recovery system, and a foundation for replication and backups.

PostgreSQL uses WAL (Write-Ahead Logging) to ensure durability and crash recovery. Every change to the database — inserts, updates, deletes — is first written to a WAL file before being applied to the data files.

🧱 Cluster-Wide Scope

WAL is cluster-wide, meaning it logs changes from all databases in a PostgreSQL instance (not just one specific database). It lives in the pg_wal/ directory and grows continuously with write activity.

🔧 WAL Levels

PostgreSQL has different wal_level settings:

To use pglogical, Debezium, or AWS DMS, you must set:

wal_level = logical

This enables PostgreSQL to emit logical changes (SQL-level) into the WAL for decoding by replication tools.

🔁 WAL and Replication

When logical replication is enabled:

• PostgreSQL creates a replication slot that bookmarks where replication started

• WAL changes are retained on disk until the slot has consumed them

• Tools like pglogical read from this slot to replicate changes

---

⚙️ Impact on Database Performance

Enabling logical replication does come with trade-offs:

• Setting wal_level = logical increases WAL size slightly due to extra metadata

• Adding replication slots can delay WAL cleanup if they’re inactive

• Active replication tasks introduce some CPU and I/O load

⚠️ Why Cleaning Up Replication Slots Matters

Since WAL is cluster-wide, if a replication slot is inactive or not consumed, PostgreSQL cannot delete WAL files needed by that slot — even if they’re from a different database. This leads to WAL bloat and can fill up disk space.

Always monitor and remove unused slots:

SELECT pg_drop_replication_slot('your_slot_name');

pglogical is efficient and lightweight for most workloads, especially when compared to trigger-based replication systems.

---

🧱 Docker-Based Setup for pglogical Replication

🔸 Step 1: Create a Docker Network (optional but recommended)

docker network create pg-net

🔸 Step 2: Run Two PostgreSQL Containers

Provider Node:

docker run -d \
  --name pg-master \
  --network pg-net \
  -e POSTGRES_USER=pguser \
  -e POSTGRES_PASSWORD=pgpass \
  -e POSTGRES_DB=pgdb \
  -p 5433:5432 \
  postgres:17

Subscriber Node:

docker run -d \
  --name pg-replica \
  --network pg-net \
  -e POSTGRES_USER=pguser \
  -e POSTGRES_PASSWORD=pgpass \
  -e POSTGRES_DB=pgdb \
  -p 5434:5432 \
  postgres:17

---

🔸 Step 3: Install pglogical Extension in Both Containers

docker exec -it pg-master bash
apt-get update && apt-get install -y postgresql-17-pglogical

Do the same inside pg-replica.

---

🔸 Step 4: Enable Logical Replication

Edit /var/lib/postgresql/data/postgresql.conf in both containers:

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

Then restart both:

docker restart pg-master
docker restart pg-replica

---

🔧 Configure pglogical

On pg-master:

CREATE EXTENSION pglogical;
SELECT pglogical.create_node(
  node_name := 'provider_node',
  dsn := 'host=pg-master port=5432 dbname=pgdb user=pguser password=pgpass'
);
CREATE TABLE users (id serial PRIMARY KEY, name text);
INSERT INTO users(name) VALUES ('Mostafa'), ('John Doe');
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

On pg-replica:

CREATE EXTENSION pglogical;
SELECT pglogical.create_node(
  node_name := 'subscriber_node',
  dsn := 'host=pg-replica port=5432 dbname=pgdb user=pguser password=pgpass'
);
SELECT pglogical.create_subscription(
  subscription_name := 'sub_to_master',
  provider_dsn := 'host=pg-master port=5432 dbname=pgdb user=pguser password=pgpass',
  synchronize_structure := true
);

---

✅ Test the Replication

On pg-master:

INSERT INTO users(name) VALUES ('Tariq'), ('Layla'), ('Nour');

On pg-replica:

SELECT * FROM users;

You should see all the rows!

Try updates and deletes too — they’ll sync as well:

UPDATE users SET name = 'Mostafa Nasr' WHERE name = 'Mostafa';
DELETE FROM users WHERE name = 'John Doe';

---

🧹 Clean Up

On pg-replica:

SELECT pglogical.drop_subscription('sub_to_master');
SELECT pglogical.drop_node('subscriber_node');

On pg-master:

SELECT pglogical.drop_node('provider_node');

Verify that replication slots are gone:

SELECT * FROM pg_replication_slots;

---

🔚 Conclusion

pglogical is a powerful, production-grade solution for PostgreSQL logical replication. It’s fast, SQL-based, and doesn’t require external tooling. While it introduces minimal performance overhead, it's an excellent fit for real-time data sync, zero-downtime migrations, or selective table replication between Postgres databases.

pglogical works natively inside PostgreSQL and doesn't require a separate service to handle replication. However, it does require the database engine to load the plugin, which means you’ll need to restart the database after enabling pglogical in shared_preload_libraries.

AWS DMS uses pglogical when handling CDC (change data capture). The replication instance will create the nodes and the subscribers, and then delete them when done. It will handle:

• Creating the replication slot

• Streaming changes

• Stopping the replication task when done

⚠️ Just make sure to delete the DMS task when you're done. This ensures the replication slot is dropped — otherwise, PostgreSQL will keep WAL files around for that inactive slot, leading to disk bloat and blocked WAL recycling.

This guide walked you through everything — from installing the extension, configuring replication, understanding how WAL and journaling work, testing inserts and updates, to cleaning it all up. 🎉

🔗 References

• PostgreSQL Logical Replication

• pglogical GitHub

• AWS DMS PostgreSQL Source Docs

• PostgreSQL WAL Documentation