From: Jon Colverson <pgsql@vcxz.co.uk>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-admin@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Attempt to re-archive existing WAL logs afterrestoringfrom backup
Date: Sat, 02 Jun 2007 01:07:44 +0100
Message-ID: <4660B4D0.4090102@vcxz.co.uk> (raw)
In-Reply-To: <1180728140.26297.261.camel@silverbirch.site>
References: <465BA7C5.6080400@vcxz.co.uk>
<465E55F2.2020103@vcxz.co.uk>
<28998.1180620113@sss.pgh.pa.us>
<465EE541.3020501@vcxz.co.uk>
<1180695755.26297.101.camel@silverbirch.site>
<1180728140.26297.261.camel@silverbirch.site>
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
Attachments:pgsql-duplicate-xlog-demo.sh (text/plain, 2.5K) download
#!/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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pgsql@vcxz.co.uk, simon@2ndquadrant.com, tgl@sss.pgh.pa.us
Subject: Re: Attempt to re-archive existing WAL logs afterrestoringfrom backup
In-Reply-To: <4660B4D0.4090102@vcxz.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox