PostgreSQL DBA Part 1: What a DBA Does and How PostgreSQL Works

This is Part 1 of the PostgreSQL DBA series on SQL Tips. PostgreSQL architecture, explained simply, is three things: a set of processes (one main server process, a few helper processes, and one process per client connection), an area of shared memory they all use, and a set of files on disk where the data and the write-ahead log live. A PostgreSQL DBA is the person who keeps those three parts healthy, safe and fast.

By the end of this part you will know what a PostgreSQL DBA does day to day, and you will be able to point at every running PostgreSQL process on an Ubuntu server, read the main memory settings, and find the exact file on disk that holds a table. Everything is shown on Ubuntu Server with PostgreSQL 18 from the official PostgreSQL apt repository, the same setup the whole series uses.

What you will learn

• What a PostgreSQL DBA is responsible for, and which later parts cover each job
• How PostgreSQL works: the postmaster, background processes and one backend process per connection
• What shared memory and per-session memory are, and which settings control them
• Where PostgreSQL 18 keeps its configuration, data, write-ahead log and log files on Ubuntu
• How to find the processes, memory settings and data files yourself with ps, pg_stat_activity and pg_settings

Prerequisites

This is the first part, so you only need a few things:

• One Ubuntu Server (current LTS) machine or virtual machine where you can run sudo.
• PostgreSQL 18 installed from the official PostgreSQL apt repository (PGDG). Part 2 walks through the installation step by step. If you want to follow along today, the short version is: sudo apt install -y postgresql-common, then sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh, then sudo apt install -y postgresql-18.
• Basic Linux comfort: opening a terminal, running a command, reading its output.
• Basic SQL: you know what a table, a row and a SELECT are.

What a PostgreSQL DBA does

A DBA (database administrator) owns the database server, not the application. Developers decide what tables and queries the application needs. The DBA makes sure the server that runs those queries stays up, keeps every committed change, can be restored after a disaster, and answers quickly.

In practice the work falls into a handful of areas. Each one has its own block of parts later in this series:

Notice how much of that list depends on understanding the server itself. You cannot tune memory you cannot name, or fix a full disk if you do not know which folder is growing. That is why Part 1 is about how PostgreSQL works.

How it works

Think of a PostgreSQL server as a small office. There is a receptionist at the front door, a few staff who work in the back, a shared whiteboard everyone writes on, and filing cabinets where the real records are kept.

The client/server model

PostgreSQL uses a client/server model. The server is a program called postgres. It manages the database files, accepts connections and runs SQL for clients. A client is any program that sends SQL to the server: psql, a Java or Python application, a web server or a backup tool. The client and server can be on different machines and talk over TCP/IP, or on the same machine through a Unix-domain socket (a special file used for local connections, on Ubuntu in /var/run/postgresql).

Processes: the postmaster, backends and helpers

When PostgreSQL starts, one main process starts first. It is often called the postmaster. This is the receptionist: it listens on port 5432, and each time a client connects it starts a new process just for that client. After that the client talks directly to its own process, and the postmaster goes back to waiting for the next connection.

That per-client process is called a backend (in the statistics views its type is client backend). One connection means one backend process. This is why connection counts matter so much in PostgreSQL: 300 connections means 300 operating system processes. Part 10 and Part 30 (connection pooling) build on this.

The postmaster also starts background processes that do housekeeping no single client owns:

Other processes appear only when a feature is on: an archiver when WAL archiving is enabled (Part 19), walsender and walreceiver for streaming replication (Part 33), and parallel worker processes while a query runs in parallel.

Memory: shared and per-session

Shared memory is the shared whiteboard. It is set up once when the server starts and every process can read and write it. The biggest part is shared buffers (shared_buffers, default 128MB), a cache of 8kB data pages. When a query needs a row, PostgreSQL first looks for its page in shared buffers and only reads the data file if the page is not there. A smaller area, the WAL buffers (wal_buffers), holds new WAL records until they are flushed to disk.

Per-session memory belongs to one backend. The most important setting is work_mem (default 4MB): the memory a single sort or hash operation may use before it spills to temporary files on disk. One complex query can run several such operations at once, so the real use can be a multiple of work_mem. maintenance_work_mem (default 64MB) is the same idea for maintenance commands such as VACUUM and CREATE INDEX. Part 28 covers memory tuning in depth.

Files: data files and the write-ahead log

The filing cabinets are the data directory, often called PGDATA. On Ubuntu with PostgreSQL 18 it is /var/lib/postgresql/18/main. Every table and index is stored there as one or more files, split into 1GB pieces when it grows larger than that.

Before PostgreSQL changes a data file, it first writes a description of the change to the write-ahead log (WAL), stored in the pg_wal folder. WAL is the notebook PostgreSQL writes in before it touches the real records. A transaction counts as committed once its WAL is safely on disk; the changed data pages can be written later. If the server crashes, PostgreSQL replays the notebook from the last checkpoint and nothing committed is lost. The same WAL files are what backups, point-in-time recovery and replication are built on.

The whole picture

  client (psql / app)            client (psql / app)
          |                               |
          | connect to port 5432          |
          v                               v
  +------------------------------------------------------+
  | postmaster (main postgres process, listens on 5432)  |
  |   starts -> backend for client 1                     |
  |   starts -> backend for client 2                     |
  |   starts -> checkpointer, background writer,         |
  |             walwriter, autovacuum launcher,          |
  |             io workers, logical repl. launcher       |
  +------------------------------------------------------+
                      | all processes share
                      v
  +------------------------------------------------------+
  | shared memory: shared_buffers (page cache),          |
  |                wal_buffers, lock tables, ...         |
  +------------------------------------------------------+
            |                                  |
            v                                  v
  data files (base/, global/)          WAL files (pg_wal/)
  /var/lib/postgresql/18/main          written first, on commit

A simple UPDATE follows that picture from top to bottom. The backend finds the page in shared buffers (or reads it from the data file), changes the row in memory, and writes a WAL record. At COMMIT the WAL is flushed to pg_wal. Later, the background writer or the checkpointer writes the changed page back to the data file. For a longer, more conceptual tour of the architecture, see our PostgreSQL architecture overview; if you come from Oracle, our Oracle architecture article is a useful comparison.

Hands-on: see the PostgreSQL processes

Now look at each part on a real server. All commands in this part only read information, except the small lab database you create later and drop at the end.

Step 1: Check that the cluster is running

On Ubuntu, one installed PostgreSQL server with its own data directory is called a cluster, named by version and name: "18 main". The pg_lsclusters command from the postgresql-common package lists every cluster on the machine.

Where: on the database server, as your normal sudo user (no sudo needed for this command).

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

The columns tell you the version (18), cluster name (main), port (5432), whether it is running (online), the operating system user that owns it (postgres), the data directory and the log file. Write these down: you will use them throughout the series.

Check it worked: Status must say online. If it says down, start it with sudo systemctl start postgresql@18-main and run pg_lsclusters again.

Step 2: List the server processes from Linux

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

ps -u postgres -o pid,ppid,cmd

  PID  PPID CMD
 1906     1 /usr/lib/postgresql/18/bin/postgres -D /var/lib/postgresql/18/main -c config_file=/etc/postgresql/18/main/postgresql.conf
 1907  1906 postgres: 18/main: io worker 0
 1908  1906 postgres: 18/main: io worker 1
 1909  1906 postgres: 18/main: io worker 2
 1910  1906 postgres: 18/main: checkpointer
 1911  1906 postgres: 18/main: background writer
 1913  1906 postgres: 18/main: walwriter
 1914  1906 postgres: 18/main: autovacuum launcher
 1915  1906 postgres: 18/main: logical replication launcher

The first line is the postmaster. It runs the binary from /usr/lib/postgresql/18/bin/, with -D pointing at the data directory and config_file pointing at the Ubuntu configuration folder /etc/postgresql/18/main/. Every other line has the postmaster's PID in the PPID column: they are its children. PostgreSQL renames each child so you can see its job, and Ubuntu adds the cluster name 18/main to the title (this comes from the cluster_name setting).

Check it worked: you should see exactly one line without a postgres: title (the postmaster), and the helper processes from the table in "How it works". The three io worker lines appear only on PostgreSQL 18 and later; on PostgreSQL 17 and earlier they are absent.

Step 3: See the same processes from inside PostgreSQL

PostgreSQL keeps a live list of its processes in the system view pg_stat_activity. The column backend_type tells you what each process is, and state tells you what a client backend is doing right now.

Where: on the database server. Open psql as the postgres operating system user, then run the SQL.

sudo -u postgres psql

SELECT pid, backend_type, state
  FROM pg_stat_activity
 ORDER BY pid;

 pid  |         backend_type         | state
------+------------------------------+--------
 1907 | io worker                    |
 1908 | io worker                    |
 1909 | io worker                    |
 1910 | checkpointer                 |
 1911 | background writer            |
 1913 | walwriter                    |
 1914 | autovacuum launcher          |
 1915 | logical replication launcher |
 1966 | client backend               | active
(9 rows)

The PIDs match the ps output from Step 2. The last row is a new process: the backend that was started for your psql session. Its state is active because it is running this very query. Background processes have no state, because they do not run client queries. The postmaster itself is not listed.

Step 4: Prove that one connection is one process

Where: still in the same psql session.

SELECT pg_backend_pid();

 pg_backend_pid
----------------
           1966
(1 row)

Now open a second terminal and look at that PID from Linux (use the number you got):

ps -o pid,ppid,cmd -p 1966

  PID  PPID CMD
 1966  1906 postgres: 18/main: postgres postgres [local] idle

The title follows the pattern user, database, client host, activity: user postgres, database postgres, connected through the local socket ([local]), currently idle (waiting for your next command). Its parent is the postmaster, PID 1906. Quit psql with \q and run the same ps command again: the process is gone, because the backend exits when its client disconnects.

Check it worked: the PPID of your backend equals the postmaster PID from Step 2, and the process disappears after you quit psql.

Hands-on: look at the memory settings

Step 5: Read the main memory parameters

Every setting the server is using is visible in the pg_settings view. The context column is very useful for a DBA: postmaster means the setting can only change with a full server restart, while user means any session can change it for itself.

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

SELECT name, setting, unit, context
  FROM pg_settings
 WHERE name IN ('shared_buffers', 'wal_buffers', 'work_mem',
                'maintenance_work_mem', 'max_connections')
 ORDER BY name;

         name         | setting | unit |  context
----------------------+---------+------+------------
 maintenance_work_mem | 65536   | kB   | user
 max_connections      | 100     |      | postmaster
 shared_buffers       | 16384   | 8kB  | postmaster
 wal_buffers          | 512     | 8kB  | postmaster
 work_mem             | 4096    | kB   | user
(5 rows)

Read setting together with unit. shared_buffers is 16384 units of 8kB, which is 128MB, the documented default. wal_buffers is 512 x 8kB = 4MB: its default value of -1 means "size it automatically" as 1/32 of shared_buffers. work_mem is 4096kB (4MB) and maintenance_work_mem is 65536kB (64MB), both defaults. The three shared settings and max_connections have context postmaster, because shared memory is created once at server start; you will see in Part 5 how to change them and restart.

Check it worked: run SHOW shared_buffers; and you should get the human-readable value 128MB on a fresh installation.

Hands-on: find PostgreSQL's files on disk

Step 6: Ask the server where its files are

On Ubuntu the configuration files are kept outside the data directory, under /etc/postgresql/18/main/. Do not guess paths: ask the running server.

Where: in psql as the postgres user.

SHOW data_directory;
SHOW config_file;
SHOW hba_file;

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

               config_file
-----------------------------------------
 /etc/postgresql/18/main/postgresql.conf
(1 row)

              hba_file
-------------------------------------
 /etc/postgresql/18/main/pg_hba.conf
(1 row)

postgresql.conf holds the server settings (Part 5) and pg_hba.conf controls who may connect (Part 6). The server log on Ubuntu goes to /var/log/postgresql/postgresql-18-main.log, the file shown by pg_lsclusters in Step 1. When anything goes wrong, that log is the first place a DBA looks.

Step 7: List the data directory

The data directory is readable only by the postgres user, so use sudo.

Where: on the database server, as your sudo user in a normal shell (not inside psql).

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

PG_VERSION
base
global
pg_commit_ts
pg_dynshmem
pg_logical
pg_multixact
pg_notify
pg_replslot
pg_serial
pg_snapshots
pg_stat
pg_stat_tmp
pg_subtrans
pg_tblspc
pg_twophase
pg_wal
pg_xact
postgresql.auto.conf
postmaster.opts
postmaster.pid

You only need a few of these today. Part 3 explains every item.

Check it worked: sudo cat /var/lib/postgresql/18/main/PG_VERSION prints 18.

Step 8: Find the file behind a table

Create a small lab database and an orders table, then ask PostgreSQL which file stores it.

Where: in psql as the postgres user.

CREATE DATABASE dba_lab;
\c dba_lab
CREATE TABLE orders (
    order_id  int PRIMARY KEY,
    customer  text NOT NULL,
    amount    numeric(10,2) NOT NULL
);
INSERT INTO orders VALUES
    (1, 'Asha', 2500.00),
    (2, 'Ravi', 1200.50),
    (3, 'Meena', 899.99);

\c dba_lab is a psql meta-command that connects to the new database. Now look up the database's OID (object identifier, an internal number PostgreSQL gives every object) and the table's file path:

SELECT oid, datname FROM pg_database ORDER BY oid;
SELECT pg_relation_filepath('orders');

  oid  |  datname
-------+-----------
     1 | template1
     4 | template0
     5 | postgres
 16384 | dba_lab
(4 rows)

 pg_relation_filepath
----------------------
 base/16384/16385
(1 row)

The path is relative to the data directory. base/16384 is the folder for database dba_lab (its OID is 16384), and 16385 is the file for the orders table. That number is the table's filenode (pg_class.relfilenode). It often equals the table's OID, but commands such as TRUNCATE, VACUUM FULL and REINDEX give the table a new file, so always use pg_relation_filepath() instead of assuming. Your numbers may differ.

Look at the file from Linux. A CHECKPOINT first makes sure the new rows are written from shared buffers to the file.

sudo -u postgres psql -d dba_lab -c "CHECKPOINT;"
sudo ls -l /var/lib/postgresql/18/main/base/16384/16385*

CHECKPOINT
-rw------- 1 postgres postgres 8192 Oct 19 12:48 /var/lib/postgresql/18/main/base/16384/16385

The table is one 8192-byte file: a single 8kB page is enough for three rows. Data files always grow in whole 8kB pages, the same page size used in shared buffers. Later, as the table is vacuumed, a free space map file (16385_fsm) and a visibility map file (16385_vm) appear next to it.

Check it worked: the size is a multiple of 8192 and the owner is postgres.

Step 9: See the write-ahead log

Every change you just made was written to WAL first. The function pg_current_wal_lsn() returns the current position in the WAL (an LSN, log sequence number), and pg_walfile_name() turns a position into the WAL file name.

Where: in psql as the postgres user, connected to dba_lab.

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

 pg_current_wal_lsn |     pg_walfile_name
--------------------+--------------------------
 0/191DCE0          | 000000010000000000000001
(1 row)

Now list the pg_wal folder from Linux:

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

total 16388
-rw------- 1 postgres postgres 16777216 Oct 19 12:48 000000010000000000000001
drwx------ 2 postgres postgres     4096 Oct 19 12:40 archive_status
drwx------ 2 postgres postgres     4096 Oct 19 12:40 summaries

The WAL file named by pg_walfile_name() is there, and it is exactly 16777216 bytes (16MB), the default WAL segment size. PostgreSQL fills one segment, then moves on to the next and recycles old ones after checkpoints. The archive_status folder is used by WAL archiving (Part 19), and the summaries folder (PostgreSQL 17 and later) by incremental backups. Run an INSERT and check pg_current_wal_lsn() again: the number grows with every change. Part 15 explains WAL in depth.

Check it worked: the file name returned by pg_walfile_name() appears in the listing.

Step 10: Clean up the lab database

Where: in psql as the postgres user, connected to a different database (you cannot drop the database you are connected to).

\c postgres
DROP DATABASE dba_lab;

You are now connected to database "postgres" as user "postgres".
DROP DATABASE

Check it worked: SELECT datname FROM pg_database; no longer lists dba_lab, and the folder base/16384 is gone.

Common mistakes and how to fix them

Connecting as the wrong operating system user

Running psql as your own Linux user on a fresh Ubuntu install fails, because no PostgreSQL role with your name exists yet:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "ubuntu" does not exist

Fix: connect as the postgres user with sudo -u postgres psql. Part 7 shows how to create your own roles.

The server is not running

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

This means no server is listening on that socket, not that you were refused. Fix: run pg_lsclusters; if the cluster is down, start it with sudo systemctl start postgresql@18-main and read /var/log/postgresql/postgresql-18-main.log if it does not come up.

Starting postgres by hand as root

PostgreSQL refuses to run as root, by design, and the documentation says the server must be run by the PostgreSQL user account. On Ubuntu you should not start the postgres binary yourself at all. Fix: manage the cluster with sudo systemctl start|stop|restart postgresql@18-main or sudo pg_ctlcluster 18 main start.

Assuming the file name equals the table OID

Scripts that build paths from pg_class.oid break after a TRUNCATE or VACUUM FULL, because those commands create a new file with a new filenode. Fix: always use pg_relation_filepath() or pg_relation_filenode().

Looking for postgresql.conf in the data directory

Many tutorials written for other Linux distributions edit postgresql.conf inside PGDATA. On Ubuntu that file is in /etc/postgresql/18/main/, and edits elsewhere are ignored. Fix: run SHOW config_file; and edit the file it names.

Practice exercise

1. Open two psql sessions as the postgres user. From a third terminal, find both backend processes with ps and match them to rows in pg_stat_activity.
2. Create a table called payments in a test database, insert a few rows, and find the full absolute path of its data file on disk.

Quick reference

# List clusters, their status, data directory and log file
pg_lsclusters

# Start the cluster if it is down
sudo systemctl start postgresql@18-main

# Show all PostgreSQL processes with their parent PID
ps -u postgres -o pid,ppid,cmd

# Connect as the postgres superuser
sudo -u postgres psql

# (in psql) processes as PostgreSQL sees them
#   SELECT pid, backend_type, state FROM pg_stat_activity ORDER BY pid;
# (in psql) PID of your own backend
#   SELECT pg_backend_pid();
# (in psql) memory settings and whether they need a restart
#   SELECT name, setting, unit, context FROM pg_settings
#    WHERE name IN ('shared_buffers','wal_buffers','work_mem','maintenance_work_mem','max_connections');
# (in psql) file locations
#   SHOW data_directory; SHOW config_file; SHOW hba_file;
# (in psql) file of a table, current WAL position and file
#   SELECT pg_relation_filepath('orders');
#   SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());

# Look at one process by PID
ps -o pid,ppid,cmd -p <pid>

# List the data directory and the WAL folder
sudo ls /var/lib/postgresql/18/main
sudo ls -l /var/lib/postgresql/18/main/pg_wal

# Read the server log
sudo tail -n 20 /var/log/postgresql/postgresql-18-main.log

Summary and what's next

• A PostgreSQL DBA keeps the server available, secure, backed up, maintained and fast
• The postmaster listens on port 5432 and starts one backend process per client connection
• Background processes (checkpointer, background writer, walwriter, autovacuum launcher, io workers in PostgreSQL 18) do the housekeeping
• Shared memory (shared_buffers, wal_buffers) is created at server start; work_mem and maintenance_work_mem are per operation
• Data lives in /var/lib/postgresql/18/main, configuration in /etc/postgresql/18/main, the log in /var/log/postgresql
• Every change is written to WAL in pg_wal before the data files, which is what makes crash recovery, backups and replication possible
• pg_lsclusters, ps, pg_stat_activity, pg_settings and pg_relation_filepath() let you see all of this yourself

In Part 2 you will install PostgreSQL 18 on Ubuntu from the official PGDG repository and learn how to start, stop and manage the service.