PostgreSQL DBA Part 3: The Data Directory (PGDATA) Explained

This is Part 3 of the PostgreSQL DBA series on SQL Tips. The PostgreSQL data directory, usually called PGDATA, is the one folder where a cluster keeps everything it stores: every table, every index, the write-ahead log and the control file. On Ubuntu with PostgreSQL 18 it is /var/lib/postgresql/18/main, while the configuration files live separately in /etc/postgresql/18/main. By the end of this part you will be able to find the data directory, explain what every file and folder in it does, map any table to the file behind it, measure what is using the space, and move the data directory to another disk safely.

In Part 2 we installed PostgreSQL 18 from the official PGDG repository and learned to start, stop and reload the 18 main cluster. Part 1 gave a quick look inside the data directory. This part opens every drawer and explains what is in it, because a DBA who knows these files can answer questions like "why is the disk full?" and "which table is this file?" in minutes.

What you will learn

• What PGDATA is, who owns it and why its permissions must be 0700
• What each file and folder in /var/lib/postgresql/18/main is for, and which ones you must never touch
• How databases map to folders under base and tables map to files (filenodes, forks and 1GB segments)
• How to read postmaster.pid, postgresql.auto.conf and the control file with pg_controldata
• How to measure disk usage and spot temporary files
• How to move the data directory to a new disk on Ubuntu, step by step

Prerequisites

• Parts 1 and 2 of this series: you know what the postmaster and backends are, and you have a running PostgreSQL 18 cluster called 18 main.
• One Ubuntu Server LTS machine with PostgreSQL 18 installed from the PGDG repository, as set up in Part 2.
• A user account that can run sudo. Most commands here read files owned by the postgres operating system user, so they need sudo or sudo -u postgres.
• For the last section (moving the data directory), a second disk or at least a second folder such as /srv/pgdata. Practise that section on a test server first.

How it works

A PostgreSQL cluster is one running server and the set of databases it manages. On disk, a cluster is exactly one directory: the data directory. The name PGDATA comes from the environment variable that PostgreSQL tools use to find it. Think of the data directory as the server's filing cabinet: each drawer (subfolder) holds one kind of record, and only one clerk (the postgres user) has the key.

The program initdb creates and fills the data directory once, when the cluster is created. On Ubuntu, pg_createcluster ran initdb for you during installation. From then on, only the PostgreSQL server should write to these files. You read them to understand the server; you change them only through SQL and PostgreSQL tools.

Ubuntu (through the postgresql-common package) splits each cluster into two places. Core PostgreSQL keeps its configuration files inside the data directory by default, but Ubuntu moves the hand-edited ones to /etc so they are easy to find and back up:

The server finds its data directory through the data_directory setting in /etc/postgresql/18/main/postgresql.conf. This setting can only be set at server start, so changing it needs a restart, not a reload.

Security is simple and strict. The data directory holds all your data, so initdb removes access for everyone except the postgres user. The server checks this at every start: the directory must have mode 0700 (owner only) or 0750 (owner plus read-only group access), or it refuses to start.

Find and inspect the data directory

Step 1: Find the data directory

Where: on the database server, as your sudo user.

pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

pg_lsclusters is Ubuntu's cluster list from Part 2. The Data directory column is PGDATA for each cluster. Now ask the running server itself, which is the most reliable answer because it shows what the server actually uses:

sudo -u postgres psql -X -c "SELECT name, setting
  FROM pg_settings
 WHERE name IN ('data_directory','config_file','hba_file','ident_file','external_pid_file')
 ORDER BY name;"

       name        |                 setting
-------------------+-----------------------------------------
 config_file       | /etc/postgresql/18/main/postgresql.conf
 data_directory    | /var/lib/postgresql/18/main
 external_pid_file | /var/run/postgresql/18-main.pid
 hba_file          | /etc/postgresql/18/main/pg_hba.conf
 ident_file        | /etc/postgresql/18/main/pg_ident.conf
(5 rows)

The output shows the Ubuntu split: three config files in /etc, the data in /var/lib, and an extra PID file in /var/run/postgresql that Ubuntu's tools use.

Check it worked: the data_directory value matches the Data directory column of pg_lsclusters. You can also run SHOW data_directory; inside psql for just that one value.

Step 2: Check ownership and permissions

Where: on the database server, as your sudo user.

sudo stat -c '%A %a %U:%G %n' /var/lib/postgresql/18/main

drwx------ 700 postgres:postgres /var/lib/postgresql/18/main

Mode 700 means only the owner, postgres, can read, write or enter the folder. Files inside are 0600. If your cluster was created with group access (initdb --allow-group-access), you would see 750 for folders and 0640 for files; group access is read-only and lets a backup user in the same group read the files.

Check it worked: the line starts with drwx------ (or drwxr-x--- for group access) and the owner is postgres:postgres.

Step 3: List what is inside

Where: on the database server, as your sudo user. The folder is closed to other users, so ls needs sudo.

sudo ls -l /var/lib/postgresql/18/main

total 92
-rw------- 1 postgres postgres    3 Oct 20 14:25 PG_VERSION
drwx------ 5 postgres postgres 4096 Oct 20 14:25 base
drwx------ 2 postgres postgres 4096 Oct 21 12:46 global
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_commit_ts
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_dynshmem
drwx------ 4 postgres postgres 4096 Oct 20 14:25 pg_logical
drwx------ 4 postgres postgres 4096 Oct 20 14:25 pg_multixact
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_notify
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_replslot
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_serial
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_snapshots
drwx------ 2 postgres postgres 4096 Oct 21 12:46 pg_stat
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_subtrans
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_tblspc
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_twophase
drwx------ 3 postgres postgres 4096 Oct 20 14:25 pg_wal
drwx------ 2 postgres postgres 4096 Oct 20 14:25 pg_xact
-rw------- 1 postgres postgres   88 Oct 20 14:25 postgresql.auto.conf
-rw------- 1 postgres postgres  130 Oct 21 12:46 postmaster.opts
-rw------- 1 postgres postgres  107 Oct 21 12:46 postmaster.pid

Notice what is not here: postgresql.conf and pg_hba.conf. On Ubuntu they are in /etc/postgresql/18/main. Here is what every item does, grouped by how often a DBA deals with it:

Several of these names end in _xact or _wal for a reason. Before PostgreSQL 10, pg_wal was called pg_xlog and pg_xact was called pg_clog. They were renamed because people kept deleting folders with "log" in the name, thinking they were only message logs. If an old blog post tells you to look in pg_xlog, that is the same folder under its old name.

Check it worked: your listing contains the same names as the table. A missing postmaster.pid simply means the cluster is stopped.

The small files at the top

Step 4: Read PG_VERSION, postmaster.opts and postmaster.pid

Where: on the database server, as your sudo user.

sudo cat /var/lib/postgresql/18/main/PG_VERSION
sudo cat /var/lib/postgresql/18/main/postmaster.opts
sudo cat /var/lib/postgresql/18/main/postmaster.pid

18
/usr/lib/postgresql/18/bin/postgres "-D" "/var/lib/postgresql/18/main" "-c" "config_file=/etc/postgresql/18/main/postgresql.conf"
786
/var/lib/postgresql/18/main
1792586884
5432
/var/run/postgresql
localhost
   208940         1
ready

PG_VERSION contains only the major version. Tools such as pg_upgrade read it to know what kind of cluster they are looking at. postmaster.opts shows exactly how the server was last started: Ubuntu's pg_ctlcluster passes -D with the data directory and -c config_file= pointing to the file in /etc.

postmaster.pid is the lock file of the running server. It stops a second server from starting on the same data directory. Line by line:

Check it worked: the PID on line 1 is the parent of all other PostgreSQL processes. ps -o pid,cmd -p 786 (use your PID) shows /usr/lib/postgresql/18/bin/postgres -D /var/lib/postgresql/18/main ...

Step 5: Look at postgresql.auto.conf

Where: on the database server, as your sudo user.

sudo cat /var/lib/postgresql/18/main/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

This file stays inside PGDATA even on Ubuntu, because the server itself writes it when you run ALTER SYSTEM SET .... It is read after postgresql.conf, so a value here wins over the same value in /etc/postgresql/18/main/postgresql.conf. When a setting "will not change" no matter how you edit postgresql.conf, check this file first. Part 5 shows how to use ALTER SYSTEM and ALTER SYSTEM RESET properly.

Check it worked: a fresh cluster shows only the two comment lines. Any other line is a setting someone changed with ALTER SYSTEM.

Where your tables live: base and global

Now create a small database and table so you have real files to look at. The sample is an orders table with 100,000 rows.

Step 6: Create a sample database and see its folder

Where: on the database server, as the postgres user through psql.

sudo -u postgres psql -X -c "CREATE DATABASE shop;"
sudo -u postgres psql -X -c "SELECT oid, datname FROM pg_database ORDER BY oid;"
sudo ls -l /var/lib/postgresql/18/main/base

CREATE DATABASE
  oid  |  datname
-------+-----------
     1 | template1
     4 | template0
     5 | postgres
 16384 | shop
(4 rows)

total 16
drwx------ 2 postgres postgres 4096 Oct 21 12:46 1
drwx------ 2 postgres postgres 4096 Oct 20 14:25 4
drwx------ 2 postgres postgres 4096 Oct 21 12:46 5
drwx------ 2 postgres postgres 4096 Oct 21 12:47 16384

Every database has an OID (object identifier, a number PostgreSQL gives to each object), and its folder under base is named after that OID, not after the database name. So base/16384 is the shop database. The OIDs 1, 4 and 5 are fixed for template1, template0 and postgres. Your shop OID may differ. Later you will also see a pgsql_tmp folder here; it holds temporary files (Step 11) and appears the first time one is needed.

Check it worked: the number of OID folders matches the number of rows in pg_database.

Step 7: Find the file behind a table

Where: on the database server, connected to shop as postgres (sudo -u postgres psql -X -d shop).

CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer    text NOT NULL,
    amount      numeric(10,2) NOT NULL,
    ordered_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer, amount)
SELECT 'customer_' || g, round((random() * 5000)::numeric, 2)
  FROM generate_series(1, 100000) AS g;

SELECT oid, relname, relfilenode
  FROM pg_class
 WHERE relname IN ('orders', 'orders_pkey');

SELECT pg_relation_filepath('orders')      AS table_file,
       pg_relation_filepath('orders_pkey') AS index_file;

CREATE TABLE
INSERT 0 100000
  oid  |   relname   | relfilenode
-------+-------------+-------------
 16386 | orders      |       16386
 16392 | orders_pkey |       16392
(2 rows)

    table_file    |    index_file
------------------+------------------
 base/16384/16386 | base/16384/16392
(1 row)

Each table and each index is stored in its own file. The file name is the filenode, stored in pg_class.relfilenode. pg_relation_filepath() returns the path relative to PGDATA, so the orders table is /var/lib/postgresql/18/main/base/16384/16386 and its primary key index is a separate file, 16392. This function is the quickest way to locate any table; it saves you remembering the rules.

Check it worked: run sudo ls -l /var/lib/postgresql/18/main/base/16384/16386; the file exists.

Step 8: See the forks and the 1GB segment rule

Where: on the database server: the first and third commands in psql (connected to shop), the second as your sudo user in the shell.

CHECKPOINT;
VACUUM orders;

sudo ls -l /var/lib/postgresql/18/main/base/16384/16386*

-rw------- 1 postgres postgres 6832128 Oct 21 12:47 /var/lib/postgresql/18/main/base/16384/16386
-rw------- 1 postgres postgres   24576 Oct 21 12:47 /var/lib/postgresql/18/main/base/16384/16386_fsm
-rw------- 1 postgres postgres    8192 Oct 21 12:47 /var/lib/postgresql/18/main/base/16384/16386_vm

One table can have several files, called forks. The file with no suffix is the main fork with the rows. _fsm is the free space map, which tells PostgreSQL which pages have room for new rows. _vm is the visibility map, which marks pages known to have no dead rows (VACUUM uses it, Part 12). Unlogged tables also get an _init fork. CHECKPOINT forces changed pages from memory to these files, so the sizes you see are up to date.

The same sizes from SQL, without touching the file system:

SELECT pg_size_pretty(pg_relation_size('orders'))        AS main,
       pg_size_pretty(pg_relation_size('orders', 'fsm')) AS fsm,
       pg_size_pretty(pg_relation_size('orders', 'vm'))  AS vm,
       pg_size_pretty(pg_total_relation_size('orders'))  AS total;

  main   |  fsm  |     vm     |  total
---------+-------+------------+---------
 6672 kB | 24 kB | 8192 bytes | 8920 kB
(1 row)

total also includes the primary key index (and TOAST data, if any), which is why it is larger than the three forks added together. One more rule: when a table or index grows past 1GB, PostgreSQL splits it into 1GB segments named 16386, 16386.1, 16386.2 and so on. A 10GB table is about ten files with the same filenode. Always use the 16386* pattern when you look for a table's files.

Check it worked: pg_controldata in Step 10 reports "Blocks per segment of large relation: 131072". 131072 blocks of 8192 bytes is exactly 1GB.

Step 9: Learn why the filenode can change

Where: on the database server, connected to shop as postgres.

CREATE TABLE payments (payment_id int, amount numeric(10,2));
INSERT INTO payments VALUES (1, 250.00), (2, 990.50);

SELECT oid, relfilenode, pg_relation_filepath('payments')
  FROM pg_class WHERE relname = 'payments';

TRUNCATE payments;

SELECT oid, relfilenode, pg_relation_filepath('payments')
  FROM pg_class WHERE relname = 'payments';

  oid  | relfilenode | pg_relation_filepath
-------+-------------+----------------------
 16394 |       16394 | base/16384/16394
(1 row)

TRUNCATE TABLE
  oid  | relfilenode | pg_relation_filepath
-------+-------------+----------------------
 16394 |       16397 | base/16384/16397
(1 row)

The OID stayed 16394, but the file changed to 16397. TRUNCATE, REINDEX, CLUSTER, VACUUM FULL and some forms of ALTER TABLE write a fresh file and switch the table to it. So never assume the file name equals the OID; ask pg_relation_filepath() every time.

You can also go the other way. When a disk tool shows a big file and you need to know which table it is, use pg_filenode_relation(tablespace, filenode) (tablespace 0 means the database's default tablespace), connected to the database whose folder holds the file:

SELECT pg_filenode_relation(0, 16386);

 pg_filenode_relation
----------------------
 orders
(1 row)

System catalogs are a special case. For some, including pg_class itself, relfilenode is 0, and the real file number comes from pg_relation_filenode():

SELECT relname, relfilenode, pg_relation_filenode('pg_class') AS real_filenode
  FROM pg_class WHERE relname = 'pg_class';

 relname  | relfilenode | real_filenode
----------+-------------+---------------
 pg_class |           0 |          1259
(1 row)

Check it worked: after the TRUNCATE, sudo ls /var/lib/postgresql/18/main/base/16384/16394 fails with "No such file or directory" once the next checkpoint has run, and the 16397 file exists.

pg_wal and the control file

Step 10: Look at the WAL files and pg_control

Where: on the database server: the SQL in psql as postgres, the commands in the shell as your sudo user.

SELECT * FROM pg_ls_waldir() ORDER BY name;

SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());

           name           |   size   |      modification
--------------------------+----------+------------------------
 000000010000000000000002 | 16777216 | 2026-10-21 12:47:47+00
 000000010000000000000003 | 16777216 | 2026-10-21 12:47:38+00
(2 rows)

 pg_current_wal_lsn |     pg_walfile_name
--------------------+--------------------------
 0/2913090          | 000000010000000000000002
(1 row)

As Part 1 explained, WAL is the notebook PostgreSQL writes in before it changes the real data files. Each segment file is 16MB (16777216 bytes) by default. The name is a hexadecimal counter that only grows, starting at 000000010000000000000001. pg_walfile_name() tells you which file the server is writing now; segment 3 here is an old file already recycled and renamed for future use. pg_ls_waldir() is allowed for superusers and members of the pg_monitor role, so a monitoring user does not need shell access.

sudo ls -l /var/lib/postgresql/18/main/pg_wal

total 32776
-rw------- 1 postgres postgres 16777216 Oct 21 12:47 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Oct 21 12:47 000000010000000000000003
drwx------ 2 postgres postgres     4096 Oct 20 14:25 archive_status
drwx------ 2 postgres postgres     4096 Oct 20 14:25 summaries

archive_status tracks which segments have been archived (Part 19). summaries is new in PostgreSQL 17: it holds WAL summaries, which incremental backups need when summarize_wal is on (it is off by default, so the folder stays empty).

The most important single file in the cluster is global/pg_control, the control file. After each checkpoint the server records the checkpoint position there, and at start-up it reads pg_control first to know where crash recovery must begin. It is binary, so you read it with pg_controldata. On Ubuntu this program is not on your PATH; use its full path:

sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata -D /var/lib/postgresql/18/main \
  | grep -E 'cluster state|checkpoint location|REDO WAL file|Time of latest|Database block size|Blocks per segment|Bytes per WAL|checksum version'

Database cluster state:               in production
Latest checkpoint location:           0/28ECE70
Latest checkpoint's REDO WAL file:    000000010000000000000002
Time of latest checkpoint:            Wed Oct 21 12:47:42 2026
Database block size:                  8192
Blocks per segment of large relation: 131072
Bytes per WAL segment:                16777216
Data page checksum version:           1

"in production" means the server is running (after a clean stop it says "shut down"). The REDO WAL file is the oldest WAL segment crash recovery would need; older segments can be recycled. Block size 8192 is the 8kB page size, and 131072 blocks per segment confirms the 1GB rule from Step 8. "Data page checksum version: 1" means data checksums are on, the default for new clusters since PostgreSQL 18 (older versions showed 0 unless you asked initdb for --data-checksums).

Check it worked: "Database cluster state" says "in production" while the cluster is online. Stop the cluster and run the command again: it says "shut down".

Temporary files and disk usage

Step 11: Watch a temporary file being created

Where: on the database server, connected to shop as postgres.

When a sort or hash needs more memory than work_mem allows (default 4MB), PostgreSQL spills to temporary files in base/pgsql_tmp. They are deleted when the query ends, but on a busy server they can fill the disk for a while. The setting log_temp_files (default -1, off) logs each one; 0 logs all of them. Both settings below last only for this session.

SET log_temp_files = 0;
SET work_mem = '64kB';

SELECT count(*)
  FROM (SELECT customer FROM orders ORDER BY customer OFFSET 0) AS sorted;

SET
SET
 count
--------
 100000
(1 row)

sudo tail -2 /var/log/postgresql/postgresql-18-main.log

2026-10-21 12:48:00.962 UTC [754] postgres@shop LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp754.0", size 1933312
2026-10-21 12:48:00.962 UTC [754] postgres@shop STATEMENT:  SELECT count(*) FROM (SELECT customer FROM orders ORDER BY customer OFFSET 0) AS sorted;

The file name pgsql_tmp754.0 is pgsql_tmp plus the PID of the backend (754) and a counter. The 64kB limit was too small for the sort, so about 1.9MB went to disk. To see temporary files while a query is still running, use SELECT * FROM pg_ls_tmpdir();. Part 23 covers logging, and Part 28 shows how to size work_mem.

Check it worked: the log contains a "temporary file" line. RESET work_mem; RESET log_temp_files; (or disconnecting) puts the settings back.

Step 12: Measure what uses the space

Where: on the database server: du as your sudo user, the SQL in psql as postgres.

sudo du -sh /var/lib/postgresql/18/main/base /var/lib/postgresql/18/main/global /var/lib/postgresql/18/main/pg_wal
sudo du -sh /var/lib/postgresql/18/main/base/*

38M	/var/lib/postgresql/18/main/base
576K	/var/lib/postgresql/18/main/global
33M	/var/lib/postgresql/18/main/pg_wal
7.4M	/var/lib/postgresql/18/main/base/1
16M	/var/lib/postgresql/18/main/base/16384
7.2M	/var/lib/postgresql/18/main/base/4
7.2M	/var/lib/postgresql/18/main/base/5
4.0K	/var/lib/postgresql/18/main/base/pgsql_tmp

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
  FROM pg_database
 ORDER BY pg_database_size(datname) DESC;

  datname  |  size
-----------+---------
 shop      | 16 MB
 postgres  | 7503 kB
 template1 | 7503 kB
 template0 | 7345 kB
(4 rows)

Both views agree: base/16384 (shop) is 16MB. When a disk alert fires, this is the order to check: is it pg_wal (WAL piling up, often because archiving or a replication slot is stuck), base/pgsql_tmp (a runaway query), or one database in base (real data growth or bloat)? The answer decides the fix.

Check it worked: the folder sizes from du and pg_database_size() match within a few kB.

Move the data directory to another disk

A common task: the root disk is small, and you have added a bigger disk mounted at /srv. You want the cluster's data there. The safe procedure is: stop the server, copy the whole folder with ownership and permissions kept, point data_directory at the new place, start, verify, and only then remove the old copy. Ubuntu's tools read data_directory from postgresql.conf, so they follow the move automatically.

before:  /var/lib/postgresql/18/main    (root disk)
after:   /srv/pgdata/18/main            (new disk mounted at /srv)

Step 13: Prepare the target folder

Where: on the database server, as your sudo user.

sudo mkdir -p /srv/pgdata
sudo chown postgres:postgres /srv/pgdata
sudo chmod 700 /srv/pgdata
sudo -u postgres mkdir -p /srv/pgdata/18

Do not use the mount point itself (/srv) as the data directory. The documentation recommends a postgres-owned folder inside the mount point. That avoids permission problems, for example with pg_upgrade, and if the disk is ever not mounted, the server fails cleanly instead of starting on an empty folder.

Check it worked: sudo ls -ld /srv/pgdata /srv/pgdata/18 shows both owned by postgres postgres.

Step 14: Stop the cluster

Where: on the database server, as your sudo user.

sudo systemctl stop postgresql@18-main
pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 down   postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Check it worked: Status is down and postmaster.pid no longer exists in the old data directory.

Step 15: Copy the data directory

Where: on the database server, as the postgres user (through sudo).

sudo -u postgres cp -a /var/lib/postgresql/18/main /srv/pgdata/18/main
sudo ls -ld /srv/pgdata/18/main

drwx------ 19 postgres postgres 4096 Oct 21 12:48 /srv/pgdata/18/main

Check it worked: the new folder has mode drwx------ and owner postgres. sudo du -sh /var/lib/postgresql/18/main /srv/pgdata/18/main shows the same size for both.

Step 16: Point data_directory at the new folder and start

Where: on the database server, as your sudo user. Edit with sudo nano /etc/postgresql/18/main/postgresql.conf and change the data_directory line.

# /etc/postgresql/18/main/postgresql.conf  (before)
data_directory = '/var/lib/postgresql/18/main'		# use data in another directory

# /etc/postgresql/18/main/postgresql.conf  (after)
data_directory = '/srv/pgdata/18/main'		# use data in another directory

data_directory has no built-in default; Ubuntu's pg_createcluster writes this line for you. It can only be set at server start, so the change takes effect when you start the cluster:

sudo systemctl start postgresql@18-main
pg_lsclusters
sudo -u postgres psql -X -c "SHOW data_directory;"
sudo -u postgres psql -X -d shop -c "SELECT count(*) FROM orders;"

Ver Cluster Port Status Owner    Data directory      Log file
18  main    5432 online postgres /srv/pgdata/18/main /var/log/postgresql/postgresql-18-main.log
   data_directory
---------------------
 /srv/pgdata/18/main
(1 row)

 count
--------
 100000
(1 row)

Check it worked: pg_lsclusters shows the new path and online, SHOW data_directory agrees, and your data is all there. Check the end of /var/log/postgresql/postgresql-18-main.log for "database system is ready to accept connections".

Undo: if anything is wrong, stop the cluster, put the old data_directory line back and start it again. The old folder is untouched, so you are back where you began.

Step 17: Remove the old copy

Where: on the database server, as your sudo user, only after the cluster has run from the new place and you have a backup.

sudo mv /var/lib/postgresql/18/main /var/lib/postgresql/18/main.old
# a few days later, when you are sure:
sudo rm -rf /var/lib/postgresql/18/main.old

Check it worked: pg_lsclusters still shows the cluster online at /srv/pgdata/18/main after the rename.

Common mistakes and how to fix them

FATAL: data directory has invalid permissions

This happens after someone runs chmod 755 on the folder, or copies it without -a. The cluster will not start, and the log says:

FATAL:  data directory "/var/lib/postgresql/18/main" has invalid permissions
DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
pg_ctl: could not start server
Examine the log output.

Fix: restore the mode and owner, then start: sudo chmod 700 /var/lib/postgresql/18/main, sudo chown -R postgres:postgres /var/lib/postgresql/18/main, sudo systemctl start postgresql@18-main.

FATAL: lock file "postmaster.pid" already exists

You see this when you try to start a second server on a data directory that is already in use, for example by running the postgres program by hand while the service runs:

FATAL:  lock file "postmaster.pid" already exists
HINT:  Is another postmaster (PID 786) running in data directory "/var/lib/postgresql/18/main"?

Fix: check with pg_lsclusters and ps -u postgres -o pid,cmd first. If the server is running, use it, do not start another one. Only if no process with that PID exists (for example after a power cut) is the file stale; a normal start usually handles that by itself. Never delete postmaster.pid while a server is running: two servers on one data directory will corrupt it.

Deleting WAL files to free space

When the disk fills, pg_wal is often the biggest folder, and rm looks tempting. Removing segments the server still needs breaks crash recovery, and the cluster may not start again. Fix: find why WAL is not being recycled (a failing archive_command or an inactive replication slot are the usual causes) and fix that; the server then removes old segments itself at the next checkpoint. Part 47 walks through it.

Copying or backing up PGDATA while the server runs

A plain cp or tar of a running data directory captures files at different moments and is usually not restorable. Fix: stop the cluster before a file copy, as in the move procedure, or use the proper tools: pg_dump (Part 17) or pg_basebackup (Part 18).

Editing postgresql.auto.conf or looking for postgresql.conf in PGDATA

On Ubuntu, postgresql.conf is in /etc/postgresql/18/main, not in the data directory. And postgresql.auto.conf is owned by ALTER SYSTEM; hand edits can be overwritten. Fix: run SHOW config_file; to find the real file, and use ALTER SYSTEM SET or ALTER SYSTEM RESET to change auto.conf (Part 5).

Practice exercise

1. Create a table employees (emp_id int, name text) in the shop database, insert one row, and find the full path of its data file on disk.
2. Run VACUUM FULL employees; and find the path again. What changed and why?

Quick reference

# List clusters and their data directories
pg_lsclusters

# Ask the server where its data and config files are
sudo -u postgres psql -X -c "SELECT name, setting FROM pg_settings WHERE name IN ('data_directory','config_file','hba_file','ident_file','external_pid_file') ORDER BY name;"

# Check owner and permissions of PGDATA (expect 700 postgres:postgres)
sudo stat -c '%A %a %U:%G %n' /var/lib/postgresql/18/main

# List the data directory
sudo ls -l /var/lib/postgresql/18/main

# Read the small files
sudo cat /var/lib/postgresql/18/main/PG_VERSION
sudo cat /var/lib/postgresql/18/main/postmaster.opts
sudo cat /var/lib/postgresql/18/main/postmaster.pid
sudo cat /var/lib/postgresql/18/main/postgresql.auto.conf

# Map databases to base/<oid> folders
sudo -u postgres psql -X -c "SELECT oid, datname FROM pg_database ORDER BY oid;"
sudo ls -l /var/lib/postgresql/18/main/base

# Table -> file, and file -> table (run in the right database)
sudo -u postgres psql -X -d shop -c "SELECT pg_relation_filepath('orders');"
sudo -u postgres psql -X -d shop -c "SELECT pg_filenode_relation(0, 16386);"

# All forks and segments of one table
sudo ls -l /var/lib/postgresql/18/main/base/16384/16386*

# WAL files and the current WAL file
sudo -u postgres psql -X -c "SELECT * FROM pg_ls_waldir() ORDER BY name;"
sudo -u postgres psql -X -c "SELECT pg_walfile_name(pg_current_wal_lsn());"

# Read the control file
sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata -D /var/lib/postgresql/18/main

# Disk usage per folder and per database
sudo du -sh /var/lib/postgresql/18/main/base/*
sudo -u postgres psql -X -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"

# Move PGDATA: prepare, stop, copy, edit data_directory, start
sudo mkdir -p /srv/pgdata && sudo chown postgres:postgres /srv/pgdata && sudo chmod 700 /srv/pgdata
sudo -u postgres mkdir -p /srv/pgdata/18
sudo systemctl stop postgresql@18-main
sudo -u postgres cp -a /var/lib/postgresql/18/main /srv/pgdata/18/main
sudo nano /etc/postgresql/18/main/postgresql.conf   # data_directory = '/srv/pgdata/18/main'
sudo systemctl start postgresql@18-main
sudo -u postgres psql -X -c "SHOW data_directory;"

# Clean up the lab database (only on a practice server)
sudo -u postgres psql -X -c "DROP DATABASE shop;"

Summary and what's next

• PGDATA is one folder per cluster; on Ubuntu with PostgreSQL 18 it is /var/lib/postgresql/18/main, and the hand-edited config files live in /etc/postgresql/18/main.
• The data directory must be owned by postgres with mode 0700 (or 0750 with group access), or the server refuses to start.
• base/<database OID>/<filenode> holds each table and index, with _fsm and _vm forks and 1GB segments; pg_relation_filepath() finds the file, pg_filenode_relation() finds the table.
• The filenode can change after TRUNCATE, REINDEX, CLUSTER or VACUUM FULL, so never assume it equals the OID.
• pg_wal holds 16MB WAL segments, global/pg_control holds the checkpoint position, and pg_controldata reads it.
• You can measure space with du and pg_database_size(), spot temporary files with log_temp_files, and move PGDATA safely with stop, cp -a, data_directory and start.
• You never delete or edit files in PGDATA by hand.

In Part 4 you will learn the psql essentials every DBA uses: meta-commands, running scripts and changing output formats.