fix-end-of-standby-backup-1.sh

#!/bin/bash

TESTDIR=/home/heikki/pgsql.92stable
PATH=/home/heikki/pgsql.92stable/bin:$PATH

# exit on error
set -e

# Set up master
initdb -D $TESTDIR/data-master
echo "autovacuum=off" >> $TESTDIR/data-master/postgresql.conf
echo "wal_level=hot_standby" >> $TESTDIR/data-master/postgresql.conf
echo "max_wal_senders=5" >> $TESTDIR/data-master/postgresql.conf
echo "checkpoint_segments=50" >> $TESTDIR/data-master/postgresql.conf
echo "shared_buffers=1MB" >> $TESTDIR/data-master/postgresql.conf
echo "log_line_prefix='M  %m %p '" >> $TESTDIR/data-master/postgresql.conf

echo

# Accept replication connections
echo "local   replication     heikki                                trust" >> $TESTDIR/data-master/pg_hba.conf
echo "host   replication     heikki             127.0.01/32                   trust" >> $TESTDIR/data-master/pg_hba.conf
echo "host   replication     heikki             ::1/128                   trust" >> $TESTDIR/data-master/pg_hba.conf

pg_ctl -w -D $TESTDIR/data-master start

# 1. Take an (offline) base backup
pg_ctl -w -D $TESTDIR/data-master stop
cp -a $TESTDIR/data-master $TESTDIR/data-backup
pg_ctl -w -D $TESTDIR/data-master start

# 2. Set up standby B, following the master with streaming replication

cp -a $TESTDIR/data-backup $TESTDIR/data-standbyB

sed -i "s/log_line_prefix=.*/log_line_prefix='B %m %p '/g" $TESTDIR/data-standbyB/postgresql.conf
echo "port=5433" >> $TESTDIR/data-standbyB/postgresql.conf
echo "hot_standby=on" >> $TESTDIR/data-standbyB/postgresql.conf

echo "primary_conninfo=''" >> $TESTDIR/data-standbyB/recovery.conf
echo "standby_mode=on" >> $TESTDIR/data-standbyB/recovery.conf

pg_ctl -w -D $TESTDIR/data-standbyB start

# Stop the master. This creates a shutdown checkpoint, which is replicated
# to the standby.
pg_ctl -w -D $TESTDIR/data-master stop

 # wait for the standby to replay the segment containing the checkpoint record
sleep 4

# 3. Take a base backup from the standby through streaming replication
# The first and last record required to restore this backup happens to be the
# shutdown checkpoint.

psql -c "checkpoint;" postgres -p 5433
pg_basebackup -D $TESTDIR/data-standbyC -p 5433 -x

# stop the standby (not required, but stops standby from complaining)
pg_ctl -w -D $TESTDIR/data-standbyB stop

# 4. Start up a new server (in master mode) from the backup.
sed -i "s/log_line_prefix=.*/log_line_prefix='C %m %p '/g" $TESTDIR/data-standbyC/postgresql.conf
echo "port=5434" >> $TESTDIR/data-standbyC/postgresql.conf

rm $TESTDIR/data-standbyC/recovery.conf

pg_ctl -w -D $TESTDIR/data-standbyC start