Prepare DOcker Containers

First we create two containers: one which for the replication Publisher and the other for replication Subscrber

# Create a dedicated network for PostgreSQL
docker network create pgnet

# Publisher
docker run -d --name pgpub \
  -e POSTGRES_HOST_AUTH_METHOD=trust \
  -e POSTGRES_PASSWORD=postgres \
  -e PGDATA=/var/lib/postgresql/data/pgdata \
  -v pg_data_pgpub:/var/lib/postgresql/data \
  --network=pgnet \
  -p 54325:5432 postgres 

# Subscriber
docker run -d --name pgsub \
  -e POSTGRES_HOST_AUTH_METHOD=trust \
  -e POSTGRES_PASSWORD=postgres \
  -e PGDATA=/var/lib/postgresql/data/pgdata \
  -v pg_data_pgsub:/var/lib/postgresql/data \
  --network=pgnet \
  -p 54326:5432 postgres 

NOTE: we are using values suitable for testing purposes (eg. trust auth method and default password for postgres user)

Logical Replication Setup

In order for the replication to work we must first set the wal_level to logical on the publisher and then restart it.

docker exec -it pgpub sed 's/#wal_level = replica/wal_level = logical/g' /var/lib/postgresql/data/pgdata/postgresql.conf -i

docker restart pgpub

On the publisher we must create a role which will be used by the subscriber.

create role replicauser REPLICATION LOGIN  password 'password';

Create test environment

We create a test database, schema and a table which will be replicated from publisher to subscriber.

create database mydb;

-- connect to the new database
create schema test;

-- we must add read privilege to replicauser (use entire schema)
grant usage on schema test to replicauser;
grant select on all tables in schema test to replicauser;
-- set default privileges as well (for future tables we will create)
alter default privileges grant select on tables to replicauser;

-- create table
CREATE TABLE test.table_1 (
	id int4 NOT NULL,
	somedata varchar NULL,
	CONSTRAINT table_1_pkey PRIMARY KEY (id)
);

-- insert some test data
insert into test.table_1 
values (generate_series(1,10),'data'||generate_series(1,10));

create database mydb;

-- connect to the new database
create schema test;

-- create table
CREATE TABLE test.table_1 (
	id int4 NOT NULL,
	somedata varchar NULL,
	CONSTRAINT table_1_pkey PRIMARY KEY (id)
);

Create a publication and SUBSCRIPTION

We first create a publication which will publish changes of data to the subscriber.

CREATE PUBLICATION mypub FOR table test.table_1;

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=mydb host=pgpub user=replicauser password=password port=5432' PUBLICATION mypub;

After that we can see in the logs on the subscriber that the subscription process has started and that the table table_1 has been synchronized from publisher:

LOG:  logical replication apply worker for subscription "mysub" has started
LOG:  logical replication table synchronization worker for subscription "mysub", table "table_1" has started
LOG:  logical replication table synchronization worker for subscription "mysub", table "table_1" has finished

Adding a new table

To add a new table first we have to execute the DDL on both subscriber and publisher. Then we add it to the publication and then refresh the subscription.

CREATE TABLE test.table_2 (
	id int4 NOT NULL,
	somedata varchar NULL,
	CONSTRAINT table_2_pkey PRIMARY KEY (id)
);

alter publication mypub add table test.table_2;

alter subscription mysub refresh publication;

Then we can again inspect log files which will tell that new table has been added.

Refreshing an entire table

If for some reasons the data in table on the subscriber gets damaged or corrupted we can restart the initial copy with the following steps:

Step 1:

-- we remove the table from the publisher
alter publication mypub drop table test.table_2;

Step 2:

-- notify the subscriber that the table has been removed from the publication
alter subscription mysub refresh publication;

-- truncate the table so the initial refresh can be made
truncate table test.table_2;

Step 3:

-- we add the table again
alter publication mypub add table test.table_2;

Step 4:

-- notify the subscriber that the table has been added
alter subscription mysub refresh publication;

-- inspect logs for synchronization