Doing pg_dump while still many active transaction.

Question

Dear fellow developers,

As subjects, what will happen to the backup file meanwhile there is still many active transaction to our database. Does it export realtime or just partially backups ?

thanks in advance for any lights.

Answer 1

pg_dump will give you a consistent state. Any transaction not completed before pg_dump has been issued will not be reflected.

Answer 2

pg_dump runs in a serializable transaction, so it sees a consistent snapshot of the database including system catalogs.

However it is possible to get 'cache lookup failed' error if someone performs DDL changes while a dump is starting. The time window for this sort of thing isn't very large, but it can happen. See: http://archives.postgresql.org/pgsql-bugs/2010-02/msg00187.php