Map PostgreSQL File Nodes Back to Tables with oid2name

You will identify which PostgreSQL database, table or index corresponds to a numeric directory entry, OID or file node. The examples use the installed PostgreSQL 16.15 client, at /usr/lib/postgresql/16/bin/oid2name, and only query the server. They do not edit tables, files or configuration.

Allow about ten minutes. You need a running PostgreSQL server with readable system catalogues, the matching client utilities package, and a database login. You may need elevated privileges to inspect a protected data-directory path with commands such as ls, but oid2name itself normally runs as an ordinary user with database access.

1. Check the installed command

Start with the explicit binary path if the command is not on your PATH:

$ command -v oid2name || echo 'not on PATH'
$ /usr/lib/postgresql/16/bin/oid2name --version
oid2name (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

The utility is often installed in PostgreSQL's versioned bin directory rather than a directory on a new shell's PATH. Use the full path in scripts when you need to make the client version unambiguous. The installed help also confirms that -f means file node, while -o means OID. Those numbers can be different.

2. List database OIDs

With no database-selection option, oid2name lists databases visible through the server connection:

$ /usr/lib/postgresql/16/bin/oid2name
All databases:
   Oid  Database Name  Tablespace
--------------------------------
 16384  appdb          pg_default

Your rows and OIDs will differ. The useful relationship is the numeric database OID to the database name and default tablespace. Connection defaults come from libpq, including PGHOST, PGPORT and PGUSER. If your server needs an explicit connection, add -h, -p and -U, or set those variables for the command.

$ PGHOST=127.0.0.1 PGPORT=5432 PGUSER=DB_USER \
  /usr/lib/postgresql/16/bin/oid2name

A password prompt or a peer-authentication error is a connection problem, not evidence that an OID is invalid. Fix the login details or use the database role intended for inspection. Do not put a password directly in the command line.

3. List tablespaces and relate them to disk paths

Use -s when a numeric directory under pg_tblspc needs a name:

$ /usr/lib/postgresql/16/bin/oid2name -s
All tablespaces:
   Oid  Tablespace Name
----------------------
 1663   pg_default
 1664   pg_global

The command reads catalogue information; it does not follow or repair tablespace links. If you inspect the filesystem afterwards, use the actual PGDATA belonging to this server and check it read-only:

$ ls -ld /path/to/PGDATA/pg_tblspc
$ ls -ld /path/to/PGDATA/pg_tblspc/TABLESPACE_OID

Replace both placeholders before running the commands. If the directory is restricted, ask an administrator or use sudo ls only for that read operation. Do not change ownership, remove a link or rename a directory while investigating a filenode.

4. Map a file node in one database

PostgreSQL relation files commonly appear as numeric names inside a database directory. Once you have a candidate file node and know the database, pass both values to -d and -f:

$ /usr/lib/postgresql/16/bin/oid2name \
    -d APP_DATABASE \
    -f FILE_NODE
From database "APP_DATABASE":
  Filenode  Table Name
----------------------
    155173  accounts

The example values are placeholders. Use digits copied from the actual filename, not the size or inode number shown by ls -li. The lookup is restricted to the database named by -d; the same number in another database can identify something else, or nothing.

PostgreSQL can create additional segment files for a large relation, such as a file with a suffix after the main numeric name. Start with the numeric filenode and treat the suffix as a storage detail, not a new relation to look up.

5. Compare OIDs, names and indexes

Use -o for a relation OID and -t for a table-name pattern. You can combine selectors, and the results include objects matched by any selector:

$ /usr/lib/postgresql/16/bin/oid2name \
    -d APP_DATABASE \
    -o RELATION_OID \
    -t 'accounts%' \
    -i \
    -x
From database "APP_DATABASE":
  Filenode  Table Name       Oid  Schema  Tablespace
----------------------------------------------------

The -t value is a SQL LIKE-style pattern, so accounts% can match more than one name. Quote patterns so the shell does not interpret special characters. -i includes indexes and sequences; without it, a surprising absence may simply mean that you asked for ordinary tables only. -x adds the schema, tablespace and OID columns, making the distinction between a relation's OID and filenode easier to see.

If you need output for a script, add -q to omit headings:

$ /usr/lib/postgresql/16/bin/oid2name -q -d APP_DATABASE -f FILE_NODE
155173  accounts

Keep the database name and numeric value in separate, quoted shell variables if this becomes a script. Check the exit status and handle an empty result explicitly; do not treat a blank lookup as permission to delete a file.

6. Keep the safety boundary clear

oid2name requires a running server and usable system catalogues. It is not a recovery tool for a server with catastrophic catalogue corruption. It also reports catalogue relationships, not a guarantee that every relation file is healthy. For a storage incident, preserve the original files and follow your backup or PostgreSQL recovery procedure.

The commands in this guide change no persistent state, so there is nothing to undo. Do not add a write command to a diagnostic pipeline, and do not remove an apparently orphaned numeric file based on one lookup. A relation can be mapped in a different database or tablespace, and a server may be using files that are not obvious from a casual directory listing.