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.

In Derby (an embedded database written in Java which is mostly used for testing or prototyping) there are "freeze" and "unfreeze" commands which can be used during an online backup. "Freeze" simply causes all database accesses to block until "unfreeze" is called. This is useful for backing up using an external program, which you might do if the external program is much faster than using Derby's internal backup solution. For my use case, I can take a snapshot almost instantaneously using some built-in filesystem utilities, so it is a constant-time operation (not O(length of DB files)).

I'm migrating an application which has outgrown Derby to PostgreSQL, and I was wondering if there's anything comparable there which I can use to quiesce all connections. Also, I'd prefer to know what my serialization point is from inside my application so that I don't get caught in some awkward state, so being able to pause/resume all other accesses is really nice-to-have for me.

Since PostgreSQL has a transaction log, I could just take a snapshot without "freeze"ing, but the snapshot would need to be run through PostgreSQL's recovery mechanism before I can use it because otherwise what's stored on disk would be the same as if I pulled the plug on a normal filesystem. This solution is not ideal.

EDIT I learned that pg_start_backup() is close, but it doesn't cause incoming transactions to block until a matching call to pg_stop_backup(), forcing me to do a point-in-time-recovery back to the transaction id pg_start_backup() returns from a filesystem snapshot. It would be nice not to have to actually shutdown PostgreSQL to get this (perhaps there is a pseudo-shutdown command that keeps connections open?).

share|improve this question

migrated from stackoverflow.com Jun 11 '13 at 1:21

This question came from our site for professional and enthusiast programmers.

    
I think you are looking for pg_start_backup(): postgresql.org/docs/current/static/… But you should read this: postgresql.org/docs/current/static/backup-file.html to understand why you can't just take a filesystem backup while the server is running –  a_horse_with_no_name Jun 10 '13 at 23:24
    
I would suggest to migrate this question to dba.SE. –  Erwin Brandstetter Jun 10 '13 at 23:25
    
@a_horse_with_no_name pg_start_backup() is close. Is there any way to actually block other writes so the snapshot is guaranteed to be consistent though (other than shutting down the database before snapshotting)? Otherwise it seems like I'll still have to do a point-in-time-recovery to the transaction for pg_start_backup() to avoid recovering transactions that occur between the pg_start_backup() and the filesystem snapshot. –  gerty3000 Jun 10 '13 at 23:45
    
@Dan: Hm, I can just log in to other stackexchange sites with the same account using the stackexchange accout. –  Erwin Brandstetter Jun 10 '13 at 23:51
    
@Dan: Just flag it asking for the move, moderators will migrate it for you. –  Erwin Brandstetter Jun 11 '13 at 0:45

1 Answer 1

up vote 6 down vote accepted

There's no exact equivalent to what you want. Options include:

Atomic file-system snapshot

If you're using an atomic file system snapshot you don't need to freeze the database. It might make recovery a little faster if you force a CHECKPOINT first, but that's about it. When you take a filesystem snapshot and copy it, then start the copy, to PostgreSQL it's as if it crashed and is starting back up. That's perfectly fine, it's crash safe and designed to work that way.

This approach is only valid if the snapshot is atomic - it's all at the same virtual instant. You can't get an atomic snapshot across multiple file systems (on Linux, at least), so if your DB is split across multiple tablespaces or WAL is on a separate disk to the heap you cannot use this approach.

pg_start_backup / pg_stop_backup

If you can't do an atomic snapshot you can enable WAL archiving, run pg_start_backup, copy the DB, run pg_stop_backup and capture the last WAL archives generated.

It's a bit more complicated, but it gives you a consistent backup without stopping writes and without needing file-system level atomic snapshots.

pg_basebackup

An alternative to using pg_start_backup and pg_stop_backup is to use pg_basebackup to do a streaming copy of the DB over the PostgreSQL replication protocol with --xlog-method=stream. This requires only a PostgreSQL replication connection, doesn't require the DB to be stopped, and is pretty seamless.

--xlog-method=stream was only added in pretty recent versions, and pg_basebackup its self is fairly new.

pg_dump

I didn't initially mention it because you were looking for external tools, but there's always pg_dump, which gets a SERIALIZABLE snapshot of the database and dumps it. The DB keeps running like normal (it can still accept writes) and the dump is entirely internally consistent from the time you started the dump.

Write quiescence

Stopping all incoming transactions won't stop PostgreSQL writing. It'll still have VACUUM work to do with autovacuum, checkpoints to perform, stats to write, etc.

There's no feature in Pg to stop all writes at this point. It might be nice to add, but I'm not aware of anyone working on it.

Some file systems, like XFS, support write freezing at the file system level; this causes all writes to block until the freeze is released. It's safe to freeze all file systems then copy all file systems.

share|improve this answer
    
Thanks, that clears some things up. I have one more requirement I forgot to mention - I'm trying to stop the writes because I'm using this functionality to make a snapshot which is consistent with that of another data source. If I can't block writes, it's possible the two data sources could be inconsistent in the snapshot. –  gerty3000 Jun 11 '13 at 2:51
    
@Dan: you can take a consistent snapshot using pg_dump without the need to block writes. –  a_horse_with_no_name Jun 11 '13 at 6:15
    
@a_horse_with_no_name I took it as a given that pg_dump wasn't an option from "This is useful for backing up using an external program, which you might do if the external program is much faster than using Derby's internal backup solution" with the author wanting the same for Pg. Should mention nonetheless. –  Craig Ringer Jun 11 '13 at 6:19
    
@a_horse_with_no_name CraigRinger understood me correctly on this one. –  gerty3000 Jun 11 '13 at 7:19
    
As a final note, a PostgreSQL expert in my company explained that shutting down the instance is the only way to get exactly what I wanted. –  gerty3000 Jun 11 '13 at 22:15

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.