Re: PITR Recovery Question

Here's a script to make your backup and rsync it to a remote destination:

#!/bin/bash
echo "checkpoint"
echo "CHECKPOINT;" | /local/pkg/bin/psql template1
echo "start backup"
echo "SELECT pg_start_backup('cisoradr:/cis/pgsql/katana7/backup');" | /local/pkg/bin/psql template1
echo "rsync"
/local/pkg/bin/rsync --delete -azxH /local/app/postgres/data pgsql(at)cisoradr-ext:/cis/pgsql/katana7/backup/.
echo "Stop backup"
echo "SELECT pg_stop_backup();" | /local/pkg/bin/psql template1

Sam

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Florian Pflug
Sent: Sunday, 6 June 2010 10:11 PM
To: gnanam(at)zoniac(dot)com
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] PITR Recovery Question

On Jun 5, 2010, at 9:05 , Gnanakumar wrote:
> Thanks for your valuable suggestion and a detailed step on common way to use
> PITR. Things are very clear now except that I've some other question in
> connection to this.
>
>> The correct way to clean out pg_xlog therefore is to either disable WAL archiving, or to make sure your archive_command succeeds eventually.
>
> Probably I would go with the 2nd option, that is allowing archive command to
> run successfully until things are completely clear.
>
> But this question is for my understanding: In case if I decide to go with
> 1st option, that is disable WAL archiving for a while, will it completely
> clean out files from pg_xlog/ and pg_xlog/archive_status/ directories, so
> that I can start the PITR by taking base backup by enabling WAL archiving
> later?

If you disable WAL archiving by setting archive_command to 'true', it'll surely clean out the files, since postgresql will actually believe it archived them successfully. I not sure what happens if you set archive_command to '' - that might disable the archiving process completely, and hence prevent the cleanup.

>> A common way to use PITR is the following.
>
>> 4) You remove all WAL segments that predate the remaining base backups. For that, you find the backup history file in the archive directory that corresponds to the oldest remaining base backup and then remove all WAL segments whose name is numerically smaller than the <number1> from that backup history file. Keeping older WAL segments buys you nothing - WAL files without a base backup that *predates* them are worthless.
>
> Can you share with me any automated shell script that takes care of this
> removal automatically? Or can you share any systematic way (steps) of doing
> things if I want to do this manually?

Sorry, I don't have a script for this at hand. But a quick search through the pgsql-admin archive brings up this post, which contains such a script.
http://archives.postgresql.org/pgsql-admin/2006-03/msg00337.php

best regards,
Florian Pflug