Re: Backup routine

On Monday, August 11, 2003 17:26, dalgoda@ix.netcom.com
[mailto:dalgoda@ix.netcom.com] wrote:
>From: dalgoda@ix.netcom.com [mailto:dalgoda@ix.netcom.com]
>Sent: Monday, August 11, 2003 17:26
>To: pgsql-admin@postgresql.org
>Subject: Re: [ADMIN] Backup routine
>
>
>In article <200308110313.h7B3DCv06482@candle.pha.pa.us>,
>Bruce Momjian  <pgman@candle.pha.pa.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.

Cheers,
    Murthy