Map PostgreSQL File Nodes Back to Tables with oid2name

A disk fills up, one numbered file under a PostgreSQL data directory looks huge, and nobody knows which table it belongs to: oid2name answers that. You will identify which 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.

• Time: about ten minutes.
• You need: a running PostgreSQL server with readable system catalogues, the matching client utilities package, and a database login.
• Privileges: you may need elevated access 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 somewhere on a new shell's PATH. Use the full path in scripts when you need the client version to be unambiguous. The installed help also confirms that -f means file node, while -o means OID; those numbers can differ.

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 relationship that matters is the numeric database OID against 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 an OID is invalid. Fix the login details or use the role meant 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 one read. Do not change ownership, remove a link or rename a directory while you are still investigating a file node.

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 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 entirely, 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 chase.

5. Compare OIDs, names and indexes

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

$ /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
----------------------------------------------------

• -t takes 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 you asked for ordinary tables only.
• -x adds the schema, tablespace and OID columns, which makes the distinction between a relation's OID and its 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.

Keep the safety boundary clear

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.