Postgresql 13 Streaming Replication using Docker

In this setup, we will create a 2 nodes postgresql streaming replication, in docker. This article will make use of postgresql image version 13.

1. Create a network, and take note of the network ip range

$ docker network create mynet

$ docker network inspect mynet 

2. Make 1 directory for pgmaster. 

$ sudo mkdir pgmasterdata

3.  Create a container called pgmaster

$ docker run -dit -v "$PWD/pgmasterdata/:/var/lib/postgresql/data -e POSTGRES_PASSWORD=abc -p 5432:5432 --restart=unless-stopped --network=mynet --name=pgmaster postgres 

4. Backup and edit pgmaster's postgresql.conf with below settings

$ sudo cp pgmasterdata/postgresql.conf pgmasterdata/postgresql.conf.ori

$ cat > postgresql.conf <<EOF

listen_addresses = '*'

port = 5432

max_connections = 50

ssl = off

shared_buffers = 32MB

# Replication Settings - Master

wal_level = hot_standby

max_wal_senders = 3

EOF

$ sudo cp postgresql.conf pgmasterdata/postgresql.conf 

5. Login to pgmaster and create a user for replication

$ docker exec -it pgmaster psql -U postgres -h localhost -d postgres

postgres=# create role replicator with replication password 'abc';

postgres=# \q

 

6. Backup and edit pgmaster's pg_hba.conf with ip range from step 1

$ sudo cp pgmasterdata/pg_hba.conf pgmasterdata/pg_hba.conf.ori

$ echo "host    replication  all  172.16.0.0/16  trust" | sudo tee -a pgmasterdata/pg_hba.conf 

7. Restart pgmaster container

$ docker restart pgmaster

8.  Run backup of master to /slavedata in pgmaster

$ docker exec -it pgmaster bash

# mkdir /pgslavedata 

# pg_basebackup -h pgmaster -D /pgslavedata -U replicator -v -P --wal-method=stream

9. Copy /slavedata in pgmaster to host

$ docker cp pgmaster:/pgslavedata pgslavedata

10. Tell pgslave that it is a slave

$ sudo touch  pgslavedata/standby.signal

11. Edit postgresql.conf in pgslavedata

$ sudo cp pgslavedata/postgresql.conf pgslavedata/postgresql.conf.ori

$ cat > postgresql.conf <<EOF

listen_addresses = '*'

port = 5432

max_connections = 50

ssl = off

shared_buffers = 32MB

# Replication Settings - Slave

hot_standby = on

primary_conninfo = 'host=<master ip> port=5432 user=replicator password=abc@123'

EOF

$ sudo cp postgresql.conf pgslavedata/postgresql.conf

12. Start pgslave

$ docker run -dit -v "$PWD"/pgslavedata/:/var/lib/postgresql/data -e POSTGRES_PASSWORD=abc -p 15432:5432 --network=mynet --restart=unless-stopped--name=pgslave postgres

13. Check replication state in pgmaster

$ docker exec -it pgmaster psql -h localhost -U postgres -d postgres -c "select usename,state from pg_stat_activity where usename = 'replicator';"

usename   | state  

------------+--------

 replicator | active 

14. Verify the setup by creating a database in pgmaster, and check if the same database appear in pgslave. 

15. To promote to pgslave if pgmaster is down, simply run "pg_ctl promote" command

$ docker exec -it pgslave bash

# pg_ctl promote