1

Is there a efficient way to check if the Postgres backups (for managed services like RDS and Cloudsql) are not corrupted?

Currently one of the ways we are implementing this check is in the following way:

  • spin up a new DB from the backup and
  • run a vacuum
  • if the vacuum runs successful we are considering the backup is not corrupted
  • delete the test DB

Does this sound like a valid approach and any better solution to this?

PS: GCP has this open source tool to test on their end, but did not understand how we can implement or use it on a managed PG instance. https://github.com/google/pg_page_verification

Thanks in advance!

Dev Babai
  • 53
  • 1
  • 7
  • I think you are doing the right thing. Another option would be to restore the backup and run a `pg_dump -f /dev/null` on the resulting database. But that would not check for index corruption. – Laurenz Albe Apr 19 '22 at 06:26

2 Answers2

1

In Google Cloud SQL the PostgreSQL Page Verification tool runs automatically[1] on your backups, but if it makes you feel more comfortable then you can run it yourself.

[1] https://cloud.google.com/blog/products/gcp/verifying-postgresql-backups-made-easier-new-open-source-tool

GiiaG
  • 71
  • 3
0

Regarding Amazon RDS, there is no need to check corruption.

The RDS service is managed and theoretically guarantees a snapshot is valid when you create the snapshot.

If you would like to manually check the integrity, the process you've outlined is a sure-fire way at a high level.

At a low level, pg_checksums is also a great way to check integrity (which the pg_page_verification tool you mention uses).

Disclaimer: I am aware of situations where RDS snapshots have become corrupted in very, very rare cases over the years but they have been extremely rare & ultimately, compensation has been offered due to a breach of the SLA.

This should, however, not be a worry 99.95% of the time.

Ermiya Eskandary
  • 15,323
  • 3
  • 31
  • 44