This is on our dev systems. We send ZFS snapshots from production to dev systems, clone the received snapshot, then run a full reindex on PostgreSQL and then take an offline ZFS snapshot of the database. (@fresh-index snapshot) At this point everything works great, and we’ve done this for at least 8 years. We do this so we can do development and revert quickly.

It’s only after we start running our new development db schema upgrades and data migrations and then do the rollback to @fresh-index we get corruption. We get errors when refreshing a materialized view, for example: “ERROR: expected one dependency record for TOAST table, found 0”. We also can’t re-run our conversion script in PG , it will error with info like “already exits”, when it shouldn’t after rollback to an offline snapshot. It’s almost as if something isn’t fully written to disk, or maybe something is stored by Postgres outside the data dir.

What doesn’t work, always, is rolling back to that fresh index point. This is what I’m trying to understand, and would like to work.

What does work, is to re-clone the received snapshot and start over. What also works, is after schema updates, any other further snapshots, rollbacks work, providing they are not before the schema updates.

What also works, is rolling back snapshots before we do schema and data upgrades.

It’s only A) doing big schema/data updates, and B) rollback to the fresh index.

I can even do online snapshots, or offline snapshots, same difference. This happens on 2 dev systems, both same setup on ZFS mirror’s, on enterprise SSD’s.

I’m at a loss as to why Postgres is not able to switch back to a previous state, especially when the service is shutdown.

I’ve tried adding sync=always on the filesystem, same difference. When I take the snapshots I run “sync” and also “zpool sync”, then sleep for up to 30 seconds, doesn’t seem to help.

I’ve added some config options in Postgres to limit dirty buffers and also fsync on WAL writes. But even still, why would any of that matter if I do a shutdown on Postres and take the snapshot offline?

I have Postgres 15.7 currently. On Alma Linux 8. ZFS is, I think, 2.1.15. This is running in a virtual machine (KVM) and the disks are passthrough to the guest with cache=none. This is happening on 2 systems the same way. Postgres data directory is in one place, and all WAL and log directories, one ZFS filesystem.

If there’s any config’s or log info I can provide to help make sense of it, let me know. I feel like this is something with Linux or ZFS, or something incorrectly configured, or just me.

Thanks for any help.