PostgreSQL Snapshot Backup and Restore with Proxmox ZFS (4/4)

In the blog series I previously wrote, I did not answer all the customer’s questions. The last one was the following:

Can this also be applied to PostgreSQL?

In short, yes, it is possible. Let’s see how.

Here is the list of the previous blog posts:

• https://www.dbi-services.com/blog/sql-server-snapshot-backup-and-restore-with-proxmox-zfs/
• https://www.dbi-services.com/blog/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3/
• https://www.dbi-services.com/blog/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-rest-api-with-sql-server-2025-3-3/

Partitioning and filesystem

We will reuse the sqlpool ZFS pool created in the first part of this series.

We identify the 300 GB disk attached to the VM. In our case, it is /dev/sdb, backed by the sqlpool/pve/vm-307-disk-0 zvol on the Proxmox side:

lsblk

We create a single partition of type Linux filesystem:

sudo sgdisk -n 1:0:0 -t 1:8300 /dev/sdb

We format the partition with XFS, which is the most commonly recommended filesystem for PostgreSQL data directories:

sudo mkfs.xfs -L pgdata /dev/sdb1 -f

We verify the result:

sudo blkid /dev/sdb1

Mountpoint:

We create the mount point:

sudo mkdir -p /pgdata

Persistent mount via fstab:

We add the mount entry to /etc/fstab using the filesystem label rather than the device name. The device name (/dev/sdb) may change if disks are added or removed while the label remains stable:

echo 'LABEL=pgdata /pgdata xfs noatime,nodiratime 0 2' | sudo tee -a /etc/fstab
sudo systemctl daemon-reload
sudo mount /pgdata

We verify that the volume is mounted:

df -h /pgdata

PostgreSQL installation

We install PostgreSQL 18 from the official PGDG repository, which provides the latest PostgreSQL versions for Ubuntu:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo apt install -y postgresql-18

Cluster creation on /pgdata:

The Ubuntu packages create a default cluster under /var/lib/postgresql. This is not what we want. The data files and the WAL must both reside on the ZFS-backed volume, so that a single ZFS snapshot captures a consistent state of the database. If they were on different volumes, the snapshot would not be atomic.

We drop the default cluster and recreate it on /pgdata:

sudo pg_dropcluster --stop 18 main
sudo install -d -o postgres -g postgres -m 700 /pgdata/18
sudo pg_createcluster -d /pgdata/18/main 18 main
sudo systemctl enable --now postgresql@18-main

We verify that the cluster is online and located on the right volume:

pg_lsclusters
sudo -u postgres psql -c "SHOW data_directory;"
sudo -u postgres psql -c "SELECT version();"

We can also confirm that the WAL directory lives inside the data directory, and therefore on the zvol:

ls -ld /pgdata/18/main/pg_wal

Creating a large database

We need a database large enough to make traditional backup and restore operations time-consuming. In the SQL Server part of this series, we used the StackOverflow database (about 207 GB). For PostgreSQL, we use pgbench, the benchmarking tool shipped with PostgreSQL.

We create the database and initialize it with a scale factor of 10000. This produces a database of approximately 146 GB, with 1 billion rows in the pgbench_accounts table:

sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 10000 --partitions=8 bench

A few minutes later:

We can monitor the data growth during the initialization:

watch -n 30 'df -h /pgdata'

A few minutes later:

On the Proxmox side:

After some time, the process completes:

We check the database size:

We run a checkpoint before taking the snapshot. The recovery process starts replaying the WAL from the last checkpoint. By running it right before the snapshot, almost nothing needs to be replayed when the database starts after a restore:

sudo -u postgres psql -c "CHECKPOINT;"

Comparison with SQL Server:

On the SQL Server side, we had to run SUSPEND_FOR_SNAPSHOT_BACKUP and BACKUP WITH METADATA_ONLY. On the PostgreSQL side, none of that is needed.

The data files and the WAL are on the same zvol. An atomic ZFS snapshot therefore captures a state equivalent to a power loss, and PostgreSQL is designed to recover cleanly from that state through crash recovery: the WAL is replayed from the last checkpoint. This is documented and officially supported.

The snapshot is the backup. There is no .bkm file, no metadata backup.

Snapshot process flow

On the Proxmox side, we create the snapshot and protect it with a hold:

SNAP="sqlpool/pve/vm-307-disk-0@pg_bench_$(date +%Y%m%dT%H%M%S)" 
zfs snapshot "$SNAP" 
zfs hold sqlsnap "$SNAP" 
echo "$SNAP"

The hold protects the snapshot from an accidental destruction, as we did in part 2 of this series. We note the exact snapshot name, it will be needed for the restore.

The database stays online during the whole operation. No I/O freeze is required.

We list the snapshots:

zfs list -t snapshot -r sqlpool/pve/vm-307-disk-0

We drop the database then we restore the snapshot:

We run the snapshot restore procedure. On the VM, we stop the cluster and unmount the volume:

sudo systemctl stop postgresql@18-main
sudo umount /pgdata

On the Proxmox side, we want to restore our snapshot. We can list the available snapshots:

zfs list -t snapshot -r sqlpool/pve/vm-307-disk-0

We roll back the snapshot:

zfs rollback -r sqlpool/pve/vm-307-disk-0@pg_bench_20260803T165409

On the VM, we mount the volume and start the service:

sudo mount /pgdata
sudo systemctl start postgresql@18-main

We check a few elements in the logs:

sudo tail -30 /var/log/postgresql/postgresql-18-main.log

The service shutdown, then the restart:

PostgreSQL detects that the database was not shut down properly and replays the WAL. This is the same crash recovery mechanism as in SQL Server. Finally, the database starts.

We then verify that the database is available again:

sudo -u postgres psql -d bench -c "SELECT pg_size_pretty(pg_database_size('bench'));"

Consistency proof under load

The previous test was done on a quiesced database: we ran a CHECKPOINT right before the snapshot, and nothing was writing. The real question is: what happens if the snapshot is taken while the database is being written to?

This is where PostgreSQL differs the most from SQL Server. There is no SUSPEND_FOR_SNAPSHOT_BACKUP. We take the snapshot in the middle of the write activity and we let the WAL replay do the work.

We start the load. The built-in pgbench script runs a TPC-B-like transaction: three UPDATE statements on the accounts, tellers and branches tables and one INSERT into the history table:

sudo -u postgres pgbench -c 8 -j 4 -T 300 bench &

While the load is running, we take a snapshot on the Proxmox side:

zfs snapshot sqlpool/pve/vm-307-disk-0@pg_bench_$(date +%Y%m%dT%H%M%S)

zfs hold sqlsnap sqlpool/pve/vm-307-disk-0@pg_bench_20260803T224551

No CHECKPOINT this time, no freeze. The database is actively writing while the snapshot is taken.

The state after some time under load:

We stop the service:

sudo systemctl stop postgresql@18-main 
sudo umount /pgdata

We restore the snapshot:

zfs rollback -r sqlpool/pve/vm-307-disk-0@pg_bench_20260803T224551 

We mount the volume, start the service and check the logs:

sudo mount /pgdata 
sudo systemctl start postgresql@18-main

This time the log shows a real recovery:

Three differences compared to the first test:

• The “last known up at” timestamp (20:44:36) does not match a checkpoint we ran manually. It matches the last automatic checkpoint triggered during the load.

• The redo is not instantaneous anymore: 3.63 seconds, replaying about 280 MB of WAL (from LSN 20/7A3618B0 to 20/8BC0A4D0). All the write activity between the last checkpoint and the snapshot had to be replayed. The transactions committed before the snapshot are recovered, the ones that were in flight are rolled back.

• The end-of-recovery checkpoint then writes everything the redo rebuilt in memory: 105355 buffers, 53.6% of the buffer pool. The database is ready to accept connections about 19 seconds after the service start.

The crash recovery completed correctly and the database has been restored. We verify the TPC-B invariant. Each pgbench transaction applies the same delta to the accounts, tellers and branches tables in a single transaction. On a consistent database, the three sums must be equal:

Major drawbacks

• The snapshot covers the whole zvol. All the databases of the cluster are captured and restored together. There is no per-database restore, unlike the METADATA_ONLY approach on SQL Server which targets a single database.
• There is no backup history. SQL Server records the metadata backup in msdb. Here, the only trace is the snapshot itself on the ZFS side.
• Point-in-time recovery is not covered. The snapshot alone brings the database back to the moment it was taken. For PITR, WAL archiving would still be required on top of it.

Conclusion

• The snapshot backup and restore model of the SQL Server series applies to PostgreSQL (no I/O freeze, no metadata backup).
• One important rule: data files and WAL must reside on the same zvol so the snapshot is atomic.
• A 146 GB database was restored in a few seconds and in less than 20 seconds under active load, WAL replay included.

Thank you. Amine Haloui