Database consistency checker in postgresql

There is a project called pgCheck on pgFoundry. Note that the development status is 'Alpha' however.

I looks like the last activity was in early 2012.

It's been suggested elsewhere that

most people use a combination of either database wide vacuums, or doing select * from each table; ie. try to scan / process every row somehow

There is no built-in consistency check command or tool in PostgreSQL.

The general view is that one should not be necessary, as corruption and inconsistency should not be possible on a quality hardware/software stack. If problems do arise, there's no guarantee any kind of consistency check would find them, so it'd just create a false sense of security. I don't agree with that sentiment, but it's what seems to come out when this is periodically discussed on pgsql-hackers.

As usual, the underlying problem is that nobody particularly needs a consistency checker tool to meet their immediate needs, so nobody's spending the time to write one to scratch an itch and nobody's funding the development of one on a commercial contract or in-house basis. Volunteering? :p

PostgreSQL (until 9.3) didn't support block level checksums. So one of the main things that you're used to verifying did not exist and could thus not be verified. A tool to scan all relations and validate checksums does not exist in PostgreSQL 9.3, but would be desirable to add and may appear in a future version. In the mean time all you can do is SELECT * from each relation individually - but due to the fact that PostgreSQL uses operating system buffer cache for reads there's no gurantee that'll actually force a read of the underlying disk block anyway. A new tool would be needed to do this.

PostgreSQL tends to avoid redundantly storing information where possible, so there's often nothing to check against, just a single authorative source. A consistency checker can't do much unless the same information appears, or can be derived from, multiple different places.

It's also very hard to do any kind of useful checking concurrently, on a database that's still busy and active. Most installations aren't going to be willing to lock the entire database, or at least several major relations at a time, to run some kind of consistency check. So the checker would need to be able to operate on a database that's subject to concurrent modifications, making it even harder to write and able to detect fewer problems reliably.

There's still plenty that a validator tool could do if one were written, especially if it was allowed to take multiple relation exclusive locks:

• Check that all tablespaces exist on disk.

• Check that each pg_class entry has file(s) corresponding to its relfilenode in the correct tablespace.

• Inspect visibility maps, free-space maps, etc, ensuring they're present when they should be, readable, and appear to match the relation they're associated with.

• Report orphan on-disk file nodes. (These are normal due to transactional DDL and lazy unlinking, but a checker could force eager unlinking and lock all relations before running the check).

• Read every block of each relation and look for obvious problems. For heap relations that'd be things like:

  • an xmin greater than xmax (after considering xid wrap-around)
  • Tuples created by transactions in the future
  • broken HOT chains / broken ctid chains
  • tuple structures that fail to match the table attributes
  • any Datum that doesn't round-trip its _in and _out functions unchanged or throws an error
  • NULL bitmap fields set on NOT NULL table attributes
  • Re-execution of CHECK constraints fails

• Re-check foreign key and exclusion constraints after locking all tables involved

... and probably lots more I don't know enough about Pg's guts to figure out, such as attempts to detect torn pages, b-tree structure validation, sanity checking GIN and GiST indexes, sanity checking pg_control, and more I wouldn't know where to even start with.

If you're keen to have such a tool, the best thing to do is learn enough to come up with a concrete proposal about how it should work - and make the time to work on it, or to fund others to spend time on its development.

Personally I'd be really quite happy to have something that could check a stopped database cluster using a special startup mode for the postgres backend, so I could (somewhat) validate physical database copies taken with pg_basebackup, with pg_start_backup(), rsync and pg_stop_backup, with file-system level atomic snapshots, etc.

Alternately, you could do what most others do: Ensure your hardware and software stack is robust and properly configured, keep good backups, and monitor your logs. There's no substitute for proper testing of the whole stack before commissioning a server - and for good backups, both physical (streaming / PITR) and logical (dumps). Do plug-pull testing on a loaded database - repeatedly - before going live to make sure your supposedly reliable I/O subsystem really is. Use multiple forms of backup.