Monday, March 8, 2021

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 3 directories for pgmaster, pgdata and pgbootstrap. The bootstrap directory will be used to setup pgslave the first time.

$ sudo mkdir -p /dockervol/{pgmaster,pgslave,pgbootstrap}
3.  Create a container called pgmaster
$ docker run -dit -v /dockervol/pgmaster/:/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 /dockervol/pgmaster/postgresql.conf 
/dockervol/pgmaster/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 /dockervol/pgmaster/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'
6. Backup and edit pgmaster's pg_hba.conf with ip range from step 1
$ sudo cp /dockervol/pgmaster/pg_hba.conf 
/dockervol/pgmaster/pg_hba.conf.ori
$ echo "host    replication  all  172.16.0.0/16  trust" | sudo tee -a /dockervol/pgmaster/pg_hba.conf 
7. Restart pgmaster container
$ docker restart pgmaster
8. Run bootstrap container
$ docker run -dit --rm -v /dockervol/pgbootstrap:/var/lib/postgresql/data -v /dockervol/pgslave:/var/lib/postgresql/slave-data -e POSTGRES_PASSWORD=abc --network=mynet --name=pgbootstrap postgres

9.  Run backup of master to /var/lib/postgresql/slave-data in pgbootstrap

$ docker exec -it pgbootstrap su - postgres

$ pg_basebackup -h pgmaster -D /var/lib/postgresql/slave-data -U replicator -v -P --wal-method=stream

10. Stop pgbootstrap

$ docker stop pgbootstrap

11. Tell pgslave that it is a slave

$ sudo touch /dockervol/pgslave/standby.signal

12. Edit postgresql.conf in /dockervol/pgslave

$ sudo cp /dockervol/pgdata/postgresql.conf /dockervol/pgdata/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 /dockervol/pgdata/postgresql.conf

13. Start pgslave

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

14. 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 

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

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

$ docker exec -it pgslave bash

# su - postgres

# pg_ctl promote

No comments: