history-files-not-kept-in-pg_xlog.sh

#!/bin/bash
#
# This script reproduces the 2nd problem from thread "Timeline history files
# restored from archive not kept in pg_xlog, while WAL files are":
#
#  The cascading replication patch made a change to the way WAL files restored
#  from archive are handled. Since the patch, when a WAL file is restored from
#  archive, it's copied under the correct filename in pg_xlog. Aside from
#  supporting cascading replication, this has the advantage that if the archive
#  subsequently goes offline, the standby can still recover back up to the
#  point where it was before, if it's restarted. It also means that you can
#  take an offline backup of the standby, and pg_xlog includes all the files
#  required to restore.
#
#  However, timeline history files are not included. When a standby restores a
#  timeline history file from the archive, it's restored under a temporary
#  filename, and effectively discarded after that. That means that if the
#  latest checkpoint is on a WAL segment that includes an earlier timeline
#  switch, you again need the archive to be online to restore from that, or
#  you get an error like:
#
#  LOG:  unexpected timeline ID 1 in log file 0, segment 3, offset 0
#
#  This is a pre-existing issue in 9.2. In git master, it also means that if a
#  standby follows a master through the archive, a cascading standby won't
#  find the timeline history files in the 1st master, and won't be able to
#  follow timeline switches.
#
#
#
# 1. Create master, and a standby that follows the master
# 2. Induce a timeline switch in the master. (In reality, timeline switches
#    don't happen in a master, but imagine that the standby is actually a
#    cascading standby that follows another standby, and the upstream standby
#    is promoted. From the cascading standby's point of view, it's the same.)
# 3. After the standby has replayed the timeline switch, shut it down.
# 4. Make the archive unavailable.
# 5. Restart standby. It will refuse to start up because it can't find the
#    history file.

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

# exit on error
set -e

mkdir $TESTDIR/walarchive

# Set up master
initdb -D $TESTDIR/data-master
echo "wal_level=hot_standby" >> $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 "archive_mode=on" >> $TESTDIR/data-master/postgresql.conf
echo "archive_command='cp %p $TESTDIR/walarchive/%f'" >> $TESTDIR/data-master/postgresql.conf

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

psql -c "create table tbl1(d text)" postgres

# Take an (offline) base backup, to initialize the standby
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 through the archive

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 "restore_command='cp $TESTDIR/walarchive/%f %p'" >> $TESTDIR/data-standbyB/recovery.conf
echo "standby_mode=on" >> $TESTDIR/data-standbyB/recovery.conf
echo "recovery_target_timeline='latest'" >> $TESTDIR/data-standbyB/recovery.conf

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

# Generate some WAL on the master
psql -c "insert into tbl1 values ('before timeline switch')" postgres;

# Bump timeline. This simulates a failover from master to another standby
pg_ctl -w -D $TESTDIR/data-master stop
echo "restore_command='/bin/false'" > $TESTDIR/data-master/recovery.conf
pg_ctl -w -D $TESTDIR/data-master start

# Generate some WAL on the master, after the timeline bump
psql -c "insert into tbl1 values ('timeline was bumped in master'); select pg_switch_xlog()" postgres

sleep 10 # wait for the standby to follow the new timeline, and replay all WAL

# stop master (not strictly required, but we don't need it anymore)
pg_ctl -w -D $TESTDIR/data-master stop

# Stop standby
pg_ctl -w -D $TESTDIR/data-standbyB stop

# make it more obvious that something's wrong by taking the standby out of
# standby mode. (this is equivalent with taking the archive offline)
echo "restore_command='/bin/false'" > $TESTDIR/data-standbyB/recovery.conf

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