PostGres Database Replication Using Pglogical

Source: Dev.to

Prerequisites

• Docker Engine

  sudo systemctl start docker
  sudo systemctl enable docker   # optional – start at boot

• Docker Desktop on Debian (installed from a .deb package)

  systemctl --user start docker-desktop

Project structure

//docker/
│
├── Dockerfile.pglogical
└── postgres/
    └── docker-compose.yml

docker-compose.yml

Create the file docker-compose.yml inside /docker/postgres/:

services:
  pg1:
    build:
      context: ..
      dockerfile: Dockerfile.pglogical
    container_name: pg1
    command:
      - postgres
      - -c
      - shared_preload_libraries=pglogical
      - -c
      - wal_level=logical
      - -c
      - max_replication_slots=10
      - -c
      - max_wal_senders=10
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: testdb
      POSTGRES_HOST_AUTH_METHOD: trust
    ports:
      - "5433:5432"
    volumes:
      - pg1_data:/var/lib/postgresql/data
    networks:
      - pgnet

  pg2:
    build:
      context: ..
      dockerfile: DockerFile.pglogical
    container_name: pg2
    command:
      - postgres
      - -c
      - shared_preload_libraries=pglogical
      - -c
      - wal_level=logical
      - -c
      - max_replication_slots=10
      - -c
      - max_wal_senders=10
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: testdb
      POSTGRES_HOST_AUTH_METHOD: trust
    ports:
      - "5434:5432"
    volumes:
      - pg2_data:/var/lib/postgresql/data
    networks:
      - pgnet

networks:
  pgnet:
    driver: bridge

volumes:
  pg1_data:
  pg2_data:

Dockerfile.pglogical

Because the base PostgreSQL image does not contain the pglogical extension, create a custom Dockerfile:

FROM postgres:16

RUN apt-get update \
 && apt-get install -y postgresql-18-pglogical \
 && rm -rf /var/lib/apt/lists/*

Build and start the containers

cd /docker/postgres
docker compose up -d

Verify that the containers are running:

docker ps

You should see something similar to:

CONTAINER ID   IMAGE               COMMAND                  CREATED        STATUS        PORTS                                            NAMES
c61ef5d393cc   postgres-pg1       "docker-entrypoint.s…"   3 days ago     Up 4 minutes  0.0.0.0:5433->5432/tcp, [::]:5433->5432/tcp      pg1
b055528784d5   postgres-pg2       "docker-entrypoint.s…"   3 days ago     Up 4 minutes  0.0.0.0:5434->5432/tcp, [::]:5434->5432/tcp      pg2

Install and enable pglogical inside each container

docker exec -it pg1 psql -U postgres -d testdb

Inside psql:

CREATE EXTENSION pglogical;
\dx   -- verify the extension is installed

You should see an entry similar to:

 List of installed extensions
  Name    | Version |   Schema   | Description
----------+---------+------------+--------------------------------
 pglogical| 2.4.6   | pglogical  | PostgreSQL Logical Replication
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language

Repeat the same steps for the pg2 container.

Register the databases as pglogical nodes

Run the following in pg1:

SELECT pglogical.create_node(
    node_name := 'pg1',
    dsn := 'host=pg1 port=5432 dbname=testdb user=postgres password=postgres'
);

Run the following in pg2:

SELECT pglogical.create_node(
    node_name := 'pg2',
    dsn := 'host=pg2 port=5432 dbname=testdb user=postgres password=postgres'
);

Create a replication set on pg1

SELECT pglogical.create_replication_set(
    'demo_set',
    replicate_insert := true,
    replicate_update := true,
    replicate_delete := true,
    replicate_truncate := true
);

Create a table and insert baseline rows (pg1)

CREATE TABLE demo_events (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT now()
);

INSERT INTO demo_events (message)
SELECT 'baseline row ' || g
FROM generate_series(1,5) g;

Add the table to the replication set (pg1)

SELECT pglogical.replication_set_add_table(
    'demo_set',
    'demo_events'
);

Create a subscription on pg2

SELECT pglogical.create_subscription(
    subscription_name := 'sub_pg1',
    provider_dsn := 'host=pg1 port=5432 dbname=testdb user=postgres password=postgres',
    replication_sets := ARRAY['demo_set'],
    synchronize_structure := true,
    synchronize_data := true
);

Wait a few seconds, then verify that the data has been replicated:

SELECT * FROM demo_events;

You should see the five baseline rows that were inserted on pg1.

Test write direction (pg1 → pg2)

Insert a new row on the primary (pg1):

INSERT INTO demo_events (message)
VALUES ('inserted new row');

The row will appear on pg2 automatically. Attempting to write directly to pg2 will raise an error, confirming that pg2 is a read‑only replica.

Conclusion

You now have two PostgreSQL containers replicating data using the pglogical extension. Changes made on the primary (pg1) are automatically propagated to the replica (pg2), providing logical, near‑real‑time replication without the need for physical streaming. Enjoy your synchronized databases! 🎉