Runbook: Decommission a PostgreSQL Cluster

1 · Prerequisites

Confirm every item is in place before any state change.

• Written authorisation to decommission, from the data owner, naming the cluster and the date
• The retention requirement for the data: how long a final copy must be kept, in what form, and who can authorise its destruction
• The complete list of things that connect to this cluster, including standbys, logical subscribers, monitoring, backup jobs and anything reading a replica
• Knowledge of what this cluster is part of: whether it is a standby of something else, a primary for something else, or a logical replication source
• Storage for the final backup that will outlive the host
• A named owner for the retained copy, because a backup with no owner is deleted by whoever needs the space

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · Enumerate everything connected. SELECT usename, application_name, client_addr, backend_type, count(*) FROM pg_stat_activity GROUP BY 1,2,3,4 ORDER BY 5 DESC; over a period long enough to catch weekly jobs, not a single sample.
• · Check for replication relationships in both directions. SELECT application_name, state FROM pg_stat_replication; shows what replicates from this cluster. SELECT pg_is_in_recovery(); and SHOW primary_conninfo; show whether it replicates from something else.
• · Check for replication slots on this cluster and on any upstream. SELECT slot_name, slot_type, active, wal_status FROM pg_replication_slots; A slot on an upstream primary that exists for this cluster will retain WAL forever once this host is gone. That is the single most common thing left behind by a decommission.
• · Check for logical subscriptions. SELECT subname, subenabled, subconninfo FROM pg_subscription; and, on any upstream, whether a subscription points here.
• · Identify what owns the archive destination. If this cluster archives to shared storage, decommissioning it must not leave orphaned segments, and must not delete segments another cluster needs.
• · Confirm the retention requirement in writing. "We do not need it any more" from one person is not a retention decision. Get the requirement, the duration and the authoriser recorded.
• · Confirm nothing has connected for an agreed quiet period before proceeding, with the server log as the evidence rather than absence of complaints.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1Announce the decommission with a date, and give the announcement time to reach people. A weekly job that connects on Sundays will not appear in a Tuesday sample.
2. 2Take the final backup first, before disabling anything. Both a physical base backup and a pg_dumpall --globals-only plus per-database pg_dump -Fc, because a logical dump can be restored into a different major version and a physical backup cannot.
3. 3Verify the final backup by restoring it. This is the last opportunity, ever. A final backup that has never been restored is a file, and the cluster it came from will not exist to try again.
4. 4Record what the backup contains and where it is: the databases, their sizes, the roles, the restore procedure, the retention date, and the named owner.
5. 5Disable login for the application roles rather than dropping them. ALTER ROLE app_ro NOLOGIN; One statement, reversible in one statement, and it produces an unmistakable error — role "app_ro" is not permitted to log in — for anything you missed.
6. 6Wait, with the roles disabled, for at least one full cycle of the longest scheduled job. This is the step that catches the monthly report nobody remembered.
7. 7Detach any standbys deliberately. Either promote them if they are being kept, or stop them and remove them. A standby left pointing at a host that no longer exists retries silently forever.
8. 8Drop the replication slots this cluster created on any upstream primary. SELECT pg_drop_replication_slot('<slot>'); on the upstream. This is the step whose omission fills somebody else's WAL volume weeks later, with no obvious connection to this work.
9. 9Drop any logical subscriptions on downstream consumers, and confirm with their owners rather than assuming.
10. 10Remove the monitoring and the alerting for this cluster, in the same change. Alerts for a host that no longer exists become noise, and noise is what teaches people to mute the alerts that matter.
11. 11Remove the backup jobs and the archive configuration, and decide explicitly what happens to the existing archive: retained under the retention policy, or deleted with authorisation.
12. 12Stop the cluster cleanly and leave the data directory in place for an agreed period before deleting it. pg_ctl -D <datadir> -m fast stop. The host can be shut down; deleting the data is a separate, later, authorised step.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓The final backup has been restored on another host and its row counts and object counts compared against the source, taken before shutdown.
• ✓The final backup, its inventory, its restore procedure, its retention date and its named owner are recorded somewhere that will outlive this cluster.
• ✓SELECT slot_name, active FROM pg_replication_slots; on every upstream primary shows no slot belonging to this cluster.
• ✓No subscription on any downstream consumer points at this cluster.
• ✓No standby is configured with a primary_conninfo naming this host, checked on the standbys rather than inferred.
• ✓The server log shows no connection attempts during the agreed quiet period with roles disabled.
• ✓Monitoring and alerting for this cluster have been removed, confirmed by the absence of alerts rather than by the change having been made.
• ✓The backup jobs no longer run, and the archive destination is either retained under the policy or deleted with recorded authorisation.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶Until the data directory is deleted, the whole procedure is reversible: ALTER ROLE ... LOGIN;, start the cluster, restore the monitoring.
• ↶That is why disabling roles precedes dropping them, and why stopping the cluster precedes deleting the data directory by an agreed period. Each step is reversible until the next one.
• ↶If a consumer is discovered after the roles were disabled, the error it produces names the cause unambiguously and one statement restores it.
• ↶If a consumer is discovered after the cluster was stopped, starting it again is a matter of minutes provided the data directory still exists.
• ↶If a consumer is discovered after the data directory was deleted, the only path is a restore from the final backup — which is exactly why that backup is verified by restoring it.
• ↶If the archive was deleted and a recovery is later required, the recovery window is bounded by whatever the final backup captured and nothing after it. Record that boundary in the retention note so nobody expects otherwise.

6 · Escalation

When the runbook isn't enough, contact:

• · A consumer is discovered that nobody can identify: escalate to the platform owner rather than proceeding. An unattributed connection to a cluster being decommissioned is an inventory problem, and the decommission is the moment it becomes an outage.
• · The retention requirement is unclear or contested: escalate to the data owner and stop. Data destroyed under an unclear requirement cannot be un-destroyed, and the cost of waiting is a running cluster.
• · This cluster is a logical replication source for a system owned by another team: escalate to that team. Their subscription will fail and their data will stop arriving, possibly without an alert.
• · A replication slot for this cluster exists on an upstream primary you do not administer: escalate to its owner with the slot name and the date. Left in place it fills their WAL volume.
• · The final backup cannot be restored: escalate immediately and do not proceed. There is no second attempt after the cluster is gone.
• · The cluster contains data subject to a legal hold or a regulatory retention requirement: escalate to whoever owns that obligation before deleting anything, including the archive.

References

1. PostgreSQL 18 documentation, Backup and Restore
2. PostgreSQL 18 documentation, pg_dumpall
3. PostgreSQL 18 documentation, Replication Slots
4. PostgreSQL 18 documentation, ALTER ROLE