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)
