Thread: pgdump problem or question?

pgdump problem or question?

From
"Bayless Kirtley"
Date:
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.
 
Thanks,
Bayless
 

Re: pgdump problem or question?

From
"Scott Marlowe"
Date:
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.