Home / server / Questions / 808065
Accepted
fadedbee
Asked: 2016-10-10 23:47:05 +0800 CST

How to pause Postgres before taking an LVM/ZFS snapshot?

  • 772

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
linux lvm zfs postgresql zfsonlinux

2 Answers

  1. Best Answer

    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.

    • 1

  2. 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)
    • 1