Chapter 25 PostgreSQL Operations
PostgreSQL 18 on Ubuntu from the on-call side: clusters, config and reload vs restart, psql, roles and pg_hba.conf, connections and PgBouncer, MVCC and locks, VACUUM and wraparound, EXPLAIN and indexes, slow queries, safe migrations, backups and PITR, replication and failover, monitoring, and managed PostgreSQL - each with the incident it causes.
In plain words
Imagine a huge, very organised library where hundreds of people borrow and return books at the same time. There are only so many desks (connections), some readers keep a book reserved for hours and block everyone else (locks), returned books pile up until someone shelves them (vacuum), a copy of every change is written in a ledger before it happens (the WAL), and a second library across town copies the ledger so it can take over if the first one burns down (a replica).
PostgreSQL is that library for your company's data. You do not have to be the librarian who designs the catalogue, but you are the one who gets called when the doors stop opening, the queue reaches the street or the ledger fills the basement.
Why it matters on call
Nearly every service an SRE runs stores its state in a relational database, and PostgreSQL is the default choice - on VMs, in Kubernetes and as Azure or AWS managed services. Many teams have no DBA, so the person on call is expected to know why "too many clients" appears after a scale-out, how to find the session blocking everyone, why the disk filled with WAL, and how to restore yesterday's data to the minute.
The chapter turns each of those incidents into a reflex on a real PostgreSQL 18, and the same checks work on managed servers. They are also standard interview material for platform and SRE roles: connection pooling, MVCC and vacuum, safe migrations, backups and PITR, replication and failover.
Lessons
- What an SRE owns in PostgreSQL
- Install: clusters, files, the service and settings
- psql fluency
- Roles, privileges and pg_hba.conf
- Connections, pools and PgBouncer
- Transactions, MVCC and locks
- VACUUM, autovacuum, bloat and XID wraparound
- Indexes and EXPLAIN
- Slow queries: the log and pg_stat_statements
- Safe migrations on a live database
- Backups: pg_dump, pg_restore and testing restores
- WAL, archiving and point-in-time recovery
- Replication and failover
- Monitoring PostgreSQL
- Managed PostgreSQL: what you still own
41 hands-on labs (missions, incidents and drills) run in the terminal: Open this chapter in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.
Questions people ask
Do I need to be a DBA to do this chapter?
No. You need basic SQL (SELECT, WHERE, JOIN, UPDATE) and the Linux chapters. The chapter is about operating PostgreSQL - connections, locks, vacuum, backups, replication, monitoring - not designing schemas or writing complex queries. You will read query plans and add indexes, because finding the query that hurts is part of the job.
Is this a real PostgreSQL?
The SQL runs on PGlite, which is PostgreSQL 18.3 compiled to WebAssembly, so plans, errors, VACUUM output and catalog views are real (simulator). Around it the lab models what an embedded single-connection engine cannot do itself: many sessions and the locks between them, max_connections, PgBouncer, replicas, WAL archiving and recovery, all with the real commands, files and log lines of Ubuntu's PostgreSQL 18.6 packages.
Which version does the chapter use?
PostgreSQL 18, the version Ubuntu 26.04 ships (postgresql-18 18.6). Version 18 was released in September 2025 and is supported until November 2030. Where 18 changed something you will meet - asynchronous I/O workers, data checksums on by default, EXPLAIN showing buffers by default, md5 passwords deprecated, idle_replication_slot_timeout - the lessons say so.
Will this help with managed PostgreSQL like Azure Flexible Server or RDS?
Yes. Everything inside the database - pg_stat_activity, locks, vacuum, EXPLAIN, pg_stat_statements, migrations, roles, replication lag - works the same on managed servers. What changes is how you reach configuration, logs and backups; the last lesson maps every topic of the chapter onto the managed services and lists what remains your job.
What are the five incidents in the chapter?
"Sorry, too many clients" after a scale-out (pool size times replicas), disk full on the primary (an inactive replication slot holding WAL), the two-second migration that took the site down (a lock queue behind ALTER TABLE), everything slow since Tuesday (autovacuum off plus a days-old idle transaction), and the backup that could not be restored (a pipe that hid pg_dump's failure).