How to Check Where PostgreSQL Stores Its Database Files (Step-by-Step)

When you’re troubleshooting disk usage, planning backups, investigating performance issues, or validating a migration, one of the first questions you’ll ask is:

“Where are my PostgreSQL database files actually stored?”

PostgreSQL is very transparent about this—if you know where to look. In this guide, we’ll cover multiple ways to find the PostgreSQL data directory and map database objects (like tables and indexes) to their physical files on disk.

1) Understand the PostgreSQL “Data Directory”

PostgreSQL stores everything under a single main folder called the data directory (often referred to as PGDATA). This directory contains the physical files for:

  • databases and system catalogs
  • table and index files
  • transaction logs
  • WAL (Write-Ahead Log) / pg_wal
  • configuration files
  • statistics and background worker state

Common subfolders you’ll see under the data directory include:

  • base/ → where database files live (per database)
  • global/ → cluster-wide system tables
  • pg_wal/ → WAL files (critical for recovery / replication)
  • pg_tblspc/ → links to tablespaces (if you use them)

2) Find the Data Directory Using SQL (psql)

If you can connect to the server with psql, the simplest method is:

SHOW data_directory;

Or:

SELECT current_setting('data_directory');

This returns the absolute path to the PostgreSQL data directory—your “root folder” for storage.

✅ Example output:

/var/lib/pgsql/15/data

3) Check Key Configuration File Locations

If you want to confirm where PostgreSQL is running from and where its configs live:

SHOW config_file;
SHOW hba_file;
SHOW ident_file;

These are especially useful on Linux systems where multiple PostgreSQL clusters may exist.

4) Find the Data Directory from Linux (Systemd + Processes)

If you have OS access (SSH), these commands help you locate the startup parameters and data directory.

Option A: Check service status

systemctl status postgresql

This often shows the service unit name (and sometimes includes the -D data directory argument depending on the distro).

Option B: Check the running postgres process (very reliable)

ps -ef | grep [p]ostgres

Look for a line like:

postgres -D /var/lib/pgsql/15/data

That -D value is the data directory.

5) Where Does PostgreSQL Store Each Database?

Inside the data directory, PostgreSQL stores each database under a folder named by its OID (Object Identifier).

To list database OIDs:

SELECT oid, datname FROM pg_database ORDER BY datname;

Then the database files typically live here:

<data_directory>/base/<db_oid>/

✅ Example:

If your database appdb has OID 16384, the folder is:

/var/lib/pgsql/15/data/base/16384/

6) Find the Actual File for a Specific Table (Mapping SQL → Disk)

This is the most practical thing DBAs need: “Show me the file for this table.”

Run:

SELECT pg_relation_filepath('schema.table_name');

This returns a path relative to the data directory, like:

base/16384/24576

To return the absolute filesystem path:

SELECT current_setting('data_directory') || '/' ||
       pg_relation_filepath('schema.table_name');

✅ Example output:

/var/lib/pgsql/15/data/base/16384/24576

7) Important Notes About Table Files (Segments, Indexes, and TOAST)

Large tables can be split into multiple files

A large table may appear as:

  • 24576
  • 24576.1
  • 24576.2

This is normal: PostgreSQL uses segment files when the relation grows beyond a certain size.

Indexes have their own files too

An index is a separate object with its own physical storage. You can map an index the same way:

SELECT pg_relation_filepath('index_name');

TOAST tables (for large values)

Very large text/blob data can be stored in a separate TOAST table behind the scenes.

8) What If You Use Tablespaces?

If tablespaces are configured, some data may live outside base/.

To list tablespaces and their locations:

SELECT spcname, pg_tablespace_location(oid)
FROM pg_tablespace;

In the filesystem, PostgreSQL references tablespaces through:

<data_directory>/pg_tblspc/

This directory contains links pointing to the real tablespace paths.

9) Managed PostgreSQL (AWS RDS / Azure / GCP) Considerations

On managed PostgreSQL services, you typically cannot browse the server filesystem directly.

But you can still:

  • run SHOW data_directory;
  • map tables using pg_relation_filepath()
  • inspect tablespaces (where supported)

Just note you may not have OS-level access to the returned path.

Best Practice: Don’t Manually Edit or Copy These Files

It’s worth saying clearly:

Never copy, move, edit, or delete PostgreSQL data files manually while the database is running.

If you need backup/restore:

  • use pg_dump / pg_restore for logical backups
  • use filesystem snapshots only with PostgreSQL-safe procedures (and ideally with WAL archiving / replication awareness)
  • use pg_basebackup for physical backups

Quick Checklist

If you only remember a few commands, remember these:

✅ Data directory:

SHOW data_directory;

✅ Database OID:

SELECT oid, datname FROM pg_database;

✅ Table → file mapping:

SELECT pg_relation_filepath('schema.table');

✅ Tablespaces:

SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;