3

How can I instruct Postgres to complete all "in-progress" transactions, but to delay starting new ones (without kicking clients off) until I have taken a ZFS or LVM snapshot (which takes less than a second).

e.g.

  1. pause Postgres, waiting for "in-progress" transactions to complete
  2. sync to disk
  3. take ZFS/LVM snapshot
  4. resume Postgres
fadedbee
  • 2,068
  • 5
  • 24
  • 36
  • And why would you need this. Why not just take a snapshot. Yes, the DB state will be dirty, and some of the transactions will be rolled back, but nothing really harmful usually happens. – drookie Oct 10 '16 at 16:30

2 Answers2

1

This answer on dba.stackexchange seems to be what you are after.

Comments do not seem to automatically link in the side bar (inside the network), so I need to write some more stuff to prevent auto-conversion to a comment... yeah.

Community
  • 1
user121391
  • 2,502
  • 13
  • 31
1

The post mentioned in @user121391 answer is correct, but in short:

  • You cannot pause postgresql in any way (force it to continue open transactions and not process new transactions)
  • If the underlying snapshot is atomic (ZFS snapshots seems to be atomic), It's not necessary to do anything before snapshot, If you run a new postgres process on the snapshot, the database will start in recovery mode (as when the previous process stopped abnormally).
  • pg_start_backup will make recovery process a little faster (since a checkpoint is made, and there will be less pending writes in WAL)
Taha Jahangir
  • 2,122
  • 1
  • 15
  • 16