Automating PostgreSQL, and change management for database change · PostgreSQL

Almost everything in this course can be automated. Some of it should not be.

What to automate

Anything repetitive and reversible:

Cluster provisioning and configuration.
Backups, and the restore tests from lesson XIII-08.
Monitoring and alerting definitions.
Minor version upgrades — lesson XVII-05’s binary swap.
Routine maintenance windows.
Schema migrations, with the safeguards below.

Anything nobody should have to remember:

lock_timeout on the migration role, from lesson XVII-04.
idle_in_transaction_session_timeout on application roles.
Retention that is bounded by the oldest backup, from lesson XIII-05.

What not to automate

Failover decisions on a single important cluster. Lesson XV-04’s arithmetic: the most common HA-caused outage is failing over when nothing was wrong.

Anything destructive without a human. VACUUM FULL, DROP, pg_resetwal, delete_old_cluster.sh.

Responding to corruption. Lesson XVIII-05: almost everything that feels like fixing it destroys evidence.

Killing sessions by rule. A script that terminates anything running over N seconds will eventually terminate the migration, the backup, or the report that matters.

Expand and contract

The pattern that makes schema change reversible. Renaming a column, without a moment where old and new code cannot both run:

-- 1. EXPAND: add the new column. Reversible; nothing reads it yet.
ALTER TABLE users ADD COLUMN email_address text;

-- 2. BACKFILL, in batches (lesson XVII-04)
UPDATE users SET email_address = email
 WHERE id IN (SELECT id FROM users WHERE email_address IS NULL LIMIT 10000);

-- 3. DUAL WRITE: application writes both columns. Deploy, and let it soak.

-- 4. SWITCH READS to the new column. Deploy. Still fully reversible.

-- 5. STOP WRITING the old column. Deploy.

-- 6. CONTRACT: after long enough to be certain.
ALTER TABLE users DROP COLUMN email;

Steps 1 to 5 are reversible at every point, and each is a separate deployment. Only step 6 is not, and by then the column has been unused long enough that dropping it is a decision rather than a risk.

It is slower than renaming the column. It is slower in exchange for never having a moment where a rollback is impossible, and on a system where downtime is expensive that trade is not close.

Making migrations safe by default

-- on the role migrations run as
ALTER ROLE migrator SET lock_timeout = '3s';
ALTER ROLE migrator SET statement_timeout = '30s';

Lesson XVII-04 measured why: a waiting ALTER TABLE blocks every query behind it, so an unbounded lock wait is an outage. Setting it on the role means the least careful migration anyone writes still fails loudly rather than stalling the site.

A checklist worth enforcing in review:

Does it rewrite the table? (lesson XVII-04’s table)
Does it take ACCESS EXCLUSIVE, and for how long?
Is lock_timeout set?
Is any backfill batched?
Is it reversible? If not, is that intended and agreed?
Has it run against a copy of production-sized data?
Can the previous application version run against the new schema?

That last one is what makes deployments independent of migrations, and it is the question expand-and-contract exists to keep answerable.

Configuration management and state are different problems

Configuration management — Ansible, Puppet, Terraform — is good at declared, idempotent state: packages, files, services, postgresql.conf.

It is bad at things that are neither. A cluster’s data is not idempotent, migrations are ordered and one-way, and “converge to the desired state” has no meaning for a database’s contents.

The split that works:

Concern	Tool
Host, packages, `postgresql.conf`, `pg_hba.conf`	Configuration management
Cluster existence and topology	Provisioning, or an operator
Schema	A migration tool with ordered, versioned migrations
Data	Nothing. It is state, not configuration

The failure mode when these are mixed is a configuration management run that “corrects” a setting an operator changed during an incident, or worse, one that tries to converge a data directory.

Note also that ALTER SYSTEM writes postgresql.auto.conf, which configuration management does not know about — so a setting changed during an incident survives the next convergence run invisibly and disagrees with the repository. Lesson XIII-05’s pg_settings.source check is how you find that.

Why database migrations resist the deployment model, and what follows

Application deployment assumes three things that are all false for databases.

That versions are interchangeable. Two application versions can run side by side during a rollout. Two schema versions cannot: there is one schema, and every running application version sees it. That is the constraint expand-and-contract works around, and it is why a migration must be compatible with both the old and new application versions rather than only the new one.

That rollback is symmetric. Deploying version N-1 undoes deploying version N. Reversing a migration is a different migration, which has to be written, and which cannot restore data the forward migration destroyed. A “down migration” that drops the column it added is symmetric; one that reverses a backfill is fiction.

That deployment is fast and atomic. A migration on a large table is neither. It can take hours, holds locks, generates WAL, and can be interrupted — lesson XVII-03’s abandonment question applies to every one of them.

Three practices follow, and they are the whole of database change management.

Migrations must be backward compatible with the currently deployed application. Deploy the migration first, then the code. If the migration requires the new code, you have coupled two things that fail independently.

Migrations must be idempotent or exactly-once. A migration runner that crashes mid-run must be safe to re-run. IF NOT EXISTS clauses and a migration ledger in the database are how that is achieved, and the ledger has to be updated in the same transaction as the change wherever the change is transactional.

Destructive migrations should be separated in time from the code that stops using the thing. The contract step of expand-and-contract is a separate deployment, days or weeks later, precisely so that the window in which a rollback is impossible does not overlap with the window in which a rollback is likely.

What to take from this

Automate the repetitive and reversible; keep judgement manual.
Database change is not application change. A dropped column does not come back with a deployment.
Expand and contract makes every step reversible except the last, and puts the last one weeks later.
Set lock_timeout on the migration role, so safety is the default rather than a habit.
Review for rewrite, lock, batching, reversibility, and whether the previous application version still runs.
Configuration management handles packages and files. Schema needs a migration tool; data needs neither.
ALTER SYSTEM writes a file configuration management does not manage. Check pg_settings.source.

Cross-course references

Ansible for Production Sysadmins — Part XII (Idempotency), Part XXXVI (Drift) and Part XXX (Host targeting and blast radius) cover automating configuration without automating an outage.
Git, CI/CD & GitOps — Part CVI (Change management), Part LVII (Approval gates) and Part LXXV (Drift) cover treating a schema change as a delivery with a review and a record.
Linux for Production Sysadmins — Part LXXIV (Configuration drift) covers detecting the setting somebody changed during an incident and never put back.