HOW TO SETUP POSTGRES DB WITH REPLICA IN DOCKER

how_to_postgres_replica_docker.md

1.- Make 1 directory for pgmaster.

sudo mkdir pgmasterdata

2.- Create a new docker container called pgmaster

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

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

4.- 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 LOGIN PASSWORD 'abc';
	  
postgres=#
	  \q

5.- Backup and edit pgmaster's pg_hba.conf with ip range from selected docker network in step 2

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

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

6.- Restart pgmaster container

docker restart pgmaster

7.- Run backup of master into /slavedata in the same pgmaster container

docker exec -it pgmaster bash
mkdir /pgslavedata
pg_basebackup -h pgmaster -D /pgslavedata -U replicator -v -P --wal-method=stream
exit

8.- Copy /slavedata that is in pgmaster to host

docker cp pgmaster:/pgslavedata pgslavedata

9.- Tell to pgslave data in host that it is a slave

sudo touch  pgslavedata/standby.signal

10.- Edit postgresql.conf in the pgslave data in host

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=172.25.0.128 port=5432 user=replicator password=abc'
EOF

sudo cp postgresql.conf pgslavedata/postgresql.conf

11.- Start new pgslave container

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

12.- TO 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';"

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

docker exec -it pgmaster psql -U postgres -h localhost -d postgres
CREATE DATABASE testdb;
\l
exit
docker exec -it pgslave psql -U postgres -h localhost -d postgres
\l

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

docker exec -it pgslave bash

pg_ctl promote