Using PostgreSQL logical replication on Docker

1. Prereuities

OS: CentOS7
PostgreSQL: PostgreSQL13.0
Docker version 20.10

2. What I want to set up

Publisher: container postgres13-server1, database testlrep, replication user repuser, table t1
Subscriber: container postgres13-server2, database testlrep, table t1

3. Creating Docker containers

(1) Install Docker and Docker images of PostgreSQL13 according to e.g. [1]

(2) Creating Docker containers by the following commands

docker run -d --name postgres13-server1 -e POSTGRES_PASSWORD=postgres -p 15432:5432 -v /tmp/pgsql13/data1:/var/lib/postgresql/data postgres:13-alpine
docker run -d --name postgres13-server2 -e POSTGRES_PASSWORD=postgres -p 15432:5432 -v /tmp/pgsql13/data1:/var/lib/postgresql/data postgres:13-alpine

(3) Get IP address of containers

docker exec -it postgres13-server1 /bin/bash
bash-5.1# hostname -i
bash-5.1# exit

4. Creating databases and Setup

(1) connect to database on postgres13-server1

psql -U postgres -h 0.0.0.0 -p 15432

(2) create database on postgres13-server1 and setup

create database testlrep;
\c testlrep
create table t1(col1 integer primary key);
create table t2(col1 integer primary key);
create replication role repuser with password '<password>'
grant connect on testlrep to repuser;
grant select on t1 to repuser;
grant select on t2 to repuser;

(3) connect to database on postgres13-server2

psql -U postgres -h 0.0.0.0 -p 15433

(4) create database on postgres13-server2 and setup

create database testlrep;
\c testlrep
create table t1(col1 integer primary key);
create table t2(col1 integer primary key);

5. Setup logical replication

5.1 Setup in publisher

(1) Change wal_level setting to the following in /tmp/pgsql13/data1/postgresql.conf

wal_level = logical

(2) Add an authentication setting to the following in /tmp/pgsql13/data1/pg_hba.conf

host     all     repuser     0.0.0.0/0     md5

(3) create publication using psql

create publication mypub for table t1;

5.2 Setup in subscriber

create subscription using psql. <ip_address> is ip which you get in 3.(3).

create subscription mysub connection 'dbname=testlrep host=<ip_adress> user=repuser password=<password>' publication mypub;

5.3 Check whethe logical replication works or not

(1) Insert data to publisher

psql -U postgres -h 0.0.0.0 -p 15432 -c "insert into t1 values(1)"
psql -U postgres -h 0.0.0.0 -p 15432 -c "insert into t2 values(1)"

(2) Select data from subscriber. You should only t1 contains value “1”.

psql -U postgres -h 0.0.0.0 -p 15433 -c "table t1"
psql -U postgres -h 0.0.0.0 -p 15433 -c "table t2"

6. References

[1] Using PostgreSQL on Docker
https://osmanthus.work/?p=235

[2] PostgreSQL13 official document, Chapter 30. Logical Replication, 30.9. Quick Setup
https://www.postgresql.org/docs/13/logical-replication-quick-setup.html