Re: Backup routine - Mailing list pgsql-admin

On Wednesday, August 13, 2003 18:50, scott.marlowe
[mailto:scott.marlowe@ihs.com] wrote:

>On Wed, 13 Aug 2003, Murthy Kambhampaty wrote:
>> On Monday, August 11, 2003 17:26, dalgoda@ix.netcom.com
...
>> >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.
>
What I had in mind was:

1. If you stop the server and run either (a.) pg_dumpall or (b.) copy of
$PGDATA from a snapshot created with a frozen filesystem (the one containing
$PGDATA), then you get a consistent backup with a known state.

2. If you leave the server running and either (a.) pg_dumpall or (b.) copy
of $PGDATA from a snapshot created with a frozen filesystem (the one
containing $PGDATA), then you get a consistent backup with an unknown state.

Additionally, with pg_dump you can, unlike with "the consistent
filesystem-level backup strategy", independently "quisce" each database
while other databases continue normal operations. This gives you the
"flexibility" to get pg_dumps with known state for each individual database,
without taking down ALL databases at one time (by shutting down the server).
This does not seem very valuable.

Sorry for any confusion I created,
    Murthy