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@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] 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!
