Thread: pgdump problem or question?

On Wed, Sep 10, 2008 at 3:31 PM, Bayless Kirtley <bkirt@cox.net> wrote:
> Early Friday morning a bad record caused me to reload the Thursday night
> pgdump backup. I performed a pgdump first to study later. In the backup I
> found several incomplete transactions all done at the end of the day.
> Investigating later I found the original bad record from the Friday dump and
> fixed it then found that those incomplete transactions were complete in that
> version.
>
> The client shuts the system down nightly by first closing the application
> then performing a pgdump on the database before shutting it down then shuts
> down the computer (Windows XP PRO, PostgreSQL 8.3.3). The pgdump would have
> taken place at least a couple of minutes after the application shutdown.
>
> I thought pgdump was suitable for a live backup of the database. This would
> seem to be wrong if it dumps partial transactions. Do I understand
> correctly? If so, is there something else I need to do before the dump? Any
> help will be greatly appreciated. This does shake my confidence in my backup
> procedures.

If the transaction was like this:

begin;
insert...
update..
delete...
commit;

then pg_dump will NOT get part of that transaction, it will either get
it all or none of it.

In fact, there's no need to turn off the application to get a coherent
backup as long as the transactions are in fact REALLY transactions
like above.