I have a 3GB database that is constantly modified and I need to make backups without stopping the server (Postgres 8.3).

My pg_dump runs for 5 minutes. What if the data is modified during the process? Do I get consistent backups? I don't want to find out when disaster strikes.

Postgres documentation http://www.postgresql.org/docs/8.3/static/app-pgdump.html doesn't say anything about this.

  • 6
    Hi Roddick. I highly recommend testing your restore process before disaster strikes. Set up a different machine and restore using one of your backups to test it out. Commented Feb 23, 2012 at 16:39

4 Answers 4


From the manual:

It makes consistent backups even if the database is being used concurrently.

So yes, you can trust the backup. Of course, it's PostgreSQL, you can trust your data in PostgreSQL.

  • 15
    I literally LOL'd when I read "you can trust your data in PostgreSQL" :) Commented Feb 23, 2012 at 14:36
  • 2
    A link to the manual would be nice Commented Jul 22, 2014 at 0:09
  • 3
    @PabloFernandez: Done! Commented Jul 22, 2014 at 4:29
  • 1
    However a logical backup on a live database will lock your tables, don't miss this information. Maybe you will find some tips : compose.com/articles/…
    – tryp
    Commented Apr 26, 2017 at 12:59
  • 1
    @FrançoisBeausoleil - why LOL on trusting PostgreSQL with your data?
    – Vérace
    Commented Aug 7, 2021 at 10:10


Please read the manual on Transaction Isolation and for example the User List discussion about this.


The backup will only see transactions that were commited before the isolation level was set.

Basically everything after pg_dump sets the transaction isolation level is not part of this dump.

The read/write operations are not affected(locked) during that time.


pg_dump starts a transaction, similarly to how any other long running query will work. The consistency guarantees there come from the MVCC implementation. The dump will always be self-consistent within those rules.

All the fuzzy parts of MVCC are around around things like what order UPDATE transactions become visible to other clients and how the locks are acquired. pg_dump is strict about the ordering and acquires a read lock on the whole database to dump it. For most people, that's what they expect, and the mechanism used never causes any trouble. The main concurrency risk is that clients trying to change the database structure will be blocked while the dump is running. That doesn't impact the quality of the dump though.


First sorry for answering on this old thread. But from my experience I can not confirm the statement that you can trust pg_dump/

I switched some month ago for a project to postgres. And of course I made all backups with pg_dump from the live system as suggested. But after the first backup check I figured out that all dumps having different sizes. After restoring random some of them, I figured out that some tables are missing. So I started to analyze why this is happen and I figured out that if the overlaying application, e.g. app servers, locking some tables and pg_dump is not waiting until they are released. I was needed every time to stop the server for the backup period which is not a good solution.

So I still looking for a fix but as I said I tottally disagree with the statement that you can trust pg_dump.

  • If that happens it would be a serious in pg_dump. What version you're using? My question was 3 years ago, and now I'm on 9.3 and Amazon reds which does live backups apparently using pg_dump. They can't be wrong.
    – Roman
    Commented May 27, 2015 at 17:02
  • You can trust pg_dump to do a serialized backup (see my answer for links/docs and a few details) However during my readings i did read that you should not change the structure of the DB itself, though i was unable to dig deeper on that. As we only do insert/update/delets i cannot comment on table creations. For us i just made sure to not create tables during the pg_dump time. Commented Jul 9, 2015 at 11:53
  • 3
    Your statements are either indicating a serious bug (did you file a bug report?), a serious bug in the way you dump your database or some misunderstanding. Lacking any facts one cannot decide among these. Commented Jul 9, 2015 at 12:02

Your Answer

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

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