Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
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. –  Derek Downey Feb 23 '12 at 16:39

2 Answers 2

up vote 10 down vote accepted

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.

share|improve this answer
1  
I literally LOL'd when I read "you can trust your data in PostgreSQL" :) –  François Beausoleil Feb 23 '12 at 14:36
    
A link to the manual would be nice –  Pablo Fernandez Jul 22 at 0:09
1  
@PabloFernandez: Done! –  Frank Heikens Jul 22 at 4:29

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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