Installing PostgreSQL In Docker

Understanding PostgreSQL File Structure: A Lay of the Land

PostgreSQL stores all of its data on disk in a predictable, well-organized directory structure. Understanding where PostgreSQL keeps tables, indexes, configuration files, transaction logs, and temporary files is essential for serious database administration.

In this article, we’ll take a tour of the PostgreSQL data directory (also known as the PGDATA directory). By the end, you’ll know exactly where objects live, what each folder does, and which files you should care about during troubleshooting, migrations, backups, and performance tuning.

The PGDATA Directory

The PGDATA directory is the heart of a PostgreSQL server. This is where PostgreSQL stores everything it needs to run — database files, WAL files, metadata, configuration, and stats. The location depends on your installation method, but the most common paths are:


# Debian Linux (PostgreSQL 18 and newer):
/var/lib/postgresql/18/main

# Verify via PostgreSQL:
SHOW data_directory;

# Or check on the filesystem:
ls -l /var/lib/postgresql/18/main

Inside PGDATA, you'll find a wide variety of files and subdirectories. Let’s break down the most important ones.

Key Directories Inside PGDATA

1. base/ — The Actual Table
This is where PostgreSQL stores the data for all databases in the cluster. Each database gets its own subfolder, identified by an OID (Object Identifier).


PGDATA/
└── base/
    ├── 1/        # template1
    ├── 12407/    # postgres database
    ├── 16384/    # your database

Inside each database directory, tables and indexes are stored as individual files. For example:


16384/
├── 2610      # pg_class table
├── 2611      # pg_attribute table
├── 32768     # a user table
├── 32769     # its index

PostgreSQL uses numeric filenames internally — it does not use table names on disk. You can map these to real objects using:


SELECT oid, relname FROM pg_class ORDER BY oid;

2. global/ — Cluster-Wide Metadata
Stores objects shared across all databases, such as:

Roles & permissions
Cluster-wide tables
Global transaction information

3. pg_wal/ — Write-Ahead Logs
This is one of the most critical directories in PostgreSQL. WAL files track all database changes before they are written to the main data files. If you lose pg_wal, you lose your database!

WAL is essential for:

Crash recovery
Point-in-time recovery (PITR)
Replication

4. pg_stat/ and pg_stat_tmp/
These hold PostgreSQL's runtime statistics. They refresh automatically and are safe to delete when the server is stopped.

5. pg_subtrans/
Tracks subtransactions (savepoints). Mainly useful for debugging complex transaction issues.

6. pg_multixact/
Stores information about multi-transaction locks. Useful when debugging locking or "hint bit" behavior.

7. pg_commit_ts/ (optional)
Stores commit timestamps if the feature is enabled.

Important Configuration Files

These live directly inside PGDATA or alongside it, depending on installation:

File	Description
postgresql.conf	Main configuration file. Controls memory, directories, logging, autovacuum, and more.
pg_hba.conf	Client authentication rules (local, md5, scram, certificates, IP rules).
pg_ident.conf	Maps system users to database users.
postmaster.pid	PID file that ensures only one PostgreSQL instance runs on PGDATA.

Temporary Files

PostgreSQL stores temp files either inside PGDATA (i.e., pg_temp files inside database folders) or in an OS temp directory depending on settings. These hold:

Big sorts
Hash aggregates
Large query operations

If you see frequent temp file usage, it often means you need to adjust: work_mem, maintenance_work_mem, or log_temp_files.

Conclusion

Understanding PostgreSQL’s file structure helps you diagnose problems faster, perform safer migrations, and build stronger backup/restore strategies. The PGDATA directory is the beating heart of your database, and knowing what lives where gives you confidence when working under pressure.