Database Decommissioning

Decommissioning is the phase teams skip, leaving unmaintained databases holding production data, unpatched and forgotten. It deserves a procedure.

1. Prove nothing uses it

Absence of complaints is not evidence.

-- PostgreSQL: connections seen recently, and per-table access counts.
SELECT usename, application_name, client_addr, count(*)
FROM pg_stat_activity GROUP BY 1,2,3;
 
SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables ORDER BY seq_scan + idx_scan DESC;

-- MySQL: which accounts and tables have been used since the last restart.
SELECT user, total_connections, rows_read FROM information_schema.user_statistics;
SELECT table_schema, table_name, rows_read FROM information_schema.table_statistics;

Reset the statistics, wait a full business cycle — including month-end and quarter-end jobs — and check again. Grep the codebase, the reporting tools, the ETL definitions and the scheduled jobs for the connection string.

2. Make it read-only, and wait

Before deleting anything, revoke write access and leave it that way for a period:

ALTER DATABASE legacy_shop SET default_transaction_read_only = on;
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM legacy_app;

Anything that still needs it will fail visibly and reversibly. This step surfaces the forgotten consumer far more reliably than any audit.

3. Take a final, verified archive

pg_dump --format=custom --compress=9 --file=/archive/legacy_shop-final.dump legacy_shop
pg_dumpall --globals-only --file=/archive/legacy_shop-globals.sql

Store it with the retention period the data's legal obligations require, and record who to ask about that if it is not obvious.

4. Stop it, then destroy it

Stop the service and leave the storage in place for a defined cooling-off period — long enough that a missed dependency surfaces while recovery is still trivial. Then destroy deliberately:

• Delete the data volumes and snapshots.
• Remove the backup schedule and expire its retained backups on the intended schedule, not immediately, unless the data must be destroyed.
• Revoke and delete the database credentials, and remove them from the secret manager.
• Remove the monitoring, alerting and dashboards, so nobody investigates alerts for a system that no longer exists.
• Remove firewall rules, DNS records and load balancer entries.
• Remove the infrastructure definitions from source control.

5. Record the decommission

Write down what was retired, when, where the final archive lives, how long it is retained, and who approved it. That record is what makes the archive findable when someone asks for it years later — which is the only reason it was kept.