Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6F9659FC749 for ; Fri, 1 Jun 2007 21:07:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 67800-07 for ; Fri, 1 Jun 2007 21:07:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from out2.smtp.messagingengine.com (out2.smtp.messagingengine.com [66.111.4.26]) by postgresql.org (Postfix) with ESMTP id A1F269FC6ED for ; Fri, 1 Jun 2007 21:07:38 -0300 (ADT) Received: from compute1.internal (compute1.internal [10.202.2.41]) by out1.messagingengine.com (Postfix) with ESMTP id E222122BCE9; Fri, 1 Jun 2007 20:07:36 -0400 (EDT) Received: from heartbeat2.messagingengine.com ([10.202.2.161]) by compute1.internal (MEProxy); Fri, 01 Jun 2007 20:07:36 -0400 X-Sasl-enc: fbOpBGRCa8qdacVZFVJvL/1uuwscKhaZXkgICBAgRQa9 1180742856 Received: from [62.3.213.205] (unknown [62.3.213.205]) by mail.messagingengine.com (Postfix) with ESMTP id 306FF18461; Fri, 1 Jun 2007 20:07:36 -0400 (EDT) Message-ID: <4660B4D0.4090102@vcxz.co.uk> Date: Sat, 02 Jun 2007 01:07:44 +0100 From: Jon Colverson User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.8.1.3) Gecko/20070326 Thunderbird/2.0.0.0 Mnenhy/0.7.5.0 MIME-Version: 1.0 To: Simon Riggs CC: pgsql-admin@postgresql.org, Tom Lane Subject: Re: Attempt to re-archive existing WAL logs afterrestoringfrom backup 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> In-Reply-To: <1180728140.26297.261.camel@silverbirch.site> Content-Type: multipart/mixed; boundary="------------000206010008040206010406" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/10 X-Sequence-Number: 25678 This is a multi-part message in MIME format. --------------000206010008040206010406 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------000206010008040206010406 Content-Type: text/plain; name="pgsql-duplicate-xlog-demo.sh" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="pgsql-duplicate-xlog-demo.sh" #!/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 <$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 <$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 --------------000206010008040206010406--