I have two docker containers with PostgreSQL. I want to replicate data from existing tables in a database called wiki. I have tried using pglogical and the native logical replication feature. However, when I try to insert data into a table in the second container, a message appears saying that there is a duplicate key.
Below are the configurations of my docker environment using native logical replication. I create two containers. The first one has a table called persons with three records and the second one has the same table without any records.
Environment:
Dockerfile - For both containers:
# Use the official PostgreSQL image as base
FROM postgres:16-bookworm
# Package installation
RUN apt-get update && apt-get -y install postgresql-16-pglogical iproute2 iputils-ping \
&& apt-get clean \
&& apt-get autoremove \
&& rm -rf /var/lib/apt/lists/*
# Copy the postgresql.conf to the appropriate directory
COPY postgresql.conf /etc/postgresql/postgresql.conf
# Copy the init-db.sh script to the initialization directory
COPY init-db.sh /docker-entrypoint-initdb.d/init-db.sh
# Override default Postgres config file
CMD ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]
USER postgres
postgresql.conf - For both containers:
listen_addresses = '*'
port = 5432
max_wal_senders = 10
wal_level = logical
max_replication_slots = 10
shared_preload_libraries = 'pgoutput'
init-db.sh - Container cont-db1-01:
#!/bin/bash
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
-- Create table
CREATE TABLE IF NOT EXISTS persons (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT
);
-- Insert test data
INSERT INTO persons (name, age) VALUES ('Alice', 30);
INSERT INTO persons (name, age) VALUES ('Bob', 25);
INSERT INTO persons (name, age) VALUES ('Charlie', 35);
EOSQL
init-db.sh - Container cont-db2-01:
#!/bin/bash
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
-- Create table
CREATE TABLE IF NOT EXISTS persons (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT
);
EOSQL
docker-compose.yml:
version: '3.1'
services:
db1:
ports:
- 5432:5432
container_name: cont-db1-01
hostname: cont-db1-01
build: ./db1
image: db1:1.0
restart: always
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: wiki
db2:
ports:
- 5433:5432
container_name: cont-db2-01
hostname: cont-db2-01
build: ./db2
image: db2:1.0
restart: always
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: wiki
Replication Configuration:
docker exec -it cont-db1-01 psql -U postgres -d wiki -c "
CREATE PUBLICATION pub_for_node1 FOR ALL TABLES;
"
docker exec -it cont-db2-01 psql -U postgres -d wiki -c "
CREATE PUBLICATION pub_for_node2 FOR ALL TABLES;
"
docker exec -it cont-db1-01 psql -U postgres -d wiki -c "
CREATE SUBSCRIPTION sub_to_node2 CONNECTION 'host=cont-db2-01 port=5432 dbname=wiki user=postgres password=postgres' PUBLICATION pub_for_node2;
"
docker exec -it cont-db2-01 psql -U postgres -d wiki -c "
CREATE SUBSCRIPTION sub_to_node1 CONNECTION 'host=cont-db1-01 port=5432 dbname=wiki user=postgres password=postgres' PUBLICATION pub_for_node1;
"
Tests:
docker exec -it cont-db1-01 psql -U postgres -d wiki -c "SELECT * FROM persons;"
id | name | age
----+---------+-----
1 | Alice | 30
2 | Bob | 25
3 | Charlie | 35
(3 rows)
docker exec -it cont-db2-01 psql -U postgres -d wiki -c "SELECT * FROM persons;"
id | name | age
----+---------+-----
1 | Alice | 30
2 | Bob | 25
3 | Charlie | 35
(3 rows)
The above two outputs show the data inserted into the persons table in the first container and its replication in the second container.
docker exec -it cont-db1-01 psql -U postgres -d wiki -c "INSERT INTO persons (name, age) VALUES ('Maria', 40);"
INSERT 0 1
docker exec -it cont-db1-01 psql -U postgres -d wiki -c "SELECT * FROM persons;"
id | name | age
----+---------+-----
1 | Alice | 30
2 | Bob | 25
3 | Charlie | 35
4 | Maria | 40
(4 rows)
The insertion of a new record into the persons table of the first container was successful.
docker exec -it cont-db2-01 psql -U postgres -d wiki -c "INSERT INTO persons (name, age) VALUES ('Jose', 10);
"ERROR: duplicate key value violates unique constraint "persons_pkey"
DETAIL: Key (id)=(1) already exists.
The output above shows that when I try to insert a record into the people table in the second container, it says there is a duplicate key. I want the replication to be truly bidirectional. I have searched extensively on the internet and have not been able to resolve this issue.
I would like to use database replication in the Wiki.js system. There would be two Wiki applications, each accessing a database. I would use the second application in case the first one fails.
