How can we validate a Postgres database backup is not corrupted?

Question

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!

Answer 1

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.

Answer 2

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

Answer 3

To verify RDS snapshot just restore database from it. As far as I know AWS relies on an official tools for PostgreSQL database.

For a database backup verification there is pg_verifybackup tool.

pg_verifybackup is used to check the integrity of a database cluster backup taken using pg_basebackup against a backup_manifest generated by the server at the time of the backup.

A dumped database with pg_dump you could verify like: cat dump.sql | psql.