Demo PostgreSQL Point-in-time Recovery with Docker

pitr.sh

#!/bin/bash

VERSION=9.6

function cleanup() {
  docker rm -f master replica >&/dev/null
  rm -Rf /tmp/data /tmp/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 --detach \
  --volume=/tmp/wal_archive:/root \
  postgres:${VERSION} \
  postgres \
  -c fsync=off -c full_page_writes=off \
  -c log_line_prefix='%m ' -c log_statement=all \
  -c synchronous_commit='on' \
  -c wal_level=replica -c max_wal_senders=1 -c archive_mode=on -c archive_command='cp %p /root/%f' -c archive_timeout=1 \
  >/dev/null

# Create base backup to local /tmp/data
wait_until_ready master
docker exec master sh -c 'echo host replication postgres 0.0.0.0/0 trust >> "${PGDATA}/pg_hba.conf"'
docker run --rm --network=postgres \
  --volume=/tmp/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 /tmp/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;')
cat << EOF > /tmp/data/recovery.conf
restore_command = 'cp /root/%f %p'
recovery_target_time = '${target_time}'
EOF

# Start the recovery
docker run --network=postgres --name=replica --detach \
  --volume=/tmp/wal_archive:/root \
  --volume=/tmp/data:/var/lib/postgresql/data \
  postgres:9.6 \
  postgres \
  -c hot_standby=on \
  -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_xlog_replay_paused()')" == 't' ]; do
  sleep 1
done

cat /tmp/data/pg_log/postgresql.csv

print_table replica

cleanup

@LS80 Thanks for your tremendous example, I forked and make another version for 13+ version.

https://gist.github.com/tpai/6115f14809b6427ae6070598d194b5e7

Hope this can help someone who seeks it.