gem-hunterpg_catcheck is a simple tool for diagnosing system catalog corruption.
If you suspect that your system catalogs are corrupted, this tool may
help you figure out exactly what problems you have and how serious they
are. If you are paranoid, you can run it routinely to search for system
catalog corruption that might otherwise go undetected. However, pg_catcheck
is not a general corruption detector. For that, you should use PostgreSQL's
checksum feature (initdb -k).
PostgreSQL stores the metadata for SQL objects such as tables and functions using special tables called system catalog tables. Users do not normally modify these tables directly, but instead modify them using SQL commands such as CREATE, ALTER, and DROP. If the system catalog tables become corrupted, you may experience errors when attempting to access your data. Sometimes, it can be impossible to back up your data using pg_dump without correcting these errors. pg_catcheck won't tell you how to your database got corrupted in the first place, and it won't tell you how to fix it. But it will usually be able to give you detailed information about what is broken, which may make it easier for you (or your PostgreSQL support provider) to understand what has gone wrong and explain the options for recovery.
pg_catcheck takes the same arguments as most other PostgreSQL utilites,
such as -h for the host or -p for the port. You can also pass it a
connection string or URL, just like psql. For a full list of options,
run pg_catcheck --help. If pg_catcheck isn't already installed, you might
need to build it first. If no pre-compiled binary package is available for
you to install, see the instructions below for "Building on UNIX/Linux" and
"Building on Windows".
When you run pg_catcheck, it will normally print out a line that looks like this:
progress: done (0 inconsistencies, 0 warnings, 0 errors)
If you see that line, it means pg_catcheck didn't find any problems. Otherwise, pg_catcheck will generally print two lines of output for each problem it finds, like this:
notice: pg_class row has invalid relnamespace "24580": no matching entry in pg_namespace
row identity: oid="24581" relname="foo" relkind="r"
notice: pg_type row has invalid typnamespace "24580": no matching entry in pg_namespace
row identity: oid="24583"
notice: pg_type row has invalid typnamespace "24580": no matching entry in pg_namespace
row identity: oid="24582"
notice: pg_depend row has invalid refobjid "24580": no matching entry in pg_namespace
row identity: classid="1259" objid="24581" objsubid="0" refclassid="2615" refobjid="24580" refobjsubid="0" deptype="n"
progress: done (4 inconsistencies, 0 warnings, 0 errors)
If the final output line mentions inconsistencies, that means that it found problems with the logical structure of your system catalogs. Warnings or errors indicate more serious problems, like not being able to read the system catalogs at all. In this particular example, there are four errors: one pg_class row, two pg_type rows, and one pg_depend row all reference an OID 24580 which they expect to find in pg_namespace. In reality, no such row exists.
There are several ways to recover from an error of this type. You could modify the OIDs in the referring rows so that they refer to a pg_namespace entry that does exist. This might enable you to recover access to the underlying data. Note that in this case all four references pertain to the same table (pg_class OID 24581, which has pg_type OIDs 24582 and 24583 for its record and array-of-record types) so you would probably want to make all of those references point to the same namespace. Alternatively, if the dangling references are objects you don't care about (e.g. if the backing file for the pg_class entry doesn't even exist on disk), you could simply delete the referring rows also. This is often enough to make pg_dump run successfully, which is often the main goal.
Unless you are sure you understand what pg_catcheck is telling you, you may wish to consult with a PostgreSQL expert. Changing the system catalogs manually can make a bad situation worse and lead to data loss, and should not be attempted unless you are knowledgeable about how PostgreSQL uses these catalogs.
pg_catcheck also provides an option to run "SELECT * FROM table_name LIMIT 0" on each table in the database, which will detect missing or inaccessible relation files. The --select-from-relations option enables this check.
pg_catcheck was initially developed by EnterpriseDB and is released under the same license as PostgreSQL itself. Patches are welcome. Please subscribe to our mailing list, pg-catcheck@enterprisedb.com, by visiting:
https://groups.google.com/a/enterprisedb.com/forum/#!forum/pg-catcheck
As with any open source project, you may be able to obtain support via the public mailing list, which is pg-catcheck@enterprisedb.com; to subscribe, visit:
https://groups.google.com/a/enterprisedb.com/forum/#!forum/pg-catcheck
If you need commercial support, please contact the EnterpriseDB sales team, or check whether your existing PostgreSQL support provider can also support pg_catcheck.
pg_catcheck should work when run against a server running PostgreSQL 8.4 or higher. It also should also work when run against a server running EnterpriseDB's Advanced Server product, version 8.4 or higher. To compile pg_catcheck, you will need to build against a server source tree version 9.0 or higher, because it relies on the function PQconnectdbParams(), which did not exist in 8.4.