Is there a way to check integrity of postgres backup (or a restored database) made with pg_dump?

Question

Hi i have a pretty big database running on Postgres 9.3. I backup it up using pg_dump with compression. I am worried that these backups may be corrupted or that i won't be able to restore it properly (using pg_restore), or that restored database is could be corrupted. The database I backup is in constant use so it's pretty hard (if not impossible) to check if restored database is working correctly by comparing rows (and to be honest i don't think that such test would give a result that's meaningful). Is there any way to check integrity of dump file or a restored database ? I read that postgres 9.3 supports checksumming db files, but i don't see how that would help my case.

Answer 1

Corruption is usually in the form of bad data that won't restore (character set weirdness and the like). I think the best you can do is automatically restore to a test db. If that process succeeds, you are likely ok.