In short, yes, it is possible. Let’s see how.<\/p>\n\n\n\n
Here is the list of the previous blog posts:<\/p>\n\n\n\n
- \n
- https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs\/<\/a><\/li>\n\n\n\n
- https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/<\/a><\/li>\n\n\n\n
- https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-rest-api-with-sql-server-2025-3-3\/<\/a><\/li>\n<\/ul>\n\n\n\n
Partitioning and filesystem<\/h2>\n\n\n\n
We will reuse the sqlpool ZFS pool created in the first part of this series.<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-1-1024x134.png\")
<\/figure>\n\n\n\nWe 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:<\/p>\n\n\n\n
lsblk<\/code><\/pre>\n\n\n\nWe create a single partition of type Linux filesystem:<\/p>\n\n\n\n
sudo sgdisk -n 1:0:0 -t 1:8300 \/dev\/sdb<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-2.png\")
<\/figure>\n\n\n\nWe format the partition with XFS, which is the most commonly recommended filesystem for PostgreSQL data directories:<\/p>\n\n\n\n
sudo mkfs.xfs -L pgdata \/dev\/sdb1 -f<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-3.png\")
<\/figure>\n\n\n\nWe verify the result:<\/p>\n\n\n\n
sudo blkid \/dev\/sdb1<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-4-1024x32.png\")
<\/figure>\n\n\n\n<\/h3>\n\n\n\n
Mountpoint:<\/strong><\/p>\n\n\n\n
We create the mount point:<\/p>\n\n\n\n
sudo mkdir -p \/pgdata<\/code><\/pre>\n\n\n\n<\/h3>\n\n\n\n
Persistent mount via fstab<\/strong>:<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
echo 'LABEL=pgdata \/pgdata xfs noatime,nodiratime 0 2' | sudo tee -a \/etc\/fstab\nsudo systemctl daemon-reload\nsudo mount \/pgdata<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-5.png\")
<\/figure>\n\n\n\nWe verify that the volume is mounted:<\/p>\n\n\n\n
df -h \/pgdata<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-6.png\")
<\/figure>\n\n\n\nPostgreSQL installation<\/h2>\n\n\n\n
We install PostgreSQL 18 from the official PGDG repository, which provides the latest PostgreSQL versions for Ubuntu:<\/p>\n\n\n\n
sudo apt install -y postgresql-common\nsudo \/usr\/share\/postgresql-common\/pgdg\/apt.postgresql.org.sh -y\nsudo apt install -y postgresql-18<\/code><\/pre>\n\n\n\nCluster creation on \/pgdata<\/strong>:<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
We drop the default cluster and recreate it on \/pgdata:<\/p>\n\n\n\n
sudo pg_dropcluster --stop 18 main\nsudo install -d -o postgres -g postgres -m 700 \/pgdata\/18\nsudo pg_createcluster -d \/pgdata\/18\/main 18 main\nsudo systemctl enable --now postgresql@18-main<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-7-1024x433.png\")
<\/figure>\n\n\n\nWe verify that the cluster is online and located on the right volume:<\/p>\n\n\n\n
pg_lsclusters\nsudo -u postgres psql -c \"SHOW data_directory;\"\nsudo -u postgres psql -c \"SELECT version();\"<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-8-1024x224.png\")
<\/figure>\n\n\n\nWe can also confirm that the WAL directory lives inside the data directory, and therefore on the zvol:<\/p>\n\n\n\n
ls -ld \/pgdata\/18\/main\/pg_wal<\/code><\/pre>\n\n\n\nCreating a large database<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
sudo -u postgres createdb bench\nsudo -u postgres pgbench -i -s 10000 --partitions=8 bench<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-9.png\")
<\/figure>\n\n\n\nA few minutes later:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-10.png\")
<\/figure>\n\n\n\nWe can monitor the data growth during the initialization:<\/p>\n\n\n\n
watch -n 30 'df -h \/pgdata'<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-11.png\")
<\/figure>\n\n\n\nA few minutes later:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-12.png\")
<\/figure>\n\n\n\nOn the Proxmox side:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-13-1024x292.png\")
<\/figure>\n\n\n\nAfter some time, the process completes:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-14-1024x105.png\")
<\/figure>\n\n\n\nWe check the database size:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-15.png\")
<\/figure>\n\n\n\nWe 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:<\/p>\n\n\n\n
sudo -u postgres psql -c \"CHECKPOINT;\"<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-16.png\")
<\/figure>\n\n\n\nComparison with SQL Server<\/strong>:<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The snapshot is the backup. There is no .bkm file, no metadata backup.<\/p>\n\n\n\n
<\/td> SQL Server<\/strong><\/td> PostgreSQL<\/strong><\/td><\/tr> Before the snapshot<\/strong><\/td> ALTER DATABASE…SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON<\/td> CHECKPOINT (optional)<\/td><\/tr> Backup record<\/strong><\/td> BACKUP WITH METADATA_ONLY<\/td> None<\/td><\/tr> During the restore<\/strong><\/td> RESTORE WITH METADATA_ONLY<\/td> Automatic crash recovery (WAL replay)<\/td><\/tr> Evidence in the logs<\/strong><\/td> “I\/O is frozen” in the ERRORLOG<\/td> “redo starts\/redo done” in the PostgreSQL log<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Snapshot process flow<\/h2>\n\n\n\n
On the Proxmox side, we create the snapshot and protect it with a hold:<\/p>\n\n\n\n
SNAP=\"sqlpool\/pve\/vm-307-disk-0@pg_bench_$(date +%Y%m%dT%H%M%S)\" \nzfs snapshot \"$SNAP\" \nzfs hold sqlsnap \"$SNAP\" \necho \"$SNAP\"<\/code><\/pre>\n\n\n\nThe 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.<\/p>\n\n\n\n
The database stays online during the whole operation. No I\/O freeze is required.<\/p>\n\n\n\n
We list the snapshots:<\/p>\n\n\n\n
zfs list -t snapshot -r sqlpool\/pve\/vm-307-disk-0<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-17.png\")
<\/figure>\n\n\n\nWe drop the database then we restore the snapshot:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-18-1024x272.png\")
<\/figure>\n\n\n\nWe run the snapshot restore procedure. On the VM, we stop the cluster and unmount the volume:<\/p>\n\n\n\n
sudo systemctl stop postgresql@18-main\nsudo umount \/pgdata\n<\/code><\/pre>\n\n\n\nOn the Proxmox side, we want to restore our snapshot. We can list the available snapshots:<\/p>\n\n\n\n
zfs list -t snapshot -r sqlpool\/pve\/vm-307-disk-0<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-19.png\")
<\/figure>\n\n\n\nWe roll back the snapshot:<\/p>\n\n\n\n
zfs rollback -r sqlpool\/pve\/vm-307-disk-0@pg_bench_20260803T165409<\/code><\/pre>\n\n\n\nOn the VM, we mount the volume and start the service:<\/p>\n\n\n\n
sudo mount \/pgdata\nsudo systemctl start postgresql@18-main<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-20.png\")
<\/figure>\n\n\n\nWe check a few elements in the logs:<\/p>\n\n\n\n
sudo tail -30 \/var\/log\/postgresql\/postgresql-18-main.log<\/code><\/pre>\n\n\n\nThe service shutdown, then the restart:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-21-1024x66.png\")
<\/figure>\n\n\n\nPostgreSQL 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.<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-22-1024x90.png\")
<\/figure>\n\n\n\nWe then verify that the database is available again:<\/p>\n\n\n\n
sudo -u postgres psql -d bench -c \"SELECT pg_size_pretty(pg_database_size('bench'));\"<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-23.png\")
<\/figure>\n\n\n\nConsistency proof under load<\/h2>\n\n\n\n
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?<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
sudo -u postgres pgbench -c 8 -j 4 -T 300 bench &<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-24.png\")
<\/figure>\n\n\n\nWhile the load is running, we take a snapshot on the Proxmox side:<\/p>\n\n\n\n
zfs snapshot sqlpool\/pve\/vm-307-disk-0@pg_bench_$(date +%Y%m%dT%H%M%S)\n\nzfs hold sqlsnap sqlpool\/pve\/vm-307-disk-0@pg_bench_20260803T224551<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-25.png\")
<\/figure>\n\n\n\nNo CHECKPOINT this time, no freeze. The database is actively writing while the snapshot is taken.<\/p>\n\n\n\n
The state after some time under load:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-26.png\")
<\/figure>\n\n\n\nWe stop the service:<\/p>\n\n\n\n
sudo systemctl stop postgresql@18-main \nsudo umount \/pgdata<\/code><\/pre>\n\n\n\nWe restore the snapshot:<\/p>\n\n\n\n
zfs rollback -r sqlpool\/pve\/vm-307-disk-0@pg_bench_20260803T224551 <\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-27.png\")
<\/figure>\n\n\n\nWe mount the volume, start the service and check the logs:<\/p>\n\n\n\n
sudo mount \/pgdata \nsudo systemctl start postgresql@18-main<\/code><\/pre>\n\n\n\nThis time the log shows a real recovery:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-28-1024x141.png\")
<\/figure>\n\n\n\nThree differences compared to the first test:<\/p>\n\n\n\n
- \n
- 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.<\/li>\n<\/ul>\n\n\n\n
- \n
- 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.<\/li>\n<\/ul>\n\n\n\n
- https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/<\/a><\/li>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/08\/image-29.png\")
<\/figure>\n\n\n\n