🧱 Architecture
| Node | Container | Host Port | Data Path |
|---|---|---|---|
| node1 | postgres-node1 | 5432 | /app/pg1 |
| node2 | postgres-node2 | 5433 | /app/pg2 |
Logical active-active:
node1 <----> node2
1️⃣ Install Docker (Official – Mandatory)
Remove old versions:
sudo apt remove docker docker-engine docker.io containerd runc -y
Install official Docker:
curl -fsSL https://get.docker.com | sudo sh
Add your user:
sudo usermod -aG docker $USER
newgrp docker
Validate:
docker version
docker compose version
You must use:
docker compose
2️⃣ Prepare Persistent Storage
sudo mkdir -p /app/pg1
sudo mkdir -p /app/pg2
sudo chown -R 999:999 /app
Why 999?
Inside the container, postgres runs as UID 999.
3️⃣ Build Custom PostgreSQL Image with pglogical
Create working directory:
mkdir ~/pglogical-demo
cd ~/pglogical-demo
Create Dockerfile
nano Dockerfile
Paste:
FROM postgres:15LABEL description="PostgreSQL 15 with pglogical"ENV DEBIAN_FRONTEND=noninteractiveRUN apt-get update && \
apt-get install -y --no-install-recommends \
postgresql-15-pglogical \
&& apt-get clean \
&& rm -rf /var/lib/apt/lists/*CMD ["postgres"]
Why this matters
- Version must match (15 ↔ 15)
- Installs pglogical binaries
- Keeps image small
- Works on ARM (Raspberry Pi 64-bit)
Build image
docker build -t postgres-pglogical:15 .
Verify extension exists:
docker run --rm postgres-pglogical:15 \
ls /usr/lib/postgresql/15/lib | grep pglogical
You must see:
pglogical.so
4️⃣ Create docker-compose.yml
nano docker-compose.yml
Paste:
version: '3.8'services:
node1:
image: postgres-pglogical:15
container_name: postgres-node1
environment:
POSTGRES_USER: repl
POSTGRES_PASSWORD: replpass
POSTGRES_DB: demo
ports:
- "5432:5432"
volumes:
- /app/pg1:/var/lib/postgresql/data
command: >
postgres
-c wal_level=logical
-c max_wal_senders=10
-c max_replication_slots=10
-c shared_preload_libraries=pglogical
networks:
- pgnet node2:
image: postgres-pglogical:15
container_name: postgres-node2
environment:
POSTGRES_USER: repl
POSTGRES_PASSWORD: replpass
POSTGRES_DB: demo
ports:
- "5433:5432"
volumes:
- /app/pg2:/var/lib/postgresql/data
command: >
postgres
-c wal_level=logical
-c max_wal_senders=10
-c max_replication_slots=10
-c shared_preload_libraries=pglogical
networks:
- pgnetnetworks:
pgnet:
driver: bridge
5️⃣ Start Environment
docker compose up -d
Verify:
docker ps
6️⃣ Enable pglogical Extension
On both nodes:
docker exec -it postgres-node1 psql -U repl -d demo
CREATE EXTENSION pglogical;
Repeat on node2.
7️⃣ Configure Logical Nodes
Node1
SELECT pglogical.create_node(
node_name := 'node1',
dsn := 'host=node1 port=5432 dbname=demo user=repl password=replpass'
);
Node2
SELECT pglogical.create_node(
node_name := 'node2',
dsn := 'host=node2 port=5432 dbname=demo user=repl password=replpass'
);
8️⃣ Create Subscriptions (Bidirectional)
Node1:
SELECT pglogical.create_subscription(
subscription_name := 'sub_node2',
provider_dsn := 'host=node2 port=5432 dbname=demo user=repl password=replpass'
);
Node2:
SELECT pglogical.create_subscription(
subscription_name := 'sub_node1',
provider_dsn := 'host=node1 port=5432 dbname=demo user=repl password=replpass'
);
9️⃣ Create Replicated Table
On node1:
CREATE TABLE test_sync (
id SERIAL PRIMARY KEY,
name TEXT,
created_at TIMESTAMP DEFAULT now()
);SELECT pglogical.replication_set_add_table(
set_name := 'default',
relation := 'test_sync'
);
Repeat replication_set_add_table on node2.
🔟 Prevent Primary Key Conflicts
Node1:
ALTER SEQUENCE test_sync_id_seq RESTART WITH 1 INCREMENT BY 2;
Node2:
ALTER SEQUENCE test_sync_id_seq RESTART WITH 2 INCREMENT BY 2;
Now:
- node1 generates odd IDs
- node2 generates even IDs
🔍 Replication Validation Commands
Check subscription status:
SELECT * FROM pglogical.show_subscription_status();
Check replication slots:
SELECT slot_name, active FROM pg_replication_slots;
Check streaming state:
SELECT client_addr, state FROM pg_stat_replication;
If:
- status = replicating
- active = true
- state = streaming
Replication is healthy.
✅ Prove Replication (Insert Test)
Insert on node1:
INSERT INTO test_sync (name) VALUES ('insert_from_node1');
Check on node2:
SELECT * FROM test_sync;
Insert on node2:
INSERT INTO test_sync (name) VALUES ('insert_from_node2');
Check on node1:
SELECT * FROM test_sync;
If both rows exist on both nodes → confirmed.
🔁 Full Environment Rebuild
Stop containers:
docker compose down
Delete data:
sudo rm -rf /app/pg1/*
sudo rm -rf /app/pg2/*
Start fresh:
docker compose up -d
Reconfigure pglogical.
🧨 Disaster Scenario – Delete Node2 and Restore from Node1
Stop node2
docker stop postgres-node2
Delete node2 data
sudo rm -rf /app/pg2/*
Backup node1
docker exec postgres-node1 pg_dump -U repl demo > backup.sql
Start node2
docker start postgres-node2
Restore
cat backup.sql | docker exec -i postgres-node2 psql -U repl -d demo
Recreate subscription if needed
SELECT pglogical.create_subscription(
subscription_name := 'sub_node1',
provider_dsn := 'host=node1 port=5432 dbname=demo user=repl password=replpass'
);
Validate again.
🧹 Cleanup: Remove Containers, Images and Network
After testing replication between node1 and node2, you may want to completely remove all resources created during this lab.
We’ll remove:
- Containers
- Volumes (if used)
- Custom Docker network
- PostgreSQL images
Stop and Remove Containers
If you created containers manually:
docker rm -f postgres-node1 postgres-node2
Verify:
docker ps -a
They should no longer appear.
Remove Custom Docker Network
Since you created:
networks:
pglogical-demo_pgnet:
driver: bridge
Remove it:
docker network rm pglogical-demo_pgnet
Verify:
docker network ls
Remove PostgreSQL Images
If you pulled the official image from Docker Hub (for example postgres:15), remove it:
docker images
docker rmi postgres-pglogical:15
🔎 Verify Everything Is Clean
Run:
docker ps -a
docker volume ls
docker network ls
docker images
You should see no leftover lab resources.