Inside the data directory

The top level

$ ls -la $PGDATA

drwx------ 5 postgres postgres     4096 base
drwx------ 2 postgres postgres     4096 global
drwx------ 2 postgres postgres     4096 pg_commit_ts
drwx------ 2 postgres postgres     4096 pg_dynshmem
-rw------- 1 postgres postgres     5753 pg_hba.conf
-rw------- 1 postgres postgres     2681 pg_ident.conf
drwx------ 4 postgres postgres     4096 pg_logical
drwx------ 4 postgres postgres     4096 pg_multixact
drwx------ 2 postgres postgres     4096 pg_notify
drwx------ 2 postgres postgres     4096 pg_replslot
drwx------ 2 postgres postgres     4096 pg_serial
drwx------ 2 postgres postgres     4096 pg_snapshots
drwx------ 2 postgres postgres     4096 pg_stat
drwx------ 2 postgres postgres     4096 pg_stat_tmp
drwx------ 2 postgres postgres     4096 pg_subtrans
drwx------ 2 postgres postgres     4096 pg_tblspc
drwx------ 2 postgres postgres     4096 pg_twophase
-rw------- 1 postgres postgres        3 PG_VERSION
drwx------ 4 postgres postgres     4096 pg_wal
drwx------ 2 postgres postgres     4096 pg_xact
-rw------- 1 postgres postgres       88 postgresql.auto.conf
-rw------- 1 postgres postgres    32657 postgresql.conf
-rw------- 1 postgres postgres       36 postmaster.opts
-rw------- 1 postgres postgres       99 postmaster.pid

Note the permissions before anything else. Every entry is 0700 or 0600, owned by the postgres user. This is not advisory: PostgreSQL refuses to start if the data directory is group- or world-readable beyond what it permits, because the files contain data the database is responsible for protecting. A well-meaning chmod -R 755 on a data directory produces a cluster that will not start, and the error message is clear about why.

The entries that matter operationally:

PG_VERSION deserves a mention out of proportion to its three bytes. When you are handed a data directory with no running server — a restored backup, a disk recovered from a failed host — this file tells you which PostgreSQL major version can read it. A data directory can only be opened by the major version that created it, so this is the first thing to read before installing anything.

Finding the file behind a table

Databases and tables are directories and files named by numbers, not by name. The mapping is available from the server.

$ psql -U postgres -c 'SELECT oid, datname FROM pg_database ORDER BY oid'

  oid  |  datname
-------+-----------
   1 | template1
   4 | template0
   5 | postgres
16389 | shop
(4 rows)

-- and the directories present under base/
1
16389
4
5

For an individual relation, pg_relation_filepath() gives the answer directly and correctly, including for tables in tablespaces outside PGDATA.

$ psql -U postgres -d shop -c "SELECT pg_relation_filepath('orders'), pg_size_pretty(pg_relation_size('orders'))"

     filepath     |  size
------------------+---------
base/16389/16390 | 0 bytes
(1 row)

The number in the filename is the relfilenode, and it is not the same thing as the table’s OID, though on a newly created table the two usually match. Operations that rewrite a table — VACUUM FULL, CLUSTER, some forms of ALTER TABLE — write a new file with a new relfilenode and drop the old one. That is why the correct way to find a table’s file is always to ask the server, never to record the number and assume it is stable.

Going the other way, from a large file to the table responsible, is the more common need during a disk-space investigation:

# Which databases are consuming the space?
psql -U postgres -c \
  "SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
     FROM pg_database ORDER BY pg_database_size(datname) DESC"

# Within one database, which relations?
psql -U postgres -d shop -c \
  "SELECT relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS total
     FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname NOT IN ('pg_catalog','information_schema')
      AND c.relkind IN ('r','m')
    ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20"

pg_total_relation_size() includes the table, its indexes and its TOAST data; pg_relation_size() is the table’s main fork alone. The difference between them is frequently large and is the subject of Part VI.

Reading postmaster.pid

$ cat $PGDATA/postmaster.pid

1
/var/lib/postgresql/18/docker
1787848884
5432
/var/run/postgresql
*
 527564         5
ready

Line by line: the postmaster’s PID, the data directory, the start time as a Unix timestamp, the port, the Unix socket directory, the listen_addresses value, the shared memory key and ID, and the current status.

Two operational uses. First, it is how you determine which server owns a data directory — the PID and the data directory together, so a host running several clusters can be untangled. Second, its presence is how PostgreSQL prevents two postmasters from opening the same directory simultaneously, which would corrupt it immediately.

What a backup actually copies

The map above explains something that matters in Part XIII. A physical backup copies PGDATA in its entirety — every database, the shared catalogues, the configuration, the transaction status — plus the WAL generated while the copy was running.

It does not automatically include tablespaces. pg_tblspc/ holds symbolic links to directories elsewhere on the filesystem, and a naive archive of PGDATA that does not follow those links produces a backup that restores into a cluster with missing tables. Backup tools handle this correctly; a hand-rolled tar frequently does not, and the failure appears only at restore time.

This is the first appearance of a theme this course returns to repeatedly: a backup procedure is not verified by the backup completing.

Production discipline

1. Read the data directory; do not write to it. The map is for interpretation. The only file edited by hand is postgresql.conf.
2. Ask the server for a relation’s path. pg_relation_filepath() is correct across tablespaces and after rewrites; a remembered number is not.
3. Check PG_VERSION before touching an offline data directory. It tells you which major version can open it, and that determines what you install.
4. Never delete postmaster.pid to make a startup error go away. PostgreSQL already removes genuinely stale lock files; a refusal means it found a reason.
5. Account for tablespaces when reasoning about backups and disk usage. pg_tblspc/ means data may not be under PGDATA at all.

Cross-course references

• Linux for Production Sysadmins — Part XIII (Disks) and Part XVIII (Storage) cover the filesystem layer this directory sits on, and Part III (Files) covers the permission model PostgreSQL enforces on it.
• Ceph & Distributed Storage — Part XXXV (RBD architecture) covers what a block device beneath this directory is actually doing with the writes.
• Docker & Containers — Part VIII (Storage) covers volume lifetime, which determines whether this directory outlives the container that created it.

Quiz