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
pg...@cisoradr-ext:/cis/pgsql/katana7/backup/.
echo "Stop backup"
echo "SELECT pg_stop_backup();" | /local/pkg/bin/psql template1
Sam
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Florian Pflug
Sent: Sunday, 6 June 2010 10:11 PM
To: [email protected]
Cc: [email protected]
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
--
Sent via pgsql-admin mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
Sent via pgsql-admin mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
