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?).