I don't know much about PostgresQL, but on MS SQL server and Oracle (IIRC), any update that would leave the database inconsistent should be inside a transaction. Any snapshot will not happen while a transaction is in progress; therefore the snapshot is consistent and restorable. I guess it depends on how sane your programmers are.
> -----Original Message----- > From: Niall O Broin [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 14, 2002 11:32 AM > To: [EMAIL PROTECTED] > Subject: Re: Backing up PostgreSQL? > > > On Thu, Jun 13, 2002 at 10:29:33PM -0500, Kirk Strauser wrote: > > > I would re-do the backup steps as > > > > 1) Make a snapshot > > 2) Use dump to back up that completely static filesystem image > > 3) Remove the snapshot > > This is NOT guaranteed to work - it may, if you're lucky. By > doing this > you're guaranteeing that the database files, no matter how active the > database, are frozen in time via the snapshot. But the big > issue that you're > failing to address here is that any one point in time the > database files are > not internally consistent. The currently running database > instance has a > consistent view of the data because it has data in memory and > on disk and of > course it knows what's where. This is why there are programs > such as pgdump > etc. One means suggested to backup a database which can't be > stopped is to > have your database files on a mirrored disk pair (or pairs). > Then to make a > backup, you issue read locks on all the tables. Having got > those locks, you > break the disk mirror at an OS level, then relinquish the locks. > > Then you backup the broken part of the mirror which is not in > use. I think > you could do something similar with a snapshot supporting > filesystem. You > lock all the tables, then make a snapshot, then relinquish > the locks. And > you should then be able to backup a consistent view of the > tables from the > snapshot. > > The above, however, is entirely speculation and I haven't > tried it. But even > as speculation, it's more likely to work than simply backing > up a snapshot. > Bear in mind that the snapshot may often work but it won't > always and your > backup system should cover every eventuality. > > > > Regards, > > > > Niall O Broin >
