2

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.

Improve this question
4
  • pg_dump takes a consistent snapshot from the database so it will not cause any inconsistencies.
    – user1822
    Apr 22, 2014 at 13:39
  • So I think it's safe to run it in parallel with the task. Are there any optimizations I can do to make the backups faster?
    – thelinuxer
    Apr 22, 2014 at 13:51
  • Short of buying faster harddisks - no. Note that pg_dump creates a "snapshot". Usually that is not considered a backup. Using WAL archiving you can create "real" backups that also allow restoring the data to a specific point in time. See the manual for details: postgresql.org/docs/current/static/continuous-archiving.html
    – user1822
    Apr 22, 2014 at 14:01
  • Why do you take the pg_dump before each complex operation? Is it because you need a backup and this is a convenient time to take one, or is it because you are afraid the operation will go rogue and stomp all over the database? The easiest way to speed up an operation is not to do that operation in the first place, so it is important to know why it is being done.
    – jjanes
    Apr 22, 2014 at 16:52

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • Can you please give me some pointers to projects that provide PITR-like functionality for separate database and/or separate schemas ?
    – thelinuxer
    Apr 24, 2014 at 17:42
  • I'm not aware of any. You can still do cluster-wide WAL archiving for PITR, then do partial restores, though.
    – Craig Ringer
    Apr 25, 2014 at 0:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.