[ On , June 13, 2002 at 22:29:33 (-0500), Kirk Strauser wrote: ]
> Subject: Re: Backing up PostgreSQL?
>
>
> At 2002-06-13T23:07:54Z, Niall O Broin <[EMAIL PROTECTED]> writes:
>
> > Nothing to do with BSD, Linux, or even, Gawd help us, Windows. If you
> > don't take the database offline then the files which make up the database
> > are not guaranteed to be in anything resembling a consistent state i.e. if
> > you copy those files, in whatever manner, there's absolutely no guarantee
> > that you'll be later able to use the database program to read the files.
>
> That's the really nice thing about FreeBSD's new snapshots (in the
> development tree). When you freeze the filesystem image, it really is a
> snapshot. If all of your database files are on the same filesystem then
> you're pretty much guaranteed a consistent backup, regardless of the amount
> of updates being committed to the database.
Which is still really totally useless for backing up an RDBMS like
PostgreSQL. Even if everything is fsync()ed all the time there's still
going to be dozens of race conditions where a backup, even if supposedly
nearly instantaneous like a snapshot, will result in an inconsistent
database image. With PostgreSQL in particular (since it only uses
filesystem files, not raw disk) you MUST stop the database --
i.e. completely stop the processes that have database files open so that
those files are completely all closed and so that the kernel filesystem
interface can present a consistent view of the data that's been written
to the filesystem. This is becoming even more critical as PostgreSQL
gains ever more features that allow the DBA to optimise when writes are
done to the filesystem.
The only good thing about filesystem snapshots is that you can in theory
do them much quicker (than a full dump) so database downtime is much lower.
> I would re-do the backup steps as
>
0) Stop the database
> 1) Make a snapshot
1.5) restart the database
> 2) Use dump to back up that completely static filesystem image
/^\
a level zero
> 3) Remove the snapshot
Of course if you're concerned about the integrity of your data (and
perhaps you give your data a higher value than even the application and
support system it is stored and manipulated in), particularly for
off-site backups, then the only really sure way to backup the database
is to do a dump at the database level into a flat-file form, and of
course PostgreSQL has pg_dump which has already been discussed.
You should probably be doing regular pg_dumps anyway, even if you don't
do them every time you run amanda. They're the only way you can be sure
your disaster recovery plan works -- i.e. they let you reload a new test
database to be sure everything works.
--
Greg A. Woods
+1 416 218-0098; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Planix, Inc. <[EMAIL PROTECTED]>; VE3TCP; Secrets of the Weird <[EMAIL PROTECTED]>
