Re: Backup routine

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.

Cheers,
Murthy