What happens when we call pg_dump while users are still using the system? We have a web application with PostgreSQL as our database and use celery background tasks for somewhat complex task. At the very beginning of this complex task we take a backup of the database using pg_dump.

I was wondering about the state of this sql dump if the users are still using the system. Could it cause any inconsistencies ?

Also, I would like to know if pg_dump is the best choice for taking backups in this case. Of course as the database grows the backup takes a lot of time which slows the overall process.

Thanks.
I was wondering about the state of this sql dump if the users are still using the system


pg_dump relies on PostgreSQL's MVCC support to ask PostgreSQL to take a snapshot of the database state at a given point in time.

Changes made by concurrent transactions after the point where pg_dump gets its snapshot do not affect pg_dump, whether they're committed or not.

There are a few things that aren't necessarily consistent, like sequences, which continue to increase while pg_dump is running. But applications have to be prepared for gaps in sequences anyway, so this isn't considered to be a problem.


  Also, I would like to know if pg_dump is the best choice for taking backups in this case


No, you're way better off using WAL archiving and PITR for your purpose. Look into PgBarman and read the docs on continuous archiving.

You should still take logical dumps for disaster recovery, but it need not be an all-the-time thing.