Lesson 25.1 · PostgreSQL Operations · 26 min read

What an SRE owns in PostgreSQL

Q: Describe PostgreSQL's process model. Why does it matter when operating it?

The postmaster listens on the port and forks one backend process per client connection; background processes (checkpointer, background writer, walwriter, autovacuum launcher, io workers in 18) do shared work, and all of them share memory. It matters because every connection is a process and a slot in maxconnections, so you pool connections instead of opening thousands; because one backend can be ended with pgterminatebackend without touching others, while kill -9 on a backend makes the postmaster restart every connection; and because pgstatactivity shows one row per process - the first place to look in any incident.

In plain words

A restaurant kitchen has one manager at the door and a cook for every table that is seated - even tables that are only reading the menu. The cooks share one big fridge and write every order in a book before they start cooking, so nothing is lost if the lights go out.

PostgreSQL works the same way: the postmaster at the door, one backend process per connection, shared memory as the fridge, and the WAL as the order book. Knowing this picture tells you why too many seated tables is a problem even when nobody is eating.

Why this lesson exists

Almost every service you will run on call sits on a relational database, and in most companies that database is PostgreSQL - on a VM, in Kubernetes, or as a managed service (Azure Database for PostgreSQL, Amazon RDS / Aurora, Cloud SQL). Many teams have no DBA. The developers own the schema and the queries; you own the thing they run on: it has to be up, reachable, fast enough, backed up, replicated, and it has to come back after a bad night. This chapter is that job, on a real PostgreSQL 18 running on this box.

This first lesson is the map: how a Postgres server is built, the short list of ways it takes production down, and the first five commands to run when it pages you. Every item on the list gets its own lesson and its own lab later in the chapter.

What you need to know already: systemd units and journalctl (the systemd chapter), processes and signals (ps, kill), disks and df, and a little SQL (SELECT, WHERE, JOIN, INSERT, UPDATE). No PostgreSQL experience is assumed.

The words you need first

Cluster - in PostgreSQL's own sense: one server instance with one data directory, one port, one set of roles, holding several databases. (Not a cluster of machines.) Ubuntu names them version/name, like 18/main.
Database - a separate namespace inside the cluster. A connection is always to one database; you cannot join tables across databases.
Schema - a folder inside a database (public by default). orders.public.customers is database orders, schema public, table customers.
Role - a user or a group (PostgreSQL has one concept for both). A role that can log in is what other systems call a user.
Postmaster - the main postgres process. It listens on the port and forks one backend process per connection.
Backend - the process serving one client connection, for as long as it stays connected, whether it is doing anything or not.
WAL (write-ahead log) - the journal every change is written to before the data files. Crash recovery, replicas and point-in-time restore are all built on it.
MVCC (multi-version concurrency control) - an UPDATE writes a new row version and leaves the old one behind for transactions that still need it. VACUUM cleans the old versions up later.
Replica (standby) - a second server replaying the primary's WAL, read-only, ready to be promoted.

One process per connection

PostgreSQL is a family of processes, not one program with threads. Here is the server on this box, seen from the shell:

$ ps -u postgres -o pid,ppid,cmd
    PID    PPID CMD
  17385       1 /usr/lib/postgresql/18/bin/postgres -D /var/lib/postgresql/18/main -c config_file=/etc/postgresql/18/mai
  17386   17385 postgres: 18/main: io worker 0
  17387   17385 postgres: 18/main: io worker 1
  17388   17385 postgres: 18/main: io worker 2
  17389   17385 postgres: 18/main: checkpointer
  17390   17385 postgres: 18/main: background writer
  17391   17385 postgres: 18/main: walwriter
  17392   17385 postgres: 18/main: autovacuum launcher
  17393   17385 postgres: 18/main: logical replication launcher

The first line is the postmaster (its parent is PID 1, systemd). Everything else is its child. The background processes are always there:

process	what it does
`io worker 0..2`	new in 18: reads data files ahead of time for the backends (`io_method = worker`)
`checkpointer`	every few minutes flushes all changed pages to the data files and marks a checkpoint in the WAL
`background writer`	trickles changed pages out between checkpoints so backends rarely have to
`walwriter`	flushes WAL buffers to `pg_wal/`
`autovacuum launcher`	decides which tables need VACUUM / ANALYZE and starts workers for them
`logical replication launcher`	starts logical replication workers (idle here)

Now connect a client and look again:

$ sudo -u postgres psql -d orders -c "select pid, usename, datname, backend_type, state from pg_stat_activity order by backend_type"
  pid  | usename  | datname |         backend_type         | state
-------+----------+---------+------------------------------+--------
 17392 |          |         | autovacuum launcher          |
 17390 |          |         | background writer            |
 17389 |          |         | checkpointer                 |
 17415 | postgres | orders  | client backend               | active
 17387 |          |         | io worker                    |
 17386 |          |         | io worker                    |
 17388 |          |         | io worker                    |
 17393 | postgres |         | logical replication launcher |
 17391 |          |         | walwriter                    |
(9 rows)

pg_stat_activity is the view you will read most in this chapter: one row per server process, with what it is doing right now. Your own psql is the client backend with state = active - it is running the very query that lists it. The others are the background processes from ps.

What this model means on call:

Every connection costs a process - several MB of memory, a slot in max_connections (default 100), and work for the OS scheduler. 400 app pods with a pool of 10 each is 4000 processes: the server refuses them long before that with FATAL: sorry, too many clients already. That is why pools and PgBouncer exist (lesson 5).
An idle connection still holds whatever its transaction took. A backend that ran BEGIN; UPDATE ... and then waits for the app to do something keeps its row locks and keeps VACUUM from cleaning up. state = idle in transaction is one of the most dangerous values in that view (lessons 6 and 7).
Killing a backend is a normal tool. pg_cancel_backend(pid) stops its current query, pg_terminate_backend(pid) ends the whole connection. Both are SQL, both need the right privileges, and neither needs a restart. kill -9 on a backend, by contrast, makes the postmaster assume shared memory is corrupt and restart every connection - never do it.

What breaks, and who owns it

When Postgres is the cause of an incident it is almost always one of these. Learn the list; the rest of the chapter is one lesson per line.

symptom on the dashboards	usual cause	lesson
apps log `FATAL: sorry, too many clients already`	pools x replicas > `max_connections`	5
apps log `no pg_hba.conf entry for host` / `password authentication failed`	access rules, a new subnet, a rotated password	4
requests time out, CPU idle, many sessions `active` + `wait_event_type = Lock`	one transaction holding a lock, the rest queued behind it	6
a migration "that takes 2 seconds" takes the site down	`ALTER TABLE` waiting for a lock, and every query queued behind it	10
slow since last week, tables and indexes growing	autovacuum not keeping up, or held back by a long transaction	7
one endpoint slow, CPU high	a query doing a sequential scan; a missing index	8, 9
disk full on the primary	WAL kept for a replica or a slot that is gone; logs; bloat	12, 13
replica behind / stale reads	replay lag, a long query on the replica, the network	13
"restore from backup" does not work	the backup was never tested	11, 12
a setting change "did nothing"	needs a restart, not a reload; or overridden elsewhere	2

What you usually do not own: the data model, the queries themselves, and business data fixes. What you do own even then: knowing which query is the problem and proving it (pg_stat_statements, EXPLAIN), and the safe way to run changes on a live database.

The first five minutes of a Postgres page

The same few questions, in this order, whatever the alert says. Run them now on the healthy box so you know what normal looks like.

1. Is it up and accepting connections?

$ pg_isready
/var/run/postgresql:5432 - accepting connections
$ 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_isready is the check every load balancer and readiness probe uses; it exits 0 (accepting), 1 (rejecting, e.g. still starting up), 2 (no response) or 3 (no attempt made, e.g. bad arguments). pg_lsclusters is Ubuntu's view of every cluster on the box.

2. What are the connections doing?

$ sudo -u postgres psql -c "select state, count(*) from pg_stat_activity where backend_type = 'client backend' group by state"
 state  | count
--------+-------
 active |     1
(1 row)

Normal: mostly idle (pooled connections waiting for work) and a few active. Trouble: active piling up, or anything idle in transaction for more than seconds.

3. Is anything waiting on a lock?

$ sudo -u postgres psql -c "select pid, pg_blocking_pids(pid) as blocked_by, wait_event_type, left(query, 40) as query from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0"
 pid | blocked_by | wait_event_type | query
-----+------------+-----------------+-------
(0 rows)

An empty answer is the healthy answer. Any row is a blocking chain: follow blocked_by to the session at the root (lesson 6).

4. What does the server log say?

$ sudo tail -n 5 /var/log/postgresql/postgresql-18-main.log
2026-09-22 20:00:03.000 UTC [17385] LOG:  listening on IPv6 address "::1", port 5432
2026-09-22 20:00:03.000 UTC [17385] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2026-09-22 20:00:03.000 UTC [17385] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-09-22 20:00:03.000 UTC [17385] LOG:  database system was shut down at 2026-09-22 20:00:03 UTC
2026-09-22 20:00:07.250 UTC [17385] LOG:  database system is ready to accept connections

Every FATAL, ERROR, checkpoint warning, lock wait and replication problem lands there, with a timestamp, the PID and user@database.

5. Is the disk all right?

$ df -h /var/lib/postgresql
Filesystem                         Size  Used Avail Use% Mounted on
/dev/mapper/ubuntu--vg-ubuntu--lv   19G  7.8G  9.4G  46% /
$ sudo du -sh /var/lib/postgresql/18/main/pg_wal
81M	/var/lib/postgresql/18/main/pg_wal

PostgreSQL stops accepting writes - and then stops entirely - when the disk under the data directory or pg_wal fills up. pg_wal growing without bound is its own incident (lesson 13).

Those five fit in a runbook card. The chapter turns each into a reflex.

Which PostgreSQL, and the simulator

$ psql --version
psql (PostgreSQL) 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)
$ sudo -u postgres psql -Atc 'show server_version'
18.3

The packages are Ubuntu 26.04's: PostgreSQL 18.6 (postgresql-18 18.6-0ubuntu0.26.04.1), the same files, paths and service names you get on a real Ubuntu server. (simulator) The SQL engine underneath is PGlite - PostgreSQL 18.3 compiled to WebAssembly, running in your browser - so server_version and select version() say 18.3, and the version string names PGlite. Everything the SQL does (plans, EXPLAIN numbers, VACUUM output, error texts) is real PostgreSQL. The operations around it - several sessions at once, locks between them, max_connections, replicas, WAL archiving, PgBouncer - are a model built to behave like the real server; where it has to simplify, the lab says (simulator).

How PostgreSQL versions work - you will be asked to plan upgrades:

A major version comes out every year, in September or October (18 came out on 25 September 2025). Each major is supported for five years: 18 until November 2030. 14 is the oldest still supported in 2026 (until November 2026).
Minor releases (18.1, 18.2, ... 18.6) come out roughly every three months with bug and security fixes only. They never change the data format: upgrading is installing the new package and restarting. Always run the latest minor.
A major upgrade changes the on-disk format. You move the data with pg_upgrade (minutes, needs both versions installed; Ubuntu wraps it as pg_upgradecluster), pg_dump + restore (slow for large databases), or logical replication into a new cluster (the near-zero-downtime way).

What is new in 18 that you will meet in this chapter: asynchronous I/O (the io worker processes), data checksums on by default in new clusters, EXPLAIN ANALYZE showing buffer counts by default, uuidv7(), OAuth sign-in, and MD5 passwords deprecated in favour of SCRAM.

Quick check

$ pg_isready

The server on this box is running normally. What does pg_isready print?

/var/run/postgresql:5432 - ready
/var/run/postgresql:5432 - accepting connections
PONG
1

Answer

/var/run/postgresql:5432 - accepting connections - It checks the server the way a client would - here over the Unix socket in /var/run/postgresql - and prints the state; the exit code (0) is what probes read.

In an interview: "What does an SRE need to know about a database the team doesn't own?" - how it is built (a postmaster forking one process per connection, shared memory, the WAL), the handful of ways it fails (connections, locks, vacuum, disk/WAL, replication, untested backups), and the first checks: pg_isready, pg_stat_activity by state, pg_blocking_pids, the server log, the disk under the data directory.

Quick check

$ sudo -u postgres psql -Atc "select count(*) > 0 from pg_stat_activity where backend_type = 'checkpointer'"

Only you are connected. Does pg_stat_activity have a row for the checkpointer?

yes: every server process has a row, background processes included
no: it only lists client connections
only while a checkpoint is running

Answer

yes: every server process has a row, background processes included - pg_stat_activity lists every process with its backend_type; count connections with backend_type = 'client backend'.

What you can do now

Name the PostgreSQL processes on a server and say what each does.
Read pg_stat_activity for state and blocking.
Say which lesson of this chapter covers which kind of incident.
Run the five first checks of a Postgres page.

Why it helps

Every PostgreSQL incident starts with the same few questions: is it up, what are the connections doing, is anything waiting on a lock, what does the log say, is the disk all right. This lesson gives you the process model that makes the answers make sense, the map of what breaks, and the five commands to run first.

It also sets expectations about versions and upgrades - minor releases are a package update and a restart, major ones are a project - which comes up in every planning discussion.

Commands in this lesson

ps psql pg_isready pg_lsclusters tail df du

FAQ

Why does each connection cost so much?

Because each one is a whole operating-system process with its own memory, a slot in max_connections and an entry every query must consider when it builds its snapshot. A few hundred are fine; thousands of mostly idle connections make the server slower for everyone, which is why applications pool their connections and large fleets put PgBouncer in front.

What is the difference between a cluster and a database?

In PostgreSQL a cluster is one server instance: one data directory, one port, one set of roles. A database is a namespace inside it. One cluster usually holds several databases (postgres, the application's databases). You connect to one database at a time and cannot join tables across databases.

Why should I never kill -9 a backend?

When a backend dies from SIGKILL, the postmaster cannot know whether it left shared memory half-written, so it terminates every other connection and runs crash recovery. pg_terminate_backend(pid) or a plain kill (SIGTERM) ends just that session cleanly; pg_cancel_backend(pid) only stops its current query.

What does pg_isready actually check?

It connects the way a client would and asks the server whether it accepts connections, without logging in. Exit code 0 means accepting, 1 rejecting (for example still starting up or in recovery without hot standby), 2 no response, 3 no attempt (bad arguments). Load balancers and Kubernetes probes use it for exactly that reason.

Why does the lab say server 19.3 while psql says 18.6?

The packages, paths and psql output are Ubuntu 26.04's PostgreSQL 18.6. The SQL engine underneath is PGlite, PostgreSQL 18.3 in WebAssembly, so select version() and server_version report 18.3 (simulator). On a real server both numbers are the same.

In an interview Mid

Describe PostgreSQL's process model. Why does it matter when operating it?

The postmaster listens on the port and forks one backend process per client connection; background processes (checkpointer, background writer, walwriter, autovacuum launcher, io workers in 18) do shared work, and all of them share memory. It matters because every connection is a process and a slot in max_connections, so you pool connections instead of opening thousands; because one backend can be ended with pg_terminate_backend without touching others, while kill -9 on a backend makes the postmaster restart every connection; and because pg_stat_activity shows one row per process - the first place to look in any incident.

Also asked: What is the WAL and why does PostgreSQL write it before changing data files? · What are the first checks you run when a PostgreSQL alert fires? · How do minor and major PostgreSQL upgrades differ?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.