0

The title says it all. I have found this from 10 years ago saying that if database data it's all in the same snapshot, then isn't needed. PG will start from snapshot like in a typical crash.

But what if there isn't a single snapshot for the whole DB? What if there are some tablespaces in other datasets and WAL is in its own dataset too? This way, snapshots could be offsync a very small period of time. Would this make necessary to pg_start_backup to ensure no data corruption?

I have found this too from 8 years ago of a guy testing exactly this, if PG would start again creating intentional delay between wal & data snapshots, but using vmware VM snapshot technology. So it seems it can work, the question would be, will it work always?

In fact, going an step further, why would be pg_start_backup() needed in any circumstance? Isn't WAL replay capable of fixing the internal inconsistencies of a non instantaneous backup?

Best regards.

0

2 Answers 2

0

(xfer comment to answer)

A lot has changed in 9 years, including the development of pg_basebackup and the removal of exclusive backup mode in PG15. No point setting up a deprecated backup method.

I would set up point-in-time recovery with a WAL archive and a backup-only standby. You get coverage from both pool failure and user mistakes, and most importantly, they are supported solutions with guarantees of recovery, if implemented properly.

1
  • ZFS replication isn't a deprecated backup method. Even with a WAL archive, you need one (probably more) base backups. And ZFS snapshots/replication excels at that.
    – Héctor
    Nov 14, 2022 at 9:27
0

Yes, it is still needed if that is how you want to do your backups and you want them to be robust. If you don't do it, then you could get lucky and it would work, or you could get kind of lucky and it would just obviously blow up when you try to restore it, or you could get unlucky and it would claim to work while leaving your data subtly corrupted.

In fact, going an step further, why would be pg_start_backup() needed in any circumstance? Isn't WAL replay capable of fixing the internal inconsistencies of a non instantaneous backup?

It can if it knows to do it. And that is why pg_start_backup() is needed. Without that, it might start WAL recovery from the wrong spot, because it doesn't know what the right spot is. (Also, if the pg_wal is not located in the last snapshot taken and you aren't using a WAL archive, then it might be missing some of the WAL it needs. But it won't know that, and will just produce silently corrupted data. However, pg_start_backup() doesn't do anything to fix this problem, so that is not really what you question is about...)

6
  • ZFS snapshots are virtually instantaneous. Why would be it needed the backup mode? How it's different to start up a PG server from a ZFS snapshot that from crash or power failure scenario?
    – Héctor
    Nov 14, 2022 at 9:24
  • @Hector But you said it wasn't one snapshot, but more than one of them. That was the whole point of your question.
    – jjanes
    Nov 14, 2022 at 12:14
  • Sorry if my question isn't well redacted enough, but I was asking for both cases. I wanted to confirm that isn't needed if everything is inside a single dataset/snapshot and what happens if they are not. If the probability of inconsistency being in different datasets it's low (looks like it is) then if a set of snapshots fail you only will need to recover from an older one (if not wal archiving), or replay wal from an older snapshot (with wal archiving), and that's all, isn't? So it seems like ZFS snapshots + wal archiving could be a great combo with instantaneous base backups + PITR.
    – Héctor
    Nov 14, 2022 at 13:13
  • With a single snapshot, then it would be fine. With multiple snapshots, I think the problem is only if a checkpoint finishes between the two snapshots, which might be extremely unlikely. Then it might resume WAL replay from that checkpoint's redo point, not from the previous one that it should have used. Unfortunately I think this could result in subtle corruption, so you might not know you need to choose a different backup until it is too late.
    – jjanes
    Nov 14, 2022 at 15:26
  • Would it help to snapshot the wal dataset first or last? Would it help to manually do a checkpoint before snapshoting?
    – Héctor
    Nov 14, 2022 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.