pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (7+ messages)

Message-ID: <4660B4D0.4090102@vcxz.co.uk>
Permalink:  ../4660B4D0.4090102@vcxz.co.uk/
Also on:    postgresql.org/message-id/4660B4D0.4090102@vcxz.co.uk

 · 

reply

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