PostgreSQL Post-Maintenance Verification

How to use this checklist

Immediately after a planned change closes, before anybody stands down.

It exists because three kinds of thing survive a window that appeared to go well: a change that did not take effect, wreckage from something that was cancelled, and a setting somebody altered for the window and has not put back.

Check the effect on a new connection

A check from the session that made the change can report either answer

user-context parameters are read at session start. Existing sessions keep what they had, so the session you ran ALTER SYSTEM in may report the old value, the new value, or something else entirely depending on how it was set.

-- from a NEW connection
SELECT name, setting, source, sourcefile FROM pg_settings WHERE name = 'work_mem';

source and sourcefile matter as much as setting: they tell you which mechanism actually won, which is not always the one you used.

Three kinds of wreckage

A cancelled concurrent index build leaves something that costs writes

SELECT c.relname, i.indisvalid, i.indisready,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid
WHERE NOT i.indisvalid;

`indisready`	Size	Meaning
`false`	0 bytes	Inert; drop when convenient
`true`	non-zero	Maintained by every write, used by nothing

Measured on 18.6: a cancelled build left a 21 MB index that the planner ignored — a Seq Scan where an index scan was expected — while 200,000 inserts grew it to 22 MB. In one estate that cost 30 percent of write latency for six weeks, because everybody believed the build had been rolled back.

A vacuum that ran for hours and reclaimed nothing

Read the output, not the exit status:

tuples: 0 removed, 4821992 remain, 3910442 are dead but not yet removable
removable cutoff: 41880231, which was 14002119 XIDs old when operation ended

Note what is missing: no new relfrozenxid line, because relfrozenxid did not advance. The horizon was pinned for the whole run.

A healthy vacuum ends with 0 are dead but not yet removable and a new relfrozenxid line with a large delta.

A replication slot with no consumer. It retains WAL indefinitely, and the failure arrives days later as a full volume with no obvious connection to this change.

Settings changed for the window

The ones that are routinely left behind:

Setting	Why it was changed	What it costs if left
`synchronous_standby_names`	A standby was being restarted	The durability guarantee, silently
`maintenance_work_mem`	To speed a vacuum	Memory, if set with `ALTER SYSTEM`
`vacuum_cost_delay`	To remove the throttle	I/O impact during normal operation
`temp_file_limit`	An emergency cap	Mysterious query failures later

synchronous_standby_names is the one that matters

A cluster left asynchronous after a window has abandoned the guarantee it was built for. Nothing will remind you, no metric will show it, and the next unplanned failover will lose data nobody expected to lose.

SELECT application_name, state, sync_state FROM pg_stat_replication ORDER BY 1;

Test the negative case if access changed

A change to pg_hba.conf, roles or grants that has only been tested with something that should succeed has not been tested. Loopback trust makes the positive test pass regardless of what the rules say.

Attempt something that should now be refused, from the application’s network path, and confirm the refusal.

Write the record while it is fresh

The change, the old value, the new value, the mechanism, the timings, and the measurement of whether it worked.

The next person to investigate this cluster will find the change and want to know why it was made. This note is the only thing that will tell them, and it is worth ten minutes now rather than an hour of archaeology later.

Where the numbers come from

Every figure is a comparison against the pre-change capture rather than an absolute reading: row counts, object counts, non-default settings, plans for the significant queries, and each standby’s position. A value that looks reasonable and differs from before is the finding this review exists to produce.

Health comes from the application’s monitoring over a period long enough to include its scheduled work, not from the database’s opinion of itself in the ten minutes after the window closed.

Access this needs

A role holding pg_monitor on every affected node and pg_read_all_settings for the parameters the change touched, plus the captured pre-change state from the pre-maintenance review.

A non-privileged connection from the application’s own network path with the real credential and driver, if anything touching access was changed — and a deliberately wrong credential, because a path that has only ever succeeded has not been shown capable of refusing.

Read access to the application’s own monitoring, since database metrics can look perfect while a service is failing.

What the review produces

A dated record naming the reviewer, the change, and the disposition of every item, with the before-and-after comparison attached in full. Record the measured duration of each phase, because the next window is planned from these numbers rather than from an estimate.

Any place where the runbook was wrong goes back into the runbook the same day, while it is still remembered. Anything left undone — a backup not yet retaken, an alert not yet re-enabled, a standby not yet returned to the synchronous set — is assigned an owner and a date before this record is signed.

Sign-off

Reviewer: ________________ Date: ___________
Change owner: _____________ Date: ___________
Service owner: ____________ Date: ___________

Every critical item must pass before the window opens. A failing critical item postpones the window; it is not a risk to carry into it. Record the date, the reviewer, and the name of whoever authorised proceeding with any item unmet.