Thread: Continuous On-line Backups
Okay, the picture is becoming clearer (to me) in terms of a continuous on-line backup scenario with postgres, and now I'd like to get more understanding of how flexible it is. Currently, I nightly pg_dump an entire cluster. I ship it to a remote server where I restore it. This has dual utility as a verification of the quality of the dump as well as creating a standby database cluster that could be used in a pinch during a failover. I also ship the dump to an off-site location as an extra safeguard. Continuous on-line backups are promoted as a high availability option because they would allow the failover process to occur such that the standby is much less behind than the standby in my current scenario. But it seems like I'll be losing a little flexibility in terms of redundancy and verification. The way I understand it, continuous on- line backups are actually in recovery mode until a failover scenario occurs. I.e., restore_command actually prevents recovery.done from being created by perpetually waiting on more WAL segment files. This renders the recovery database useless until an event is triggered. Is this a correct assessment? So long as I know that nothing will access the recovery database with write activity, is there a way to toggle the continuity so that I could allow recovery to complete on a nightly basis, pg_dump the recovered database (a read-only action), and then resume recovering? -- 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)
Thomas F. O'Connell wrote: > So long as I know that nothing will access the recovery database with > write activity, is there a way to toggle the continuity so that I > could allow recovery to complete on a nightly basis, pg_dump the > recovered database (a read-only action), and then resume recovering? Even a read-only action uses up transaction numbers and other things, so I'd expect that to be a problem. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Mar 24, 2006, at 5:19 PM, Peter Eisentraut wrote: > Thomas F. O'Connell wrote: >> So long as I know that nothing will access the recovery database with >> write activity, is there a way to toggle the continuity so that I >> could allow recovery to complete on a nightly basis, pg_dump the >> recovered database (a read-only action), and then resume recovering? > > Even a read-only action uses up transaction numbers and other > things, so > I'd expect that to be a problem. Good point. My chief concern with the loss of flexibility would be the greater difficulty in detecting a lost or corrupted WAL segment file if archive_command is operating across a network (or locally, with an external script responsible for moving the files across the network). With nightly pg_dumps/pg_restores, at least I know my window of a valid database is on the order of 24 hours. Simon Riggs recently recommended base backups as a weekly process with a continuous on-line backup operating in the interim. I guess the price to be paid for the higher availability is increased scrutiny of the process. -- 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)