[ On Saturday, June 15, 2002 at 14:42:35 (+0200), Ragnar Kj�rstad wrote: ]
> Subject: Re: Backing up PostgreSQL?
>
> On Fri, Jun 14, 2002 at 09:15:15PM -0400, Greg A. Woods wrote:
> > [ On Saturday, June 15, 2002 at 00:45:12 (+0200), Ragnar Kj�rstad wrote: ]
> > > Subject: Re: Backing up PostgreSQL?
> > >
> > > snapshots (LVM snapshots) are not "supposedly nearly instantaneous", but
> > > "instantaneous". All write-access to the device is _locked_ while the
> > > snapshot is in progress (the process takes a few milliseconds, maybe a
> > > second on a big system), and there are _no_ races. That's the whole
> > > point of the snapshot!
> >
> > That's irrelevant from PostgreSQL's point of view. There's no sure way
> > to tell the postgresql process(es) to make the on-disk database image
> > consistent before you create the snapshot. The race condition is
> > between the user-level process and the filesystem. The only sure way to
> > guarantee a self-consistent backup is to shut down the process so as to
> > ensure all the files it had open are now closed. PostgreSQL makes no
> > claims that all data necessary to present a continually consistent view
> > of the DB will be written in a single system call. In fact this is
> > impossible since the average database consistes of many files and you
> > can only write to one file at a time through the UNIX system interface.
>
> Yes it does, and no it's not impossible.
> see http://www.postgresql.org/idocs/index.php?wal.html
Sorry but you're not talking about what I'm talking about. Yes WAL will
give you a means to hopefully recover a restored database into a
consistent view. It will NOT, and cannot possibly, guarantee the
consistency of many files on the disk, not even with fsync() (though
PostgreSQL does try very hard to order it's own metadata writes -- but
that's not the point and it's still irrelevant to the question).
Unless you can do a global database lock at a point where there are no
open transactions and no uncommitted data in-core, the only way to
guarantee a consistent database on disk is to close all the database
files. Even then if you're very paranoid and worried about a hard crash
at a critical point during the snapshot operation you'll want to first
flush all OS buffers too, which means first unmounting and then
remounting the filesystem(s) (and possibly even doing whatever is
necessary to flush buffers in your hardware RAID system).
It really_ is best to just use pg_dump and back up the result.
> Stopping the database means closing all the connections, and if you have
> multiple applications doing long overlapping transactions that don't
> recover well from shutting down the database, then you have a problem.
I agree, however this is an operational problem, not a technical
problem. If you choose to use filesystem backups for your database then
you need to have a very clear and deep understanding of these issues.
It really Really is best to just use pg_dump and back up the result.
> The newest release of postgresql always use fsync (on it's log) unless
> you specificly turn it off. You shouldn't do that if you care about your
> data.
I agree, but we're not discussing what you and I do, but rather what
random John Doe DBA does. There's ample quantity of suggestion out in
the world already that makes it possible he will turn off fsync for
performance reasons
> The only requirement on the filesystem is that it is journaling as well,
> so it's always kept in a consistant state like the postgresql-database
> is..
Now you're getting a little out of hand. A journaling filesystem is a
piling of one set of warts ontop of another. Now you've got a situation
where even though the filesystem might be 100% consistent even after a
catastrophic crash, the database won't be. There's no need to use a
journaling filesystem with PostgreSQL (particularly if you use a proper
hardware RAID subsystem with either full mirroring or full level 5
protection). Indeed there are potentially performance related reasons
to avoid journaling filesystems! I've heard people claim they are just
as fast with PostgreSQL, and some even have claimed performance
improvements, but none of the claimants could give a scientific
description of their benchmark and I'm pretty sure they were seeing
artifacts, not a representation of real-world throughput.
> Restoring a snapshot is the fastest possible way of getting the system
> back online, and even a tape-backup of the snapshot will be faster than
> importing the database.
This is true. However before you give that fact 100% weight in your
decision process you need to do a lot more risk assessment and disaster
planning to understand whether or not the tradeoffs inherent will not
take away from your real needs.
I still believe it really Really REALY is best to just use pg_dump and
back up the result. If the time to reload a dump is a major concern to
you then you have other far more critical issues to deal with before you
can make a sane choice about backup integrity and disaster recovery.
--
Greg A. Woods
+1 416 218-0098; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Planix, Inc. <[EMAIL PROTECTED]>; VE3TCP; Secrets of the Weird <[EMAIL PROTECTED]>
