Re: Backup routine - Mailing list pgsql-admin

Centuries ago, Nostradamus foresaw when pgman@candle.pha.pa.us (Bruce Momjian) would write:
> Christopher Browne wrote:
>> The world rejoiced as peterandsarah@blueyonder.co.uk (Peter and Sarah Childs) wrote:
>> >     However there is a third way. That should be safe but some
>> > people may disagree with me! If you can "freeze" the disk while you
>> > take the backup. The backup can be used as if the computer had
>> > crashed with no hard disk failure at all. Ie WAL will be consistant
>> > and database may take longer but once it is up it will be safe (like
>> > paragaph 1). Now freezeing a disk for backup is not that
>> > difficult. You should be doing it anyway for user file
>> > consistancy. (You don't want the first 30 pages of you document to
>> > disagree with the end because somone was saving it during the
>> > backup!
>>
>> I heard D'Arcy Cain indicate that some SAN systems (I think he
>> mentioned NetApp) support this sort of thing, too.  Digital's AdvFS
>> also supports it.
>>
>> Of course, if you take this approach, you have to make _certain_
>> that when you "freeze" a replica of a filesystem, that _ALL_ of the
>> database is contained in that one filesystem.  If you move WAL to a
>> different filesystem, bets would be off again...
>
> Also, I assume you have to stop the server just for a moment while
> you do the freeze, right?

I'm sure that's _preferable_.

Supposing you don't, the result is that the backup will be treated
much like the condition where a server is "terminated by power
failure," and, at restart, the system will have to rummage around the
WAL to clean up a bit.

Obviously not what you'd want, in an _ideal_ world, but it fits into
what WAL is supposed to be able to protect against, right?

If-and-when PITR gets into place, I'd think one a valued feature would
be the notion of being able to signal the postmaster to tell it to
close off a WAL and open a new one (even though we might not strictly
be due for it).  (Note that the "O-guys" can do something like this
with their "archive logs.")

If that signal can be submitted, then someone might be able to take
this sort of "cloned filesystem" backup, and just drop off the last
WAL file as irrelevant.  That might not be quite exactly what's
imminent for 7.5, mind you...
--
select 'cbbrowne' || '@' || 'acm.org';
http://www.ntlug.org/~cbbrowne/sap.html
(eq? 'truth 'beauty)  ; to avoid unassigned-var error, since compiled code
                      ; will pick up previous value to var set!-ed,
                      ; the unassigned object.
-- from BBN-CL's cl-parser.scm