On Wed, Sep 10, 2008 at 3:31 PM, Bayless Kirtley <bkirt@xxxxxxx> 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.
