Schema migrations, backups, and restore boundaries

A release includes a data contract

A container image rollback does not reverse committed data or a schema change. Before changing a column, identify the current and candidate application versions that read or write it, migration ownership, lock requirements, and the rollback window.

A practical expand-and-contract sequence is: add a compatible structure, deploy tolerant code, backfill with bounded batches and checks, switch usage, then remove the old structure only when the rollback window closes. This is an application-specific plan, not a zero-downtime guarantee. PostgreSQL documents the lock and rewrite behavior of individual ALTER TABLE operations↗.

Rehearse one additive change and discard it:

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '5s';
ALTER TABLE study.entries ADD COLUMN source_ref text;
SELECT entry_id, source_ref FROM study.entries ORDER BY entry_id;
ROLLBACK;

Expect the three existing rows with null source_ref inside the transaction and no new column afterwards. A nullable addition can still wait on locks. This rehearsal does not prove compatibility with deployed application versions. See modifying tables↗.

Know what a backup includes

pg_dump makes a consistent logical backup of one database. Separate dumps of Accounts and Ledger are not one distributed snapshot. Cluster-wide roles and tablespaces require separate handling. A successful dump exit code alone does not establish recoverability. SQL dump↗, pg_dump↗.

WAL supports crash recovery; retaining a data volume does not configure WAL archiving or PITR. Recovery to a target time requires an appropriate base backup and the necessary WAL chain. This module describes the boundary but does not implement PITR or HA. Continuous archiving and PITR↗.

Define a restore test

The next lab dumps only the teaching study schema, restores into a fresh study_restore database, and checks counts, totals, constraints, and privileges. It uses the same PostgreSQL major version and --no-owner --no-acl deliberately: ownership and grants must then be rebuilt explicitly.

For a real service, also document encryption, access controls, off-host storage, retention, extension and version compatibility, RPO, and measured recovery time. Restoring on the same server demonstrates a logical round trip, not survival of host loss.

Checkpoint

Describe the largest acceptable data loss (RPO) and recovery time (RTO) for a proposed service before selecting a backup schedule. Explain how you would reconcile in-flight MicroBank events across the two databases after recovery. Restoring both files does not necessarily recreate one globally consistent instant.

Reason through the checkpoint

If old code still requires a column, dropping it prevents a simple image rollback even when the new code works. An additive nullable column preserves more compatibility while both versions exist, but still needs lock and application checks. A dump restored on the same machine can prove a logical round trip; it does not prove that an off-host backup survives host loss.

For MicroBank, independently restored databases may disagree about transactions and published/consumed messages. The operating plan therefore needs transaction identifiers, queue/outbox reconciliation rules, and same-case verification in addition to two restore commands. The next lab deliberately proves the smaller, well-defined study schema restore first.