Translating postgres data directory file names on disk to relation names

PostgreSQL stores tables, indexes and other relations on disk with a very specific file naming scheme. Translation is required when you want to identify which logical relation at the SQL level corresponds to a given on disk file. You cannot simply use the relation oid.

WARNING: Looking up a relfilenode id in pg_class as a relation oid is wrong, but will often appear to work anyway, and rather misleadingly tends to produce correct results especially in simple test databases. Pg will create a relation with the same relfilenode as its oid if the relfilenode id is not already in use. Also, the relfilenode id changes when a table is rewritten by VACUUM FULL, an index by REINDEX, etc, but the oid does not.

Handling all the special cases gets complex and makes for a tedious procedure, so the supporttools repository has extensions/relfilenode_lookup to automate the process. It's a SQL-only extension that handles a variety of corner cases and reports extra information like the base table associated with an index or toast table mentioned in an error.

If you can't use the extension, use pg_filenode_relation to map from a filenode back to a relation, and use pg_relation_filenode to get the on disk filename for a relation. See postgres admin functions. To understand postgres's file naming and storage see database file layout.

Given a path (say, from an error message), which should look like one of these:

• base/16400/140921237
• pg_tblspc/176967555/PG_9.1_201105231/123304298/135285149
• global/1234

you will need to extract the parts of the path that identify the tablespace if any, database oid if any, and relfilenode.

The above paths match the following patterns respectively:

• base/ dboid / relfilenode - default tablespace
• pg_tblspc/ tablespaceoid /PG_ pgmajor _ catversion / dboid / relfilenode - non-default tablespace
• global/ relfilenode - global tablespace

When finding the relfilenode you can ignore any fork identifier (_fsm, _vm, etc) suffix on the file name, and you can ignore any .1 etc extent number. The fork identifiers are very important for understanding what the error means, but not for finding which relation it affects.

So all these are the relfilenode 140921237:

• base/16400/140921237
• base/16400/140921237.4
• base/16400/140921237_fsm
• base/16400/140921237_vm

There are various corner cases around mapped relations, global relations, and more. See blog for details.