PostGres Database Replication Using Pglogical

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! 🔄💪

Bonus: Enable Bidirectional Replication

Subscribe to Pg1

Use this command

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

On pg1, we disable both options for the following reasons:

• synchronize_structure = false
  We do not synchronize the table structure because pg1 already has the table.
  The table was created locally on pg1, so there is no need to copy it from pg2.

• synchronize_data = false
  We do not synchronize existing data because pg1 already contains the original rows.
  Copying data again would cause duplicate primary keys and conflicts.

With both options disabled, pglogical will:

Only replicate new changes that happen after the subscription is created.

This means that:

• Any rows inserted later on pg2 will be replicated to pg1

• Existing rows that were already present on pg1 are left untouched

Set Id ranges for each master

In a pglogical multi-master setup, more than one database node can write data at the same time.
Because of this, each node must generate unique primary keys.

PostgreSQL sequences are local to each node, and pglogical does not coordinate them automatically.
If two nodes generate the same ID, replication will fail due to a primary-key conflict.

For this reason, ID generation must be handled manually. We solve this by assigning different ID ranges to each node.
For example:

• pg1 generates IDs starting from 1

• pg2 generates IDs starting from 1000

ALTER SEQUENCE demo_events_id_seq RESTART WITH 1000;

set pg2 table in replication set

SELECT pglogical.create_replication_set(
    set_name := 'demo_set'
);

SELECT pglogical.replication_set_add_table(
    set_name := 'demo_set',
    relation := 'demo_events',
    synchronize_data := false
);