Cluster Layout

PGDATA

The root directory of a PostgreSQL cluster. Everything the server manages — data files, WAL, config, sockets — lives under this path. The location is set at initdb time and is stored in the environment variable $PGDATA and in the server process itself. You can retrieve it at any time with SHOW data_directory.

Write-Ahead Log (WAL)

A sequential log of every change made to the database, written before the change is applied to the actual data files. On crash, PostgreSQL replays WAL to bring data files back to a consistent state. WAL also enables streaming replication: standbys apply the primary's WAL to stay in sync. WAL files live in pg_wal/ and are 16 MB each by default.

pg_wal Disk Risk

pg_wal/ is not a static log — segments are continuously created and, once no longer needed, recycled or removed. "No longer needed" depends on three things all being true: the segment has been checkpointed, it has been archived (if archiving is enabled), and no replication slot is still holding it. If any of those stalls — a failing archive_command, an inactive replication slot, or checkpoints falling behind — pg_wal grows without bound and can fill the data disk, which halts the entire server, not just WAL writes. This is one of the most common causes of a PostgreSQL primary going down in production.

🔌 Connect as postgres

Connect to the postgres database as the postgres superuser. All system-level queries in this lab require superuser access.

psql -U postgres

psql (18.4) Type "help" for help. postgres=#

📂 Find PGDATA

Use SHOW data_directory to retrieve the path of the PGDATA directory. This is the root of the entire cluster on disk.

SHOW data_directory;

data_directory ---------------- /var/lib/postgresql/18/data (1 row)

🔢 Check the Major Version

Confirm the running server version with SHOW server_version inside psql.

SHOW server_version;

server_version ---------------- 18.4 (1 row)

📁 List the Top-Level PGDATA Contents

Use the as-postgres helper to list everything directly under PGDATA as the postgres OS user. The student account does not have read access to /var/lib/postgresql/18/data — as-postgres runs the command under the postgres OS user.

\! as-postgres ls -al /var/lib/postgresql/18/data

total 144 drwx------ 20 postgres postgres 4096 Jun 22 19:48 . drwxr-xr-x 5 root root 4096 Jun 22 19:48 .. -rw------- 1 postgres postgres 3 Jun 22 17:05 PG_VERSION drwx------ 10 postgres postgres 4096 Jun 22 17:05 base drwx------ 2 postgres postgres 4096 Jun 22 19:50 global drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_commit_ts drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_dynshmem -rw------- 1 postgres postgres 5721 Jun 22 17:05 pg_hba.conf -rw------- 1 postgres postgres 2681 Jun 22 17:05 pg_ident.conf drwxr-xr-x 2 postgres postgres 4096 Jun 22 17:05 pg_log drwx------ 4 postgres postgres 4096 Jun 22 17:05 pg_logical drwx------ 4 postgres postgres 4096 Jun 22 17:05 pg_multixact drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_notify drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_replslot drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_serial drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_snapshots drwx------ 2 postgres postgres 4096 Jun 22 19:48 pg_stat drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_stat_tmp drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_subtrans drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_tblspc drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_twophase drwx------ 4 postgres postgres 4096 Jun 22 17:05 pg_wal drwx------ 2 postgres postgres 4096 Jun 22 17:05 pg_xact -rw------- 1 postgres postgres 88 Jun 22 17:05 postgresql.auto.conf -rw------- 1 postgres postgres 33438 Jun 22 17:05 postgresql.conf -rw------- 1 postgres postgres 40 Jun 22 19:48 postmaster.opts -rw------- 1 postgres postgres 70 Jun 22 19:48 postmaster.pid

🗄️ Map OIDs to Databases

Query pg_database to list all databases with their OIDs. Each OID corresponds to a subdirectory under base/ in PGDATA.

SELECT oid, datname FROM pg_database ORDER BY oid;

oid | datname -------+------------ 1 | template1 4 | template0 5 | postgres 16385 | beer_db 17478 | social_db 18098 | tv_db 18378 | weather_db (7 rows)

🧵 Identify the Tablespace Directory

pg_tblspc/ holds symlinks to tablespace directories — but only for tablespaces created beyond the two built-ins. Query pg_tablespace to see pg_default and pg_global, the tablespaces every fresh cluster starts with.

SELECT spcname, oid FROM pg_tablespace;

spcname | oid ------------+------ pg_default | 1663 pg_global | 1664 (2 rows)

📍 Locate the PID File

Read postmaster.pid to see the running server's PID, the PGDATA path it was started with, and the port it is listening on. This file exists only while the server is up and is removed automatically on a clean shutdown.

\! as-postgres cat /var/lib/postgresql/18/data/postmaster.pid

87 /var/lib/postgresql/18/data 1782157719 5432 /tmp * 1250 0 ready

📄 Find the Current Log File

Use pg_current_logfile() to find which file on disk is receiving log output right now. On this cluster, logging_collector is off — so the function returns NULL. Identify what that means and where logs go instead.

SELECT pg_current_logfile();

pg_current_logfile -------------------- (1 row)

⏱️ Server Start Time

Use pg_postmaster_start_time() to find when the PostgreSQL server process started. This is useful for confirming whether a restart actually occurred.

SELECT pg_postmaster_start_time();

pg_postmaster_start_time ------------------------------- 2026-06-22 19:48:40.061312+00 (1 row)

📋 List WAL Files

Use the pg_ls_waldir() function to list files in the pg_wal/ directory, most recent first. Each segment is 16 MB by default. Notice the naming convention — files are named by their LSN position.

SELECT name, size, modification FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 5;

name | size | modification --------------------------+----------+------------------------ 00000001000000000000000A | 16777216 | 2026-06-22 19:50:12+00 000000010000000000000009 | 16777216 | 2026-06-22 19:50:07+00 000000010000000000000008 | 16777216 | 2026-06-22 19:49:57+00 000000010000000000000007 | 16777216 | 2026-06-22 19:49:30+00 00000001000000000000000D | 16777216 | 2026-06-22 17:05:15+00 (5 rows)

📦 Why pg_wal Size Matters

Sum the size of every file in pg_wal/ to see the total WAL volume currently held on disk. In production, this number should stay roughly stable — continuous growth means something is preventing old segments from being recycled.

SELECT count(*) AS segments, pg_size_pretty(sum(size)) AS total_size FROM pg_ls_waldir();

segments | total_size ----------+------------ 7 | 112 MB (1 row)