Check your Postgres database catalog integrity effortlessly in seconds

Regular database backups is something no one can afford missing. If your database is inconsistent backups won't be produced successfully. A quick search on pg_dump schema with oid does not exist reveals a lot are suffering and cannot produce any backups.

Going through the search results, people suggest manually scanning the system catalog tables to identify inconsistencies. This is a tedious process and requires a lot of knowledge about the catalog structure.

Fortunately, there is an open-source tool called pg_catcheck, which can detect these inconsistencies. However, it requires you to install Postgres from source and build the tool against its source tree, which may be tricky on some systems. In this blog, I will introduce a cloud-based solution to effortlessly run pg_catcheck, without any compilation, at no extra cost.

Cloud pg_catcheck is a heroku customized fork of the original repo. There are 2 main advantages to use it over the local one:

1. No need to download, compile postgres or pg_catcheck from source.
2. For postgres servers running on heroku (or any AWS-based servers), super fast running time because it runs on the same infrastructure of the database server, reducing the time to do the check from minutes to several seconds.

Fortunately, heroku gives free 750 dyno hours per month, so you will always do the checks for free, because there are no web dynos running.

How to use:

To check your own database, just clone this repo, deploy it to heroku, set database url and finally run the check:

 # setup: do this only once
git clone https://github.com/hammady/pg_catcheck.git
cd pg_catcheck
heroku create --buildpack https://github.com/ddollar/heroku-buildpack-multi.git
git push heroku master

# set the database url and run the check
heroku config:set DATABASE_URL=postgres://username:password@host:port/databasename
heroku run ./check

Before deploying, you need to create an account on heroku, if you don't already have one.