Re: PITR as Online Backup Solution

Hi Thomas,

I've been trying to get a similar system up and running, and I have to say
one point in the documentation isn't particularly clear.

It says that you can string together an almost endless supply of WAL logs to
replay against a database, however you must always start from a base backup
- ie. You cannot do a recovery on your standby, add a log from your live,
then re-do the recovery again. You would have to restore your base backup,
then replay all the logs to include the new one.

A guy called Simon Riggs came up with an idea of having a base backup, then
creating a script to be called in your recovery_command that reads the WAL
logs in, but if it cannot find one it needs, it sits and waits and retries
every so often - every time it finds a new log, that is passed back to the
PG recovery. Then you would have to find a method of telling the script
that you wish to bring the database up and it will exit and allow PGSQL to
come up at the current state with the latest data.

This is a script I am looking to develop myself in the coming months, as I
would also like a very similar situation to yourself.

Replication sounds like a better option, and I am also looking to Slony-I
but haven't got it up and running yet. Running this would keep the backup
system up to date as much as possible, and you could simply tell your
applications to switch over to the standby as the need arises - however, I
believe Slony is only master-to-slave, so any writes on your slave will not
be propagated back to your master.

Regards

Andy

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Thomas F. O'Connell
Sent: Thursday, 02 March, 2006 10:39 PM
To: PGSQL Admin
Subject: [ADMIN] PITR as Online Backup Solution

I'm administering a database that is not immediately a good candidate
for replication via Slony. As an interim solution, I'd like to give
PITR a shot. The primary goal is to have a failover scenario that
allows for recovery within a window that's much smaller than it would
be if the only option were the output of the previous night's
pg_dump, and the need has arisen jointly with the fact that pg_dump
has become an unwieldy process on this database.

Here's the main question: Is it possible to replay logs continuously
against a database that has already been recovered? The documentation
only covers the scenario of full recovery from scratch, and I'm
wondering if this is because this is the only scenario possible with
the current level of PITR functionality.

Ideally, I'd be able to take a base backup of a production system,
copy it to a remote system, which is also the repository for segment
files generated by archive_command, and complete the recovery process
outlined in the docs. From that point, it would make sense to me that
I should be able to continuously replay WAL files against the new
database (possibly as soon as archive_command generates a new one)
without having to purge my data directory. Is that a reasonable
assumption?

If so, then I need to know more about which steps to use from the
recovery scenario presented in the docs and how to identify the state
in which a given segment file exists. For instance, it's not
immediately clear from the docs what happens to the segment files
after recovery_command runs during the recovery scenario. It says
those segments are copied from the archive directory, but then what?
Are they recycled as in a basic postgres installation?

Also, if this system eventually works as I expect it might be able
to, would there ever be a reason to redo it from scratch (i.e., is
one base backup sufficient ad infinitum)?

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
3004 B Poston Avenue
Nashville, TN 37203-1314
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

!DSPAM:14,4407742449411499595736!