[ 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]>