PostgreSQL Docker image with repmgrd for automatic failover
This is heavily based on 2ndQuadrant's blog post on the topic), but:
- several environment variables have been added
- this image doesn't use passwordless replication. Make sure to set
REPMGR_PASSWORD
- I've taken the liberty to add my own pg_recall PostgreSQL extension, because I need it on all my production clusters.
It's a source-only extension, so unless you callCREATE EXTENSION recall
, it's entirely inactive. - a few other minor changes
Environment:
This docker image uses the following environment variables (with their defaults if applicable):
REPMGR_USER=repmgr
REPMGR_DB=repmgr
REPMGR_PASSWORD
(required)
Use something likepwgen -n 24 1
to generate a random one (and make sure you use the same one on all your nodesBARMAN_PASSWORD
(required)
Use something likepwgen -n 24 1
to generate a random one (and make sure you use the same one on all your nodesSTREAMING_PASSWORD
(required)
Use something likepwgen -n 24 1
to generate a random one (and make sure you use the same one on all your nodesPRIMARY_NODE=
If set, this is used in theconninfo
string (used by other nodes to connect to this one.
If empty,hostname -f
is used Make sure you use a hostname the others can resolve (or an IP address)WITNESS=
If non-empty, this node is set up as witness node (i.e. won't hold actual data but still has a vote in leader election).
docker build --tag postgres-repmgr .
docker build --tag postgres-pgbouncer pgbouncer
docker build --tag postgres-barman barman
export REPMGR_PASSWORD=head /dev/urandom | tr -dc A-Za-z0-9 | head -c 24 ; echo ''
export BARMAN_PASSWORD=head /dev/urandom | tr -dc A-Za-z0-9 | head -c 24 ; echo ''
export STREAMING_PASSWORD=head /dev/urandom | tr -dc A-Za-z0-9 | head -c 24 ; echo ''
docker network create pg_stream
docker run --name pg-repmgr-1 --hostname pg-repmgr-1 --network pg_stream -e REPMGR_PASSWORD=$REPMGR_PASSWORD -e BARMAN_PASSWORD=$BARMAN_PASSWORD -e STREAMING_PASSWORD=$STREAMING_PASSWORD -d postgres-repmgr
sleep 15
docker run --name pg-barman-1 --hostname pg-barman-1 --network pg_stream -e BARMAN_PASSWORD=$BARMAN_PASSWORD -e STREAMING_PASSWORD=$STREAMING_PASSWORD -e PRIMARY_NODE=pg-repmgr-1 -d postgres-barman
sleep 30
docker exec -it pg-barman-1 barman backup all
docker exec -it pg-barman-1 barman backup all
docker exec -it pg-barman-1 barman cron
docker exec -it pg-barman-1 barman check pg-repmgr-1
Server pg-repmgr-1: PostgreSQL: OK is_superuser: OK PostgreSQL streaming: OK wal_level: OK replication slot: OK directories: OK retention policy settings: OK backup maximum age: OK (interval provided: 7 days, latest backup age: 2 minutes, 21 seconds) compression settings: OK failed backups: OK (there are 0 failed backups) minimum redundancy requirements: OK (have 1 backups, expected at least 1) ssh: OK (PostgreSQL server) not in recovery: OK pg_receivexlog: OK pg_receivexlog compatible: OK receive-wal running: OK archiver errors: OK
docker run --name pg-repmgr-2 --hostname pg-repmgr-2 --network pg_stream -e REPMGR_PASSWORD=$REPMGR_PASSWORD -e BARMAN_PASSWORD=$BARMAN_PASSWORD -e STREAMING_PASSWORD=$STREAMING_PASSWORD -e PRIMARY_NODE=pg-repmgr-1 -d postgres-repmgr
sleep 20
docker run --name pg-repmgr-3 --hostname pg-repmgr-3 --network pg_stream -e REPMGR_PASSWORD=$REPMGR_PASSWORD -e BARMAN_PASSWORD=$BARMAN_PASSWORD -e STREAMING_PASSWORD=$STREAMING_PASSWORD -e PRIMARY_NODE=pg-repmgr-1 -d postgres-repmgr
sleep 8
docker exec -it pg-repmgr-2 su -c "repmgr cluster show" - postgres sleep 3
docker run --name pg-pgbouncer-1 --network pg_stream -e PRIMARY_NODE=pg-repmgr-1 -d postgres-pgbouncer
sleep 1
docker exec -it pg-pgbouncer-1 psql -U postgres -c "select client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn from pg_stat_replication;"
[ monitor from another shell ] docker logs -f pg-repmgr-2
docker pause pg-repmgr-1
sleep 120
docker exec -it pg-repmgr-2 su -c "repmgr cluster show" - postgres
docker exec -it pg-pgbouncer-1 psql -U postgres -c "select client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn from pg_stat_replication;"
docker exec -it pg-barman-1 barman check all
docker unpause pg-repmgr-1
sleep 100
docker exec -it -u postgres pg-repmgr-1 bash -c 'repmgr node service --action=stop --checkpoint'
docker exec -it -u postgres pg-repmgr-1 bash -c 'cp -f /etc/postgresql/postgresql.conf /var/lib/postgresql/data/postgresql.conf'
docker exec -it -u postgres pg-repmgr-1 bash -c 'repmgr -h pg-repmgr-2 -d repmgr node rejoin'
docker exec -it pg-pgbouncer-1 psql -U postgres -c "select client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn from pg_stat_replication;"
docker kill pg-repmgr-1
docker kill pg-repmgr-2
docker kill pg-repmgr-3
docker kill pg-pgbouncer-1
docker kill pg-barman-1
docker rm pg-repmgr-1
docker rm pg-repmgr-2
docker rm pg-repmgr-3
docker rm pg-pgbouncer-1
docker rm pg-barman-1