Re: Attempt to re-archive existing WAL logs afterrestoringfrom backup - Mailing list pgsql-admin

Simon Riggs wrote:
> I've spent some time re-examining the code and I can't see a route for
> the discussed problem to occur. All the main code paths do not exhibit
> this problem in 8.2, so I'm not sure where to go from here.
>
> If you can help further, please give me a shout.

Thanks a lot for looking into this.

I've attached a self-contained minimal shell script which sets up a
cluster and demonstrates the problem. I hope that will provide an
unambiguous description. I'm running it on Ubuntu 7.04 (i386) and the
script includes all the commands to build PostgreSQL from scratch, but
obviously you can snip that stuff out and change the paths to suit your
environment.

The script ends with the server log showing the archiver attempting to
re-archive the last log that was restored in the recovery procedure.

Thanks again.

--
Jon
#!/bin/sh

set -e

apt-get -y install curl
cd /tmp
curl -O ftp://ftp5.us.postgresql.org/pub/PostgreSQL//source/v8.2.4/postgresql-8.2.4.tar.gz
cat <<EOF | sha512sum -c

571fc42abd7b06e0fcc33a326796bb28bdcb7e3260d3e4b56e40f0e22517784460829900e2941e978ca253cac7c8e00397c342ba6397a0657b4b68157aa425e9
*postgresql-8.2.4.tar.gz
EOF
adduser --system --home /var/lib/pgdata --no-create-home --gecos "PostgreSQL administrator,,," --group --disabled-login
postgres
apt-get -y install libc6-dev gcc make libreadline5-dev zlib1g-dev libssl-dev
tar -zxof postgresql-8.2.4.tar.gz
cd postgresql-8.2.4
./configure
make
make install
# apt-get -y autoremove --purge libc6-dev gcc make libreadline5-dev zlib1g-dev libssl-dev
cd /

#----------------------
# End of installation.
#----------------------

PGSQL_BIN=/usr/local/pgsql/bin

DB=/tmp/duplicatexlogdemo
XLOG_ARCHIVE=/tmp/xlogarchive
PGDATA_BACKUP=/tmp/pgdata_backup.tar.gz
MAKE_BACKUP=/tmp/makepgdatabackup
RESTORE_BACKUP=/tmp/restorepgdatabackup
SERVER_LOG=/tmp/serverlog

mkdir -p $DB
chown postgres:postgres $DB
sudo -u postgres $PGSQL_BIN/initdb -D $DB

mkdir -p $XLOG_ARCHIVE
chown postgres:postgres $XLOG_ARCHIVE

cat <<EOF >$MAKE_BACKUP
#!/bin/sh
sudo -u postgres $PGSQL_BIN/psql -c "SELECT pg_start_backup('testbackup')" >/dev/null
tar -C $DB --exclude=pg_xlog -c . | gzip -c >$PGDATA_BACKUP
sudo -u postgres $PGSQL_BIN/psql -Atc "SELECT pg_xlogfile_name(pg_stop_backup())"
EOF
chmod 755 $MAKE_BACKUP

cat <<EOF >$RESTORE_BACKUP
#!/bin/sh
mkdir -p $DB/pg_xlog/archive_status
echo "restore_command = 'cp $XLOG_ARCHIVE/%f %p'" >$DB/recovery.conf
chown -R postgres:postgres $DB
chmod -R g=,o= $DB
tar -C $DB -zxf $PGDATA_BACKUP
EOF
chmod 755 $RESTORE_BACKUP

echo "archive_command = 'test ! -f $XLOG_ARCHIVE/%f && cp %p $XLOG_ARCHIVE/%f'" >>$DB/postgresql.conf

sudo -u postgres $PGSQL_BIN/pg_ctl start -w -D $DB -l $SERVER_LOG
sudo -u postgres $PGSQL_BIN/psql -c "CREATE DATABASE blah1"
$MAKE_BACKUP
# Create some logs to recover from:
sudo -u postgres $PGSQL_BIN/psql -c "CREATE DATABASE blah2; SELECT pg_switch_xlog()"
sudo -u postgres $PGSQL_BIN/psql -c "CREATE DATABASE blah3; SELECT pg_switch_xlog()"
sudo -u postgres $PGSQL_BIN/psql -c "CREATE DATABASE blah4; SELECT pg_switch_xlog()"
sudo -u postgres $PGSQL_BIN/psql -c "CREATE DATABASE blah5; SELECT pg_switch_xlog()"
sudo -u postgres $PGSQL_BIN/psql -c "CREATE DATABASE blah6; SELECT pg_switch_xlog()"
sudo -u postgres $PGSQL_BIN/pg_ctl stop -D $DB
cd /
rm -Rf $DB
$RESTORE_BACKUP
sudo -u postgres $PGSQL_BIN/pg_ctl start -w -D $DB -l $SERVER_LOG
$MAKE_BACKUP
tail $SERVER_LOG