Re: Backup routine

On Sun, 10 Aug 2003, Christopher Browne wrote:

> Centuries ago, Nostradamus foresaw when pgman(at)candle(dot)pha(dot)pa(dot)us (Bruce Momjian) would write:
> > Christopher Browne wrote:
> >> The world rejoiced as peterandsarah(at)blueyonder(dot)co(dot)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.

Note that many NAS storage systems support a "snapshot" mechanism that
basically does this in about 1/10th of a second.

It produced horrible hickups for our Linux boxen with default NFS
settings, whereby our NFS mounts would disappear for three or four minutes
after a snapshot.

I've never, by the way, run my database on a NAS, and probably never will,
if I have any say in it. We just use NAS for static document storage
(hundreds of gigs of it.)