minrecoverypoint-recipe.sh

#!/bin/bash

PATH=/home/heikki/pgsql.84stable/bin:$PATH
PGDATAmaster=/home/heikki/pgsql.84stable/data-master
PGDATAstandbyB=/home/heikki/pgsql.84stable/data-standbyB
WALARCHIVE=/home/heikki/pgsql.84stable/walarchive

# exit on error
set -e

mkdir $WALARCHIVE

# Set up master
initdb -D $PGDATAmaster
#echo "wal_level=hot_standby" >> $PGDATAmaster/postgresql.conf
#echo "hot_standby=on" >> $PGDATAmaster/postgresql.conf
echo "archive_mode=on" >> $PGDATAmaster/postgresql.conf
echo "log_checkpoints=on" >> $PGDATAmaster/postgresql.conf
echo "full_page_writes=off" >> $PGDATAmaster/postgresql.conf
echo "archive_command='test ! -f $WALARCHIVE/%f && cp %p $WALARCHIVE/%f'" >> $PGDATAmaster/postgresql.conf

pg_ctl -w -D $PGDATAmaster start

# Set up standby
PGPORT=5432 psql -c "select pg_start_backup('foo', true)" postgres
cp -a $PGDATAmaster $PGDATAstandbyB
rm $PGDATAstandbyB/postmaster.pid
PGPORT=5432 psql -c "select pg_stop_backup()" postgres

echo "restore_command = '~/pgsql.84stable/bin/pg_standby -s 1 $WALARCHIVE %f %p %r'" >> $PGDATAstandbyB/recovery.conf

echo "port=5433" >> $PGDATAstandbyB/postgresql.conf

echo "log_line_prefix='M  %m %p '" >> $PGDATAmaster/postgresql.conf
echo "log_line_prefix='B %m %p '" >> $PGDATAstandbyB/postgresql.conf
pg_ctl -w -D $PGDATAmaster reload

pg_ctl -D $PGDATAstandbyB start

PGPORT=5432 psql -c "create table tbl(f int)" postgres
PGPORT=5432 psql -c "insert into tbl values(generate_series(1,1000));" postgres
PGPORT=5432 psql -c "vacuum tbl;" postgres
PGPORT=5432 psql -c "checkpoint;" postgres
PGPORT=5432 psql -c "select pg_switch_xlog();" postgres

echo "###### Wait for standby to replay the WAL file, and restart it to force a restartpoint..."
sleep 15
pg_ctl -w -D $PGDATAstandbyB stop -m f
pg_ctl -D $PGDATAstandbyB start
sleep 5

PGPORT=5432 psql -c "delete from tbl;" postgres
XID=`psql -t -A -c "select txid_current();" postgres`
PGPORT=5432 psql -c "select pg_current_xlog_insert_location();" postgres

PGPORT=5432 psql -c "vacuum verbose tbl;" postgres
PGPORT=5432 psql -c "select pg_switch_xlog(); " postgres

echo "###### Wait for standby to replay the new WAL file..."
sleep 10

echo "###### Killing standby..."
pg_ctl -w -D $PGDATAstandbyB stop -m i

# Set recovery point to just before vacuum.
echo "recovery_target_xid='$XID'" >> $PGDATAstandbyB/recovery.conf

echo "###### Restarting standby. It should refuse to start up,"
echo "###### because recovery_target_xid is before min recovery point."
pg_ctl -D $PGDATAstandbyB start