PostGres Database Replication Using Pglogical

Originally published on Hashnode

This is an example of PostgreSQL database replication using the pglogical extension. In this example, I used Docker containers, with each container having its own database. When data is written to the primary database, the secondary container receives the same data.

👨‍💻A man proudly told his wife, “I’m an SQL DB admin!”

She threw him out of the house…

Because he had one to many relationships💔 — SQL News Network

Start Docker

If you have docker engine use

sudo systemctl start docker
sudo systemctl enable docker // if you want start at boot

Or if you have docker desktop use this in debian Linux (installed from .deb package)

systemctl --user start docker-desktop

Create configuration files

You need to create 2 config files as shown in this folder tree

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

Define config files

docker-compose.yml is the file that defines how to run the containers.

cd <RATH_TO_PROJECT>/docker/postgres/ 
nano docker-compose.yml

paste this content for your yml

container names are inside the services part. For these example containers are pg1 and pg2

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:

docker-compose.yml has a limitation: It doesn’t let you install software inside the system images!

Since pglogical is not included in the images we need to create Dockerfile.pglogical to specify that we want to add pglogical to the image.

FROM postgres:16

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

Start the containers

Use this command to start the containers

cd <ROUTE_TO_DOCKER_YML>
docker compose up -d

Check that containers are running

docker ps

you will get something like this

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

• Where each line is a container you have running

Get pglogical running in each container

• Follow this instructions for pg1 and pg2 the examples shown are also for pg1

Install pglogical in each container using the following command

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

Enable the extension in each container

CREATE EXTENSION pglogical;

The use this sql command to check installed extensions in each container

testdb=# \dx

example output when pglogical is installed

                   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
(2 rows)

Register database as a pglogical node named pg1 and pg2

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

Create table in the provider database

On pg1

Connect postgres

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

Create demo table & baseline data

On 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:

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

Create subscriptor

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:

SELECT * FROM demo_events;

You should see the 5 baseline rows on pg2.

Finally if you try to insert data in the table from pg1 (primary db) it will be successful but if you try from pg2 (replica db) you will get a error-only error. which shows that replication is working as expected.

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

Enjoy! 🎉 You now have your replicated databases working. Feel the power of synchronized data with no more differing versions! 🔄💪

---