verifying postgres backups (from Heroku)

I was moving a demo app from Heroku to Amazon recently when I decided at the last minute that I wanted to keep the demo data. I used Heroku’s API to curl a postgres pg_dump to my desktop; directions are found here.

Unfortunately, the pg_dump file format is binary by default, so I couldn’t simply peek inside to verify the backup. I decided to create a “peek” database for this purpose:

> psql 
psql > create database peek;

I then loaded the pg_dump file into this empty database for viewing:

> pg_restore -d peek heroku.bck

Postgres complained a lot during the load, but as you can see, the errors all dealt with the permissions mismatch between Heroku and my desktop. In fact, all of the data loaded correctly - I did not have to prepare a role or schema of any sort. Nice.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1482; 1259 179323 TABLE admins kdhvssheeq
pg_restore: [archiver (db)] could not execute query: ERROR:  role "kdhvssheeq" does not exist
Command was: ALTER TABLE public.admins OWNER TO kdhvssheeq;
-- etc etc etc

If you know of a better way to do this, please let me know. This got the job done in a few minutes, and is easily scriptable if you want to verify backups through grep’ing column contents inside a postgres backup, for example.