Demo PostgreSQL 13 Point-in-time Recovery with Docker

pitr.sh

#!/bin/bash

VERSION=13

function cleanup() {
  docker rm -f master replica >&/dev/null
  rm -Rf $(pwd)/data $(pwd)/wal_archive 
}

function wait_until_ready() {
  docker exec "$1" sh -c 'until pg_isready --user=postgres >/dev/null; do sleep 1; done'
}

function print_table() {
  echo
  docker exec "$1" psql --user=postgres --command='SELECT * FROM temp'
}

cleanup

docker network create postgres >&/dev/null

set -e

# Start the master service
docker run --network=postgres --name=master -e POSTGRES_PASSWORD=demo -d \
  --volume=$(pwd)/wal_archive:/root \
  postgres:${VERSION} \
  postgres \
  -c log_line_prefix='%m ' -c log_statement=all \
  -c synchronous_commit='on' \
  -c wal_level=replica -c archive_mode=on -c archive_command='test ! -f /root/%f && cp %p /root/%f' -c archive_timeout=1 \
  >/dev/null

# Create base backup to local ./data
wait_until_ready master
docker exec master sh -c 'echo host replication postgres 0.0.0.0/0 trust >> "${PGDATA}/pg_hba.conf"; \
  su - postgres -c "PGDATA=/var/lib/postgresql/data /usr/lib/postgresql/13/bin/pg_ctl reload"'
docker run --rm --network=postgres \
  --volume=$(pwd)/data:/tmp/data \
  postgres:${VERSION} \
  sh -c 'until pg_isready --host=master --user=postgres >/dev/null; do sleep 1; done && 
         pg_basebackup --host=master --user=postgres -D /tmp/data --progress' >/dev/null

# Generate some WAL in local ./wal_archive
docker exec master psql --user=postgres --command='CREATE TABLE temp(i int, t timestamp)' >/dev/null
for i in {1..10}; do
  docker exec master psql --user=postgres --command="INSERT INTO temp VALUES (${i}, current_timestamp)" >/dev/null
  sleep 1
done
print_table master

# Create recovery.conf to setup a recovery
target_time=$(docker exec master psql -qtAX --user=postgres --command='SELECT t FROM temp WHERE i = 6;')
echo "hot_standby = on
restore_command = 'cp /root/%f %p'
recovery_target_time = '${target_time}'" >> $(pwd)/data/postgresql.conf
echo "" > $(pwd)/data/standby.signal

# Start the recovery
docker run --network=postgres --name=replica --detach \
  --volume=$(pwd)/wal_archive:/root \
  --volume=$(pwd)/data:/var/lib/postgresql/data \
  postgres:${VERSION} \
  postgres -c log_destination=csvlog -c logging_collector=on -c log_filename=postgresql.log >/dev/null

wait_until_ready replica

# Wait for recovery to complete
until [ "$(docker exec replica psql -qtAX --user=postgres --command='SELECT pg_is_wal_replay_paused()')" == 't' ]; do
  sleep 1
done

cat $(pwd)/data/log/postgresql.csv

print_table replica

cleanup