pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Continuous On-line Backups
3+ messages / 2 participants
[nested] [flat]

* Continuous On-line Backups
@ 2006-03-24 20:14  Thomas F. O'Connell <tfo@sitening.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Thomas F. O'Connell @ 2006-03-24 20:14 UTC (permalink / raw)
  To: pgsql-admin

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)




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Continuous On-line Backups
@ 2006-03-24 23:19  Peter Eisentraut <peter_e@gmx.net>
  parent: Thomas F. O'Connell <tfo@sitening.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Peter Eisentraut @ 2006-03-24 23:19 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Thomas F. O'Connell <tfo@sitening.com>

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/



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Continuous On-line Backups
@ 2006-03-25 06:23  Thomas F. O'Connell <tfo@sitening.com>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Thomas F. O'Connell @ 2006-03-25 06:23 UTC (permalink / raw)
  To: pgsql-admin


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)



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-03-25 06:23 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-24 20:14 Continuous On-line Backups Thomas F. O'Connell <tfo@sitening.com>
2006-03-24 23:19 ` Peter Eisentraut <peter_e@gmx.net>
2006-03-25 06:23   ` Thomas F. O'Connell <tfo@sitening.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox