Re: Backup routine

On Wed, 13 Aug 2003, Murthy Kambhampaty wrote:

> On Monday, August 11, 2003 17:26, dalgoda(at)ix(dot)netcom(dot)com
> [mailto:dalgoda(at)ix(dot)netcom(dot)com] wrote:
> >From: dalgoda(at)ix(dot)netcom(dot)com [mailto:dalgoda(at)ix(dot)netcom(dot)com]
> >Sent: Monday, August 11, 2003 17:26
> >To: pgsql-admin(at)postgresql(dot)org
> >Subject: Re: [ADMIN] Backup routine
> >
> >
> >In article <200308110313(dot)h7B3DCv06482(at)candle(dot)pha(dot)pa(dot)us>,
> >Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> >>Also, I assume you have to stop the server just for a moment while you
> >>do the freeze, right?
> >
> >It depends on if you need known state or just consistent state.
> >
> >Taking a snapshot of the system will get you a consistent
> >state just like
> >if the machine crashed. You can restore that snapshot, bring
> >PG back up
> >and everything will work. Of course, you really have no way of knowing
> >what transactions were commited and what were not.
> >
> >On the other hand, stop the server/snapshot/start the server
> >gives you not
> >only consistency, but a known state. That is, you know for sure that
> >whatever was done before you stopped the server is what was done.
> >
> But these considerations apply to pg_dump-s as well, no? I guess with
> pg_dump you CAN dump one database at a time, and you can "quiesce" each
> database before dumping -- disallow connections to that database for the
> duration of the pg_dump, and wait for all transactions to complete before
> starting pg_dump -- which is a little more flexible. Given the time it takes
> to do a pg_dump on databases over a few gigabytes in size, though, I can't
> say I find the flexibility valuable.

But that's still not exactly the same. If you pg_dump a single database
in a cluster, THAT database will be consistent to itself, guaranteed by
MVCC.

Sure, one database in a cluster may not be consistent with another
database, but generally, seperate databases are treated / considered to be
independent.