Upgrades, maintenance and on-call
A working database still needs care: new versions, bloated tables, a slowly filling disk, and alerts at 3 a.m. This lesson shows how a DBA upgrades PostgreSQL safely, keeps tables lean, sees trouble weeks ahead, and answers the common alerts calmly.
By the end you can
- Pick between pg_upgrade, logical replication and dump/restore for a given size and downtime budget
- Rehearse a major upgrade on a restored copy and avoid its usual traps
- Measure table and index bloat, and choose between REINDEX CONCURRENTLY, pg_repack and VACUUM FULL
- Forecast disk growth and alert on days left until 85%
- Follow a runbook for disk filling, connections exhausted, replication lag, stuck sessions and wraparound
The everyday picture
Think of a Mangrove store building. Some upkeep is small: a technician swaps a fridge part in twenty minutes and the store stays open. Every few years comes a full refit with new fridges and wiring, planned for months, practised in an empty unit, and done on the quietest night of the year.
Between refits, empty boxes pile up in the storeroom until it looks full while half of it is air, so someone flattens them. The manager watches how fast the room fills, so she can get more space before festival stock arrives. And the night guard has a folder by the alarm panel: for each alarm, what it means, what to check first, and who to call.
A database needs the same: small patches (minor upgrades), planned refits (major upgrades), clearing clutter (bloat), a forecast of space, and the guard's folder (runbooks).
Minor and major versions, and the support clock
Since PostgreSQL 10, the first number is the major version and the second is the minor version. In 16.4, the major is 16 and the minor is 4.
Minor upgrades contain only bug and security fixes and never change the on-disk format. The project ships one at least every three months. You install the new packages and restart: the replica first, then the primary in a quiet minute or after a switchover. Read the release notes every time, because now and then a minor release asks you to rebuild some indexes. A minor version a year old means a year of known, fixed bugs.
Major upgrades bring new features and change the format of the system catalogs, so a new server cannot open an old data directory directly. Each major version gets fixes for about five years: PostgreSQL 16 (September 2023) is supported until November 2028. Preparing a major upgrade of a busy database takes weeks, so a healthy rhythm is to move every one or two major versions, about a year after each release.
Managed services (RDS, Cloud SQL, Azure Database for PostgreSQL) apply minor upgrades in a maintenance window you choose, and offer major upgrades as a button (usually pg_upgrade underneath, with downtime) or a blue/green switch. Near end of life they force the upgrade or charge extra. Still your job: testing the app and extensions on the new version, the timing, and the rollback plan.