How to find out which PostgreSQL table a file on disk corresponds to

Sometimes you need to determine which table a file on disk corresponds to. You have a path full of numbers like base/16499/19401 and you want to make sense of it. You might be looking at an error message that mentions a file name, for example, like:

ERROR:  could not read block 11857 of relation base/16396/3720450: read only 0 of 8192 bytes

Viewing the path of a relation

You can see the path of a table using:

SELECT pg_relation_filepath('tablename');

but what about the reverse, getting the relation name from the path? There’s a function named pg_filenode_relation that looks handy for that … but to use it you already need to be connected to the particular database the file corresponds to, which means you need to know that.

Structure of file paths

Here’s how to find out the database and table a file path refers to on a modern PostgreSQL. (Older versions used a different format, see this blog).

There are three main patterns for paths:

* For files in the default tablespace, base/database_oid/filenode id for the relation
* For files in other tablespaces: pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid / filenode id for the relation
* For shared relations (see below): global/filenode id for the relation

Shared relations are discussed at the end. For the first two, which are the main ones you’ll encounter, the last part is the same, the database oid and the relation oid.

Notice that I say “filenode id for the relation” not “relation oid”. That’s because PostgreSQL has a relfilenode map in a file named pg_relfilenode.map for each database/tablespace. Tables’ file names aren’t necessarily the same as their oids in pg_clas and can can change when VACUUM FULL, TRUNCATE, etc are run. For example:

test=> select pg_relation_filepath('a');
 pg_relation_filepath 
----------------------
 base/16385/101565
(1 row)

test=> VACUUM FULL a;
VACUUM
test=> select pg_relation_filepath('a');
 pg_relation_filepath 
----------------------
 base/16385/101577
(1 row)

So. How do you turn that path back into the relation name?

Database oids and relation filenode ids

Say you get the error given at the start of this post. We can break it down into parts:

• base: in the default tablespace
• 16396: in the database with oid 16396
• 3720450 the filenode id for the table with oid 3720450

then find out what each part is.

Finding a database by oid

First, connect to any database on that PostgreSQL instance and run:

select datname
from pg_database
where oid = 16396

(or whatever your database’s oid is). This will give you the database name.

Then connect to that database.

Reverse mapping relfilenodes on 9.4

If you’re on 9.4 or newer the next part is easy:

SELECT pg_filenode_relation(0, 3720450);

(the 0 means “default tablespace”; see below for info on tablespaces).

That function handles the relfilenode mapping etc for you. So it’ll just show you the table name. It won’t be schema_qualified if it’s on the current search_path; you can SET search_path = ''; first to force it to be qualified.

You must be connected to the correct database or you’ll an incorrect or missing result.

Reverse mapping relfilenodes on 9.3

If you’re on 9.3 or below, connect to the database the table is in and query pg_class with:

select 
    n.nspname AS tableschema, 
    c.relname AS tablename
from pg_class c
inner join pg_namespace n on (c.relnamespace = n.oid)
where c.relfilenode = 3720450;

(or whatever your table’s relfilenode id is).

This will tell you the table the error relates to.

No results?

Well. That usually works.

The relfilenode can also be zero, in which case the file is located via the pg_relfilenode.map. That’s typical for shared catalogs and some system catalogs, their indexes, TOAST tables, etc. pg_database, pg_class and pg_proc for example.

So if your query returns no results, it might be a file managed via the relfilenode map, you might be connected to the wrong DB, or you might have corruption meaning that Pg has no idea what table it is.

How do you tell? That’s a topic for a follow-up post.

What about the schema?

Did you notice that the schema (namespace) doesn’t appear in the path anywhere?

PostgreSQL’s schemas are purely a namespace within the database. They don’t have any effect on where tables are stored on the disk.

Other tablespace paths

A recent case I was looking at was the error:

ERROR: could not truncate file "pg_tblspc / 16709 / PG_9.3_201306121 / 16499/19401" to 8 blocks: Permission denied

This is in a non-default tablespace, since it begins with pg_tblspc.

The process to find the table its self is actually the same. You can ignore the pg_tblspc/nnn/PG_n.n_nnnnnn/ part and just focus on the following database_oid/relation_oid, as described in “Default tablespace paths” above. It’s worth understanding what the path means, though.

So the filename pattern breaks down to:

• pg_tblspc: it’s in a non-default tablespace
• 16709: it’s in the tablespace with oid 16709
• PG_9.3_201306121: used by PostgreSQL 9.3 with catalog version 201306121.
• 16499: in the database with oid 16499
• 19401 the table with relfilenode id 19401

We’ve already discussed the part about the database oid and table relfilenode id. They’re the same with a tablespace, they just start off in a different location.

So what about the tablespace part?

pg_tblspc is a directory inside the PostgreSQL data directory, which contains symbolic links to all the tablespace locations (or on NTFS, junction points for them). Each symlink is named after the tablespace’s oid. This is how PostgreSQL finds tablespaces. The tablespace SQL commands manipulate these links.

The oid corresponds to the pg_tablespace entry for the tablespace, as seen from:

select spcname
from pg_tablespace
where oid = 16709;

Within the tablespace’s directory there’s another directory named for the PostgreSQL version. It’s constant for that version, and its only purpose is to let multiple PostgreSQL instances share a tablespace – say, during a pg_upgrade. Usually there’s only one entry.

Within that the structure is the same as for base/ paths – a database oid, then a relation oid.

Global (shared) tables

There’s a third category of errors, but if you see one you’re probably in trouble. PostgreSQL has shared catalogs – tables that have the same contents in every database. These live in the special tablespace pg_global with relfilenode id 16709.

Their paths begin with global instead of base and they don’t have a database oid component.

Shared catalogs are not listed not be listed by relfilenode in pg_class. So you can’t look up, e.g. pg_database from pg_class. pg_filenode_relation returns null, whether called with the default tablespace oid or with the global tablespace oid 1664.

Finding these is a topic for a followup post dealing with mapped relations.

Of course, if you’re having problems with a shared catalog you probably can’t start the database at all.

Dealing with corruption

Database corruption shouldn’t happen. It can anyway. There can be hardware faults, kernel and filesystem bugs, SSDs that lie about doing reliable disk flushes, buggy SANs, and of course PostgreSQL bugs. If you suspect database corruption, before you do anything else, read and act on the advice in the wiki page on corruption.

The guts

To see how it all works, start with the macro relpathbackend in src/include/common/relpath.h. It calls GetRelationPath in src/common/relpath.c.

The manual discusses the database’s on disk structure; see storage file layout.