DBOps

DBOps is an informal name for database operations done the DevOps way: schema changes kept in version control and applied by a pipeline without stalling production, backups that can be replayed to any second, replicas and failover that behave as planned, and queries and connections that stay fast as data grows. Its centre is the component delivery tooling tends to treat as an afterthought, the stateful database: you cannot simply redeploy it or rebuild it from source.

The page is PostgreSQL-first, with MySQL contrasts and Amazon RDS as the managed example: principles carry over to other engines, engine behaviour does not.

Start with: beginners, the glossary and Lab 1; engineers, the lock queue and expand and contract; leaders, metrics and the judgement call. Last verified 2026-10-18 (PostgreSQL 18.6 current, 19 at release candidate 1)^[7].

Databases fail through waiting, copying and slow accumulation.

• A tiny migration takes the API down. GoCardless lost about 15 seconds of API availability when a planned migration waited behind a slow query for a lock and “read/write queries issued by calls to our API piled up behind it”^[8].
• Replication breaks under load. At GitLab.com in January 2017 the secondary “started to lag behind”, then failed because the write-ahead log (WAL, the engine’s change journal) it needed was “already removed from the primary”; while rebuilding it by hand, an engineer wiped the primary’s data directory by mistake^[9].
• Slow-burning limits. PostgreSQL’s transaction IDs (XIDs) are 32-bit, so vacuum must freeze old rows (mark them visible to all) at least every two billion transactions or the server stops assigning new IDs^[10]. Sentry lost “most of the US working day” to this in 2015^[11]; Mandrill about 40 hours in February 2019^[12].
• Delivery friction. DORA calls database changes “often a major source of risk and delay when performing deployments”^[13].

With DBOps, a schema change is a reviewed pull request that waits seconds at most for a lock, a bad write can be undone to the second, and slow counters alert before they become incidents.

• Not DataOps. DataOps runs data pipelines; DBOps runs the engines they read from.
• Not backup policy or disaster recovery. An editorial distinction: engine-level PITR, replication and failover here; copies, retention and the restore-test policy in BackupOps, whose monthly tier-1 restore test is, for a database, this page’s timed PITR drill; invoking DR in DisasterRecoveryOps.
• Not DBA-free. Developers write most migrations, but someone owns engines, upgrades and recovery; managed services move that work rather than remove it (NoOps).

The label has no traceable origin; the practice has documented milestones:

• 2003: Sadalage and Fowler’s Evolutionary Database Design: every change a migration script, DBAs working with developers, a “transition phase” in which old and new access paths both work (rewritten 2016)^[14].
• 2014: Danilo Sato names parallel change, “also known as expand and contract”^[15].
• 2015–2017: GoCardless’s lock-queue write-ups (2015)^[8][16], Stripe’s dual-write migrations^[17], GitLab’s postmortem^[9] and Database Reliability Engineering^[5].
• Since then: online schema-change tools, migration linters, Kubernetes operators and managed blue/green switchovers (tooling).

Mental model: one log, three duties. Every committed write, good or bad, enters the write-ahead log (WAL in PostgreSQL, the binary log in MySQL). That one stream serves three jobs; confusing them causes recovery mistakes.

%%{init: {"flowchart": {"wrappingWidth": 170, "nodeSpacing": 18, "rankSpacing": 30, "padding": 8}}}%%
flowchart TB
  classDef core fill:#e8f3f2,stroke:#16756f,color:#122029
  classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029
  classDef neutral fill:#f1f4f6,stroke:#5b7a87,color:#122029
  C["A committed write, good or bad"]:::neutral --> W["Write-ahead log"]:::core
  W --> D1["1 Crash recovery: replay since the last checkpoint"]:::core
  W --> D2["2 Replication: stream to standbys within milliseconds"]:::core
  W --> D3["3 Archive: base backup plus every WAL segment"]:::core
  D2 --> R2["Fast failover and read scaling, but mistakes are copied too"]:::risk
  D3 --> R3["Point-in-time recovery: replay up to just before the mistake"]:::core

The boundaries below are editorial distinctions of this Atlas, not industry standards.

1. Changes are code: reviewed, versioned migrations applied by a pipeline and recorded in the database^[14][18].
2. Waiting is the danger: every DDL statement has a short lock_timeout and a retry plan^[16].
3. Compatible across one version: the schema serves the running and the next application version, so the application can roll back “without requiring a database rollback”^[13]; the schema only rolls forward^[26].
4. Copies are not recovery: replicas give availability; base backups plus archived WAL, restored on a schedule, give recoverability^[23].
5. Decide the loss before the failure: asynchronous replication loses what was in flight^[27]; fence before promoting^[25].
6. Watch the slow counters: XID age, archive age, slots, bloat and connections, not only CPU.
7. Least privilege by role: the service cannot change its schema; only the migration job holds that right^[28].

Trade-offs: safety versus speed, durability versus write latency, portability versus convenience.

• Engine: server, buffer cache, data files, WAL (media and volumes are StorageOps).
• Change: a migrations directory, a history table (flyway_schema_history, DATABASECHANGELOG)^[18][29], a guard or linter, a runner.
• Availability: standbys, a failover manager (Patroni, an operator such as CloudNativePG, or the cloud service)^[30][31], and one endpoint clients follow.
• Recoverability: base backups and a WAL archive in a separate failure domain (pgBackRest^[32] or managed backups^[33]).
• Access path: roles, a connection pooler, client timeouts.
• Evidence: pg_stat_statements^[34], lock-wait and slow-query logs^[35], replication and archiver views^[24], restore records.

R = responsible, A = accountable, C = consulted, I = informed. In small companies one platform engineer holds the DBA column. A DBA team that writes every migration becomes a queue; one that publishes guardrails and reviews only risky classes scales.

Classify every migration; the class sets review and timing (an Atlas proposal echoing the danger lists in strong_migrations and Squawk^[36][37]):

A logical reference for one PostgreSQL service, whether managed, run by a Kubernetes operator or on VMs with Patroni.

%%{init: {"flowchart": {"wrappingWidth": 150, "nodeSpacing": 16, "rankSpacing": 30, "padding": 8}}}%%
flowchart TB
  classDef core fill:#e8f3f2,stroke:#16756f,color:#122029
  classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029
  classDef decision fill:#fdf3e3,stroke:#c9800f,color:#122029
  classDef neutral fill:#f1f4f6,stroke:#5b7a87,color:#122029
  PR["Pull request: migration plus app change"]:::neutral --> CI["CI, without production secrets: guard, throwaway DB, tests"]:::core
  CI --> DJ["Deploy job in a protected environment: sole holder of the migrator credential"]:::decision
  APP["Service instances: app role, timeouts"]:::neutral --> POOL["Connection pooler"]:::core
  DJ --> PRI["Primary"]:::core
  POOL --> PRI
  PRI --> SB["Standby: failover target"]:::core
  PRI --> ARC["Separate account: base backups and WAL archive, which production cannot delete"]:::core
  PRI --> MON["Metrics: locks, lag, archive age, XID age, top queries"]:::neutral
  MON --> ON["On-call and runbooks"]:::risk

Happy path for a column rename, the hardest everyday change (Lab 2 runs step 3):

1. Plan. Classify each step (expand, risky, contract); write migrations V2 to V7 and the application changes.
2. CI. Guard; all migrations applied to an empty database as the migrator role and timed on a production-sized copy; tests.
3. Expand, backfill, constrain. V2 adds the column and sync trigger; a throttled job backfills; V3 to V6 add the index and NOT NULL. Rollback at each point: redeploy the old application, or drop what was added.
4. Switch. The application reads and writes the new column; the previous version still works because the trigger keeps the old column complete.
5. Contract. After a soak period and with a fresh restore point, V7 drops the trigger and the old column. Rollback: restore only.

Exception path. A migration hits lock_timeout: the file rolls back, on-call finds the blocker (triage queries), the job retries. A concurrent index build fails: drop the INVALID index first (failures). Replica lag rises: the backfill pauses.

Most ALTER TABLE forms take PostgreSQL’s ACCESS EXCLUSIVE lock, which conflicts even with a plain SELECT; locks are “normally held until the end of the transaction”^[20][19]. The statement may need milliseconds; the danger is the wait. While the ALTER queues behind one long transaction, “all queries which arrive after it will queue up behind it”^[16]. MySQL documents the same for metadata locks: a pending one “blocks subsequent transactions on the table”^[21].

%%{init: {"flowchart": {"wrappingWidth": 165, "nodeSpacing": 16, "rankSpacing": 28, "padding": 8}}}%%
flowchart TB
  classDef core fill:#e8f3f2,stroke:#16756f,color:#122029
  classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029
  classDef decision fill:#fdf3e3,stroke:#c9800f,color:#122029
  classDef neutral fill:#f1f4f6,stroke:#5b7a87,color:#122029
  T0["t = 0 s: a report opens a transaction and reads orders (ACCESS SHARE, held to commit)"]:::neutral
  T1["t = 1 s: ALTER TABLE orders requests ACCESS EXCLUSIVE, conflicts and waits"]:::decision
  T2["t = 2 s: API reads request ACCESS SHARE, conflict with the queued request and wait"]:::risk
  Q{"Migration has a lock_timeout?"}:::decision
  A["No: every request waits until the report commits; clients time out"]:::risk
  B["Yes, 2 s: the ALTER aborts at t = 3 s, the queue drains, the deploy retries later"]:::core
  T0 --> T1 --> T2 --> Q
  Q -->|"no"| A
  Q -->|"yes"| B

Lab 1 reproduces both outcomes. How short a timeout? GoCardless’s library defaults to 750 ms, since you “typically don’t want to block the queries from your app for more than a few hundred milliseconds”^[16]; strong_migrations’ example uses 10 seconds^[36]. Pick the longest pause your clients tolerate and set it in the migration’s session, not in postgresql.conf, where it “would affect all sessions”^[22].

From the PostgreSQL 18 and MySQL 8.4 manuals (October 2026); check your version.

One engine difference shapes migration files. PostgreSQL can run most DDL in a transaction block (CREATE INDEX CONCURRENTLY cannot)^[40], so a failed file rolls back; MySQL ends any open transaction before DDL “as if you had done a COMMIT”^[42], so keep one DDL statement per MySQL migration.

DORA advises decoupling database and application changes^[13]; the technique is parallel change^[15], Sadalage and Fowler’s transition phase^[14].

%%{init: {"flowchart": {"wrappingWidth": 300, "nodeSpacing": 14, "rankSpacing": 24, "padding": 8}}}%%
flowchart TB
  classDef core fill:#e8f3f2,stroke:#16756f,color:#122029
  classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029
  classDef decision fill:#fdf3e3,stroke:#c9800f,color:#122029
  S1["1 Expand: add nullable customer_name and a sync trigger. Undo: redeploy the old app"]:::core
  S2["2 Backfill in batches, throttled on replica lag. Undo: stop, rerun later"]:::core
  S3["3 Index concurrently; CHECK NOT VALID, VALIDATE, SET NOT NULL. Undo: drop them"]:::core
  S4["4 Switch the app to read and write customer_name. Undo: previous app still works"]:::core
  S5{{"Gate: has the soak period passed, and is there a fresh restore point?"}}:::decision
  S6["5 Contract: drop trigger and cust_name. Irreversible: restore is the only undo"]:::risk
  S1 --> S2 --> S3 --> S4 --> S5
  S5 -->|"yes"| S6
  S5 -->|"no"| S4

1. SET lock_timeout first, plus statement_timeout for steps that should not scan.
2. One blocking statement per transaction.
3. On a timeout the deploy fails and retries with backoff; three failures mean investigate.

Acceptance: no lock wait in the log longer than the timeout^[35].

1. Walk the primary key (keyset: id > last) in batches of a few thousand rows, each a short transaction under lock_timeout. Never select WHERE customer_name IS NULL … LIMIT: each batch rescans the rows already filled, so total work grows quadratically.
2. Rely on the sync trigger for rows written meanwhile; stay idempotent, so a restart is safe.
3. Pause while replay_lag exceeds a limit^[24]; retry a batch that times out.

Acceptance: zero rows out of step.

1. Build indexes with CREATE INDEX CONCURRENTLY, outside a transaction. Flyway needs executeInTransaction=false in a .conf file beside the script and postgresql.transactional.lock = false^[43][44]; Liquibase, runInTransaction set to false^[45].
2. Check indisvalid: a failed build leaves an invalid index that still costs writes^[40].
3. Add constraints NOT VALID (no scan), then VALIDATE them in a later migration under SHARE UPDATE EXCLUSIVE^[19].
4. For NOT NULL up to version 17, a validated CHECK lets SET NOT NULL skip its scan (Lab 2’s V4–V6). On 18+, add a named NOT NULL customer_name NOT VALID constraint and validate it instead^[19][41].

Build a new structure beside the old and switch. For MySQL, gh-ost copies into a “ghost” table fed from the binary log and can throttle, pause and postpone cut-over^[46]; pt-online-schema-change uses triggers and an atomic RENAME TABLE, needs care with foreign keys and pauses beyond --max-lag^[47]. For PostgreSQL, pgroll serves old and new schema versions through views^[48]; otherwise apply the column pattern above.

PITR combines a physical base backup with the continuous WAL archive since it began; replay can stop at any point^[23]. The target is a time, a named restore point or a transaction, and recovery_target_action pauses (the default), promotes or shuts down on arrival^[49]. Four rules decide whether it works on the day^[23]:

1. The archive command returns zero “if and only if it succeeds” and should refuse to overwrite an existing file.
2. Recovery needs an unbroken WAL sequence back to the start of the base backup.
3. If archiving keeps failing, pg_wal/ fills and the server shuts down with a PANIC when the disk is full: alert on pg_stat_archiver.failed_count and last_archived_time^[24].
4. Archive lag is exposure: it adds to the data lost in a disaster.

Restore into a new data directory or instance, never over the damaged one, with archiving disabled on the copy (Lab 3). Amazon RDS also restores to a new instance^[33]:

• it uploads transaction logs to S3 every five minutes;
• restored volumes load blocks from S3 in the background, so performance “might not be at its fullest” at first;
• retention is 0 to 35 days, and 0 disables automated backups.

After an RDS blue/green switchover, PITR history “doesn’t carry over” to the new instance^[50].

“PostgreSQL streaming replication is asynchronous by default”, and “The amount of data loss is proportional to the replication delay at the time of failover”^[27]. Synchronous commit closes the gap at a latency cost: remote_write survives a standby crash but not its operating-system crash, on waits for the standby’s flush, remote_apply also for replay^[49]. Watch:

• Lag in time (write_lag, flush_lag, replay_lag) and bytes^[24][27]: it sets failover loss and read staleness.
• Replication slots, which keep WAL for absent standbys and “can cause the server to retain so many WAL segments that they fill up the space allocated for pg_wal”; cap them with max_slot_wal_keep_size^[27]. GitLab’s 2017 secondary broke the other way: no WAL archive, and nothing retained the WAL it needed^[9].
• Standby conflicts: DDL locks and vacuum cleanup cancel standby queries after max_standby_streaming_delay; hot_standby_feedback avoids that but “may result in undesirable table bloat” on the primary^[51].

Failover is an external decision. “PostgreSQL does not provide the system software required to identify a failure on the primary”, and a returning old primary must be stopped from acting as one (STONITH)^[25]. Patroni stops PostgreSQL when it cannot renew its leader lock and can arm a watchdog that resets the machine if Patroni itself hangs^[30]. RDS Multi-AZ DB instance failover is “typically 60–120 seconds” and changes the DNS record; AWS recommends a JVM DNS cache TTL of no more than 60 seconds^[52]. Afterwards pg_rewind makes the old primary a standby of the new one, if it has data checksums (an initdb default only from 18) or wal_log_hints; its unreplicated writes do not survive, so copy its data directory first^[53] (reconciling lost writes).

Measure, explain, change one thing, re-measure. pg_stat_statements ranks normalised statements (it needs shared_preload_libraries, a restart and CREATE EXTENSION)^[34]. Rank by total time, not the slowest single query; read the plan with EXPLAIN (ANALYZE, BUFFERS) on a copy; apply the cheapest fix. Indexes slow every write and can prevent heap-only tuple updates, so “indexes that are seldom or never used in queries should be removed”^[54]. Triage query 7 lists candidates. Each server keeps its own counters^[51], which reset when it starts from a crash, a base backup or PITR^[24]: check every replica that serves reads, and how long the counters have run, before dropping an index. Turn on log_min_duration_statement and log_lock_waits, both off by default^[35].

Updates and deletes leave dead row versions. VACUUM makes their space reusable, refreshes planner statistics and freezes old rows; VACUUM FULL takes ACCESS EXCLUSIVE, so avoid it^[10][20]. Autovacuum waits for dead rows above 50 plus 20% of the table (18 adds a 100-million cap)^[55]: a 100-million-row table carries about 20 million before it is cleaned. Watch n_dead_tup and last_autovacuum (triage query 6)^[24]; lower the scale factor per table for large, busy tables only^[10]; run ANALYZE after a backfill or bulk load^[10].

Bloat that is already there: REINDEX INDEX CONCURRENTLY rebuilds an index without blocking writes (outside a transaction block)^[56]. The pg_repack extension rebuilds a table online, taking ACCESS EXCLUSIVE only briefly at start and swap; it needs a primary key and free disk of about twice the table plus indexes^[57]. For time-based retention, partition: dropping a partition “is far faster than a bulk operation” and skips a bulk DELETE’s vacuum work, though it pays off “only when a table would otherwise be very large”^[58].

Each connection costs memory, and past saturation more connections add contention, not throughput. An old PostgreSQL wiki heuristic starts from (core_count × 2) + effective_spindle_count active connections; it is untested on SSDs, so treat it as a first guess to measure^[59]. Poolers cap server connections but restrict session state. RDS Proxy calls this pinning: for PostgreSQL “setting a variable leads to session pinning” (watch DatabaseConnectionsCurrentlySessionPinned)^[60]. Three rules:

1. Know what transaction pooling breaks. PgBouncer’s transaction mode lends a server connection per transaction, so SET/RESET, LISTEN, WITH HOLD cursors, SQL PREPARE and session advisory locks break; protocol-level prepared statements need max_prepared_statements above zero^[38].
2. Set session defaults per role or in the proxy’s initialisation query, not with per-session SET.
3. Run migrations over a direct or session-mode connection. Behind a transaction pooler, SET lock_timeout and the next statement can reach different server connections, so the timeout silently lapses and Flyway’s session advisory lock breaks^[38][44].

PostgreSQL supports each major version “for 5 years after its initial release”, ships minor releases at least quarterly and recommends always running the current minor^[7]. As of October 2026, 13 is unsupported and 14’s support ends with its final release on 2026-11-12^[7]. A minor update needs new binaries and a restart. A major upgrade takes one of three routes:

1. pg_upgrade. Run --check first. With --link you “will not be able to access your old cluster once you start the new cluster”; --clone avoids that. In 18 most planner statistics carry over, and vacuumdb --all --analyze-in-stages --missing-stats-only fills the rest^[61].
2. Logical replication to a new cluster, then a cut-over.
3. A managed blue/green switchover, typically under a minute, but it cannot replicate DDL when PostgreSQL uses logical replication^[50].

Postponing costs money. At the end of standard support, RDS enrols an instance in paid Extended Support (up to three years) if its setting allows; that is the API and CLI default, not the console’s. Otherwise, or when Extended Support ends, RDS upgrades the major version automatically^[62].

No standard or certification defines DBOps. In descending order of authority:

• Engine manuals are normative for behaviour: lock levels, recovery settings and limits for your version.
• Research-backed recommendations: DORA’s database change management capability^[13].
• Practice patterns: evolutionary database design^[14], parallel change^[15], Amazon’s rollback-safe “prepare” then “activate” deployments (its example is a data format, not a schema)^[63], dual writes at scale^[17].
• Reliability engineering: SRE applied to databases in Database Reliability Engineering^[5].

Capabilities as documented in October 2026; editions change, so re-check. Combine one tool where safety lives in review (a linter), one in the runner, and, for big rewrites, one in execution.

Selection criteria: engine support; handling of transactional and non-transactional statements; behaviour after a partial failure; drift detection; the edition you need; plain SQL or XML/YAML changelogs, whichever the team reads better.

Automate what is cheap to get right every time: the guard, the runner, throttled backfills, scheduled restore tests, alerts. Keep a human on decisions that trade data for availability: GitHub’s automated cross-region promotion in 2018 is the cautionary case (use cases)^[66].

Language models can draft migrations and explain plans; their SQL passes the same guard, tests and review. Never give an assistant or agent the migrator role.

One service on a supported PostgreSQL (CI uses 18; the SQL was executed on 13.13), Flyway as runner, GitHub Actions as pipeline. Assumptions: one database per service, owned by it; production credentials only in a protected deployment environment. Layout:

orders-service/
  db/migrations/V1__create_orders.sql ... V6__customer_name_set_not_null.sql
  db/migrations/V3__index_customer_name.sql.conf   # executeInTransaction=false
  db/held-back/V7__contract_drop_cust_name.sql     # moved in after the soak period
  db/roles.sql  db/triage.sql  flyway.toml  migration_guard.py  scripts/test-db.sh
  .github/workflows/db.yml
  .github/CODEOWNERS        # DBA review of .github/ and every path db.yml watches

Runner configuration. Credentials come from environment variables (${env.…}) and defaultSchema puts the history table and unqualified names in orders^[67]; session-level advisory locks let concurrent index builds run^[44]. Example status: parsed as TOML (smol-toml 1.3.1); not executed (Flyway is not installed on the test host); keys from the cited Flyway pages.

[flyway]
locations = ["filesystem:db/migrations"]
defaultSchema = "orders"   # created by roles.sql; holds flyway_schema_history

[flyway.postgresql]
transactional.lock = false

[environments.ci]          # the throwaway service container, roles.sql applied
url = "jdbc:postgresql://localhost:5432/postgres"
user = "orders_migrator"
password = "${env.PGPASSWORD}"

[environments.production]  # values come from the protected environment
url = "${env.DB_URL}"
user = "${env.DB_USER}"
password = "${env.DB_PASSWORD}"

Roles. The service reads and writes but cannot alter or drop tables: that right belongs to the owner and “cannot be granted or revoked”^[28]. The REVOKE on public matters only on PostgreSQL 14 or older and upgraded databases^[68]. Each role’s search_path is the service schema, so migrations and queries need no prefix. Example status: executed on PostgreSQL 13.13 with V1–V6 applied as orders_migrator by the lab runner (standing in for Flyway): orders_app read and wrote the tables unqualified; its DROP TABLE failed (“must be owner of table orders”).

-- Least-privilege roles for one service's schema (PostgreSQL 13+). Run once
-- per database by an administrator; CI runs it on every throwaway database.
-- Passwords or IAM/OIDC authentication are configured outside this file.
CREATE ROLE orders_owner NOLOGIN;                  -- owns every object
CREATE ROLE orders_migrator LOGIN IN ROLE orders_owner;  -- CI migration job only
CREATE ROLE orders_app LOGIN;                      -- the running service
CREATE ROLE orders_ro LOGIN;                       -- reports, replicas
CREATE SCHEMA orders AUTHORIZATION orders_owner;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;        -- already the default from 15
GRANT USAGE ON SCHEMA orders TO orders_app, orders_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO orders_app;
ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders
  GRANT USAGE, SELECT ON SEQUENCES TO orders_app;
ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders
  GRANT SELECT ON TABLES TO orders_ro;
-- Session defaults: unqualified names resolve to the service's schema, objects
-- created by the migrator belong to orders_owner, DDL gives up quickly, and
-- the service cannot hold locks for long.
ALTER ROLE orders_migrator SET search_path = orders;
ALTER ROLE orders_app SET search_path = orders;
ALTER ROLE orders_ro SET search_path = orders;
ALTER ROLE orders_migrator SET role = 'orders_owner';
ALTER ROLE orders_migrator SET lock_timeout = '2s';
ALTER ROLE orders_app SET statement_timeout = '15s';
ALTER ROLE orders_app SET idle_in_transaction_session_timeout = '60s';
-- The default privileges also reach the runner's history table: revoke DML on
-- it from orders_app after the first run if the audit trail must be tamper-proof.

Pipeline. check holds no production credentials. On a throwaway service container^[69] it runs the guard, roles.sql, the migrations as orders_migrator and the tests as orders_app, so a missing grant fails in CI. The tests (test-db.sh, below) are invariant queries that must return zero, like Lab 2’s out_of_step; pgTAP can add unit tests that exercise “views, procedures, functions, rules, or triggers”^[70]. Only migrate references the production environment, so only it can read that environment’s secrets; restrict the environment to main^[71].

The review gate (plan limits as of October 2026). On GitHub Free, Pro and Team, an environment’s required reviewers and its administrator-bypass setting exist only in public repositories. Free also limits environment secrets, deployment-branch rules, protected branches, rulesets and code owners to public repositories^[71][72]. Without required reviewers, every merge to main migrates production, so the merge review is the gate:

1. Protect main with a branch-protection rule or a ruleset that requires code-owner review of .github/ and every path the workflow watches, and dismisses stale approvals.
2. Allow no bypass: branch-protection rules exempt administrators and bypass roles until “Do not allow bypassing the above settings” is on, and a ruleset’s bypass list must stay empty.
3. Keep few admins and holders of “edit repository rules”: they can still change the rule^[72].

Only then does review also stop a pull request from weakening the guard or the tests that judge it. Example status: YAML syntax-checked; not executed. Flyway 13.10.3 with Redgate’s published SHA-1^[67] and actions/checkout v7.0.1 pinned by commit^[73] (October 2026); pin the image by digest.

name: database
on:
  pull_request:
    paths: &watched   # the migrations and everything that checks or applies them
      - "db/**"
      - "flyway.toml"
      - "migration_guard.py"
      - "scripts/**"
      - ".github/workflows/db.yml"
  push:
    branches: [main]
    paths: *watched
permissions:
  contents: read
env:
  FLYWAY_VERSION: "13.10.3"                  # the version you tested
  FLYWAY_SHA1: "6bd32305c1d4489a864590f6952124ec6cb4a866"   # Redgate's .sha1 file
jobs:
  check:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:18
        env:
          POSTGRES_PASSWORD: ci-only-password
        ports: ["5432:5432"]
        options: >-
          --health-cmd pg_isready --health-interval 10s
          --health-timeout 5s --health-retries 5
    env:
      PGHOST: localhost
      PGUSER: postgres
      PGPASSWORD: ci-only-password
      PGDATABASE: postgres
    steps:
      - uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1  # v7.0.1
      - name: Guard new migrations
        run: python3 migration_guard.py db/migrations/*.sql db/held-back/*.sql
      - name: Production roles, plus CI-only passwords
        run: |
          psql -v ON_ERROR_STOP=1 -f db/roles.sql
          psql -c "ALTER ROLE orders_migrator PASSWORD '$PGPASSWORD'" \
               -c "ALTER ROLE orders_app PASSWORD '$PGPASSWORD'"
      - name: Install Flyway (pinned, checksum-verified)   # YAML anchor, reused below
        run: &install-flyway |
          f="flyway-commandline-$FLYWAY_VERSION-linux-x64.tar.gz"
          base=https://download.red-gate.com/maven/release/com/redgate/flyway
          curl -fsSLO "$base/flyway-commandline/$FLYWAY_VERSION/$f"
          echo "$FLYWAY_SHA1  $f" | sha1sum -c -
          tar -xzf "$f" && echo "$PWD/flyway-$FLYWAY_VERSION" >> "$GITHUB_PATH"
      - name: Apply all migrations as the migrator role
        run: flyway -environment=ci migrate
      - name: Tests as the application role
        run: PGUSER=orders_app ./scripts/test-db.sh
  migrate:
    if: github.ref == 'refs/heads/main'
    needs: check
    runs-on: ubuntu-latest
    environment: production
    concurrency: production-db
    steps:
      - uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1  # v7.0.1
      - name: Install Flyway (pinned, checksum-verified)
        run: *install-flyway
      - name: Apply pending migrations
        env:
          DB_URL: ${{ secrets.DB_URL }}     # the primary itself, never a pooler
          DB_USER: ${{ secrets.DB_MIGRATOR_USER }}
          DB_PASSWORD: ${{ secrets.DB_MIGRATOR_PASSWORD }}
        run: flyway -environment=production migrate

Database tests. Example status: executed on PostgreSQL 13.13 as orders_app after V1–V6: three ok lines, no rows left behind; it failed, as intended, with the sync trigger disabled, with an out-of-step row and without the INSERT grant.

#!/usr/bin/env bash
# scripts/test-db.sh: CI database tests, run as orders_app after the migrations.
# Each test is one query that must print 0; anything else fails the job.
# Delete the two cust_name tests in the pull request that ships V7.
set -euo pipefail
t() {  # t <name> <query>: runs inside a transaction that is always rolled back
  got=$(psql -qAtX -v ON_ERROR_STOP=1 -c BEGIN -c "$2" -c ROLLBACK)
  [ "$got" = 0 ] || { echo "FAIL $1: $got" >&2; exit 1; }; echo "ok $1"
}
t old_app_write_synced "WITH r AS (INSERT INTO orders (id, cust_name)
  VALUES (-1, 'probe') RETURNING customer_name)
  SELECT count(*) FROM r WHERE customer_name IS DISTINCT FROM 'probe'"
t out_of_step "SELECT count(*) FROM orders WHERE customer_name IS DISTINCT FROM cust_name"
t invalid_indexes "SELECT count(*) FROM pg_index WHERE NOT indisvalid"

Triage queries for on-call. Example status: executed on PostgreSQL 13.13 during a staged blocking chain (report → migration → api-read); query 6 after Lab 3; query 7 on a primary and a fresh standby, each of which counted only its own scans.

-- 1. Who blocks whom: sessions that wait, and the sessions they wait for.
SELECT pid, application_name AS app, state, wait_event_type AS waits_on,
       pg_blocking_pids(pid) AS blocked_by,
       date_trunc('second', now() - xact_start) AS xact_age,
       left(query, 40) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND pid <> pg_backend_pid()
  AND (cardinality(pg_blocking_pids(pid)) > 0
       OR pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity))
ORDER BY xact_start;
-- 2. Connection use against the limit.
SELECT count(*) AS connections,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,
       current_setting('max_connections')::int AS max_connections
FROM pg_stat_activity WHERE backend_type = 'client backend';
-- 3. Transaction ID age: alert long before 2 billion.
SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_of_2b
FROM pg_database ORDER BY xid_age DESC LIMIT 3;
-- 4. Indexes left INVALID by a failed CREATE INDEX CONCURRENTLY.
SELECT indexrelid::regclass AS invalid_index FROM pg_index WHERE NOT indisvalid;
-- 5. Top statements by total time (needs pg_stat_statements).
SELECT calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 40) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3;
-- 6. Dead rows and the last autovacuum and autoanalyze, per table.
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / greatest(n_live_tup + n_dead_tup, 1)) AS dead_pct,
       date_trunc('second', last_autovacuum) AS last_autovacuum,
       date_trunc('second', last_autoanalyze) AS last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;
-- 7. Indexes not scanned since the counters were last reset: candidates to drop.
--    Counters are per server and reset by crash recovery: check each replica too.
SELECT s.relname AS table_name, s.indexrelname AS index_name, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s JOIN pg_index i USING (indexrelid)
WHERE s.idx_scan = 0 AND NOT i.indisunique   -- unique indexes enforce constraints
ORDER BY pg_relation_size(s.indexrelid) DESC LIMIT 5;

Three labs, executed on 2026-10-18 (Ubuntu 20.04, PostgreSQL 13.13, bash 5.0, Python 3.8). PostgreSQL 13 is unsupported^[7]: use a supported version; the behaviour shown is documented in the current manual^[20][19][23]. Prerequisites: Linux, PostgreSQL server binaries (13+), bash, python3, 1 GB of disk, no root. Cost and risk: local CPU only; the cluster listens only on a Unix socket in a private directory with trust authentication: never point the scripts at a real data directory. Run them from their directory.

Setup. export LAB=$(mktemp -d); set PGBIN to your server’s bin directory and put it on PATH:

#!/usr/bin/env bash
# lab-up.sh: create a throwaway, socket-only PostgreSQL cluster for the labs.
set -euo pipefail
PGBIN="${PGBIN:-/usr/lib/postgresql/13/bin}"     # the major version you have
LAB="${LAB:?set LAB to a new, empty directory you own (mode 0700)}"
mkdir -p "$LAB/sock" "$LAB/wal-archive"
"$PGBIN/initdb" -D "$LAB/primary" -U labadmin --auth=trust --data-checksums \
  > /dev/null
cat >> "$LAB/primary/postgresql.conf" <<CONF
listen_addresses = ''                 # no TCP: Unix socket only
unix_socket_directories = '$LAB/sock'
port = 55432
wal_level = replica
archive_mode = on
archive_command = 'test ! -f $LAB/wal-archive/%f && cp %p $LAB/wal-archive/%f'
CONF
"$PGBIN/pg_ctl" -D "$LAB/primary" -l "$LAB/primary.log" -w start > /dev/null
PGHOST="$LAB/sock" PGPORT=55432 "$PGBIN/psql" -U labadmin -d postgres -Atc \
  "SELECT 'up: ' || current_setting('server_version')"

Expected output: up: 13.13 … (your version). If it fails with “could not start server”, read $LAB/primary.log: a socket path “too long (maximum 107 bytes)” followed by “could not create any Unix-domain sockets” means $LAB is too long; keep it to 87 characters or fewer (a default mktemp -d path has 19). Teardown for all labs: stop every cluster, then delete only $LAB:

#!/usr/bin/env bash
# lab-down.sh: stop every lab cluster, then delete the lab directory only.
set -uo pipefail
PGBIN="${PGBIN:-/usr/lib/postgresql/13/bin}"; LAB="${LAB:?}"
for d in pitr standby primary; do
  [ -f "$LAB/$d/postmaster.pid" ] && "$PGBIN/pg_ctl" -D "$LAB/$d" -m fast -w stop
done
rm -rf -- "$LAB"

Objective: see a millisecond ALTER TABLE block reads, and lock_timeout turn that into a failed, retryable deploy. Prerequisites: the Setup cluster.

#!/usr/bin/env bash
# lab1.sh: a waiting ALTER TABLE blocks readers; lock_timeout bounds the damage.
set -euo pipefail
: "${LAB:?}"
export PGHOST="$LAB/sock" PGPORT=55432 PGUSER=labadmin PGDATABASE=postgres
psql -q -v ON_ERROR_STOP=1 <<'SQL'
SET client_min_messages = warning;
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (id bigint PRIMARY KEY, cust_name text NOT NULL);
INSERT INTO orders SELECT g, 'customer ' || g FROM generate_series(1, 100000) g;
SQL
read_once() {   # an "API read" that reports how long it waited
  local t0; t0=$(date +%s%N)
  PGAPPNAME=api-read psql -qAtc "SELECT count(*) FROM orders" > /dev/null
  echo "api-read finished after $(( ($(date +%s%N) - t0) / 1000000 )) ms"
}
run() {   # $1 = lock_timeout for the migration ('0' means none)
  echo "== migration with lock_timeout = $1"
  PGAPPNAME=report psql -qc \
    "BEGIN; SELECT count(*) FROM orders; SELECT pg_sleep(8); COMMIT;" > /dev/null &
  sleep 1                                 # the report now holds ACCESS SHARE
  PGAPPNAME=migration psql -q -c "SET lock_timeout = '$1'" \
    -c "ALTER TABLE orders ADD COLUMN IF NOT EXISTS customer_name text" \
    2>&1 | sed 's/^/migration: /' &
  sleep 1                                 # the migration now waits in the queue
  read_once &
  sleep 0.5
  psql -Atc "SELECT application_name || ' (pid ' || pid || ') waits on '
             || wait_event_type || ', blocked by pid '
             || array_to_string(pg_blocking_pids(pid), ',')
             FROM pg_stat_activity WHERE wait_event_type = 'Lock'
             ORDER BY application_name"
  wait
}
run 0
psql -qc "ALTER TABLE orders DROP COLUMN customer_name"   # reset
run 2s

Expected output (process ids differ):

== migration with lock_timeout = 0
api-read (pid 3353417) waits on Lock, blocked by pid 3353412
migration (pid 3353412) waits on Lock, blocked by pid 3353402
api-read finished after 6025 ms
== migration with lock_timeout = 2s
api-read (pid 3353560) waits on Lock, blocked by pid 3353547
migration (pid 3353547) waits on Lock, blocked by pid 3353528
migration: ERROR:  canceling statement due to lock timeout
api-read finished after 1014 ms

Validation: the read is blocked by the migration, not by the report, and waits about six seconds, then about one. Troubleshooting: if nothing waits, the report ended too early: raise pg_sleep. Teardown: keep the cluster for Lab 2.

Objective: run the expand half (V1 to V6) while an “old application” writes only the old column, and prove nothing drifts. Prerequisites: the Setup cluster, pgbench, and each file below saved under the name in its first line. One file per migration; V3 has a .conf file containing executeInTransaction=false, as Flyway expects^[43]:

-- ===== db/migrations/V1__create_orders.sql
CREATE TABLE orders (
  id        bigint PRIMARY KEY,
  cust_name text NOT NULL
);
-- ===== db/migrations/V2__expand_customer_name.sql
-- Expand: new nullable column (metadata-only) plus a trigger that keeps both
-- columns in step while old and new application versions run side by side.
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN customer_name text;
CREATE FUNCTION orders_sync_customer_name() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.customer_name := coalesce(NEW.customer_name, NEW.cust_name);
    NEW.cust_name     := coalesce(NEW.cust_name, NEW.customer_name);
  ELSIF NEW.cust_name IS DISTINCT FROM OLD.cust_name THEN
    NEW.customer_name := NEW.cust_name;        -- old version wrote
  ELSIF NEW.customer_name IS DISTINCT FROM OLD.customer_name THEN
    NEW.cust_name := NEW.customer_name;        -- new version wrote
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER orders_sync_customer_name
  BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_sync_customer_name();
-- ===== db/migrations/V3__index_customer_name.sql
-- Runs outside a transaction (see the .conf file): CONCURRENTLY requires it.
SET lock_timeout = '2s';
CREATE INDEX CONCURRENTLY orders_customer_name_idx ON orders (customer_name);
-- ===== db/migrations/V4__customer_name_check_not_valid.sql
-- Brief lock, no table scan: only new and updated rows are checked from now on.
-- PostgreSQL 18+: ADD CONSTRAINT orders_customer_name_nn NOT NULL customer_name
-- NOT VALID here, VALIDATE it in V5, and no V6 is needed.
SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT orders_customer_name_nn
  CHECK (customer_name IS NOT NULL) NOT VALID;
-- ===== db/migrations/V5__validate_customer_name_check.sql
-- Full scan, but under SHARE UPDATE EXCLUSIVE: reads and writes continue.
SET lock_timeout = '2s';
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_name_nn;
-- ===== db/migrations/V6__customer_name_set_not_null.sql
-- The validated CHECK proves there are no NULLs, so no second scan is needed.
SET lock_timeout = '2s';
SET client_min_messages = debug1;  -- lab only: shows that the scan is skipped
ALTER TABLE orders ALTER COLUMN customer_name SET NOT NULL;
RESET client_min_messages;
ALTER TABLE orders DROP CONSTRAINT orders_customer_name_nn;
-- ===== db/held-back/V7__contract_drop_cust_name.sql (not run in the lab)
-- contract-approved: CHG-0000 (placeholder ticket), after a 14-day soak
-- Irreversible. Ship only when no running version reads or writes cust_name.
SET lock_timeout = '2s';
DROP TRIGGER orders_sync_customer_name ON orders;
DROP FUNCTION orders_sync_customer_name();
ALTER TABLE orders DROP COLUMN cust_name;

The runner, the backfill and the “old application” for pgbench:

#!/usr/bin/env bash
# migrate.sh: minimal versioned-migration runner, a teaching aid that mimics
# Flyway or Liquibase. No run lock, and history is written after each file commits:
# never use it in production. Usage: migrate.sh <dir> [target-version]
set -euo pipefail
DIR="${1:?migrations directory}"; TARGET="${2:-999999}"
psql -qX -v ON_ERROR_STOP=1 -c "SET client_min_messages = warning" \
  -c "CREATE TABLE IF NOT EXISTS schema_history (
  version int PRIMARY KEY, script text NOT NULL, checksum text NOT NULL,
  installed_at timestamptz NOT NULL DEFAULT now())"
for name in $(ls "$DIR" | grep -E '^V[0-9]+__.+\.sql$' | sort -V); do
  f="$DIR/$name"; v="${name#V}"; v="${v%%__*}"
  [ "$v" -le "$TARGET" ] || break
  sum=$(sha256sum "$f" | cut -c1-16)
  done_sum=$(psql -qAtX -c "SELECT checksum FROM schema_history WHERE version = $v")
  if [ -n "$done_sum" ]; then            # applied before: it must be unchanged
    [ "$done_sum" = "$sum" ] || { echo "ERROR: $name changed after it ran" >&2; exit 1; }
    continue
  fi
  tx=(--single-transaction)               # one file = one transaction by default
  grep -qx 'executeInTransaction=false' "$f.conf" 2>/dev/null && tx=()
  psql -qX -v ON_ERROR_STOP=1 "${tx[@]}" -f "$f"
  psql -qX -c "INSERT INTO schema_history (version, script, checksum)
               VALUES ($v, '$name', '$sum')"
  echo "applied $name"
done

#!/usr/bin/env bash
# backfill.sh: idempotent, throttled backfill that walks the primary key
# (keyset), so each batch reads only its own id range and costs the same at
# the end of the table as at the start. Rows written meanwhile are kept in
# step by the sync trigger. Pauses while any replica's replay lag > MAX_LAG_S.
set -euo pipefail
BATCH="${BATCH:-5000}"; MAX_LAG_S="${MAX_LAG_S:-5}"; last=0; total=0; fails=0
while :; do
  lag=$(psql -qAtX -c "SELECT coalesce(max(extract(epoch FROM replay_lag)), 0)
                       FROM pg_stat_replication")
  if awk -v l="$lag" -v m="$MAX_LAG_S" 'BEGIN { exit !(l > m) }'; then
    echo "replica lag ${lag}s > ${MAX_LAG_S}s: pausing"; sleep 5; continue
  fi
  hi=$(psql -qAtX -c "SELECT max(id) FROM (SELECT id FROM orders
                      WHERE id > $last ORDER BY id LIMIT $BATCH) b")
  [ -n "$hi" ] || break                    # no rows after $last: done
  if ! n=$(psql -qAtX -v ON_ERROR_STOP=1 -c "SET lock_timeout = '2s'" -c "
      WITH done AS (
        UPDATE orders SET customer_name = cust_name
        WHERE id > $last AND id <= $hi AND customer_name IS NULL RETURNING 1)
      SELECT count(*) FROM done"); then
    fails=$((fails + 1))                   # e.g. a lock timeout: retry the batch
    [ "$fails" -lt 3 ] || { echo "three failures: investigate" >&2; exit 1; }
    sleep 5; continue
  fi
  last=$hi; total=$((total + n)); fails=0
  sleep 0.2                                # leave room for production traffic
done
echo "backfill complete: $total rows updated"

-- old_app.sql: the "old application" for pgbench; it knows only cust_name.
\set id random(1, 200000)
UPDATE orders SET cust_name = 'renamed ' || :id WHERE id = :id;
\set nid random(200001, 9000000)
INSERT INTO orders (id, cust_name) VALUES (:nid, 'new ' || :nid)
  ON CONFLICT (id) DO NOTHING;

The lab driver:

#!/usr/bin/env bash
# lab2.sh: rename orders.cust_name to customer_name under live writes (expand).
set -euo pipefail
: "${LAB:?}"; PGBIN="${PGBIN:-/usr/lib/postgresql/13/bin}"
export PGHOST="$LAB/sock" PGPORT=55432 PGUSER=labadmin PGDATABASE=postgres
psql -qX -c "SET client_min_messages = warning" \
  -c "DROP TABLE IF EXISTS orders, schema_history CASCADE" \
  -c "DROP FUNCTION IF EXISTS orders_sync_customer_name()"
bash migrate.sh db/migrations 1
psql -qX -c "INSERT INTO orders SELECT g, 'customer ' || g
             FROM generate_series(1, 200000) g"
# The "old application": updates and inserts that know only cust_name.
"$PGBIN/pgbench" -n -c 2 -T 25 -f old_app.sql > "$LAB/old_app.log" 2>&1 &
sleep 1
bash migrate.sh db/migrations 2
bash backfill.sh
bash migrate.sh db/migrations 6
wait
grep -E 'number of transactions actually processed|failed' "$LAB/old_app.log" || true
psql -X -c "SELECT count(*) AS rows,
              count(*) FILTER (WHERE customer_name IS DISTINCT FROM cust_name)
                AS out_of_step
            FROM orders"
psql -qAtX -c "SELECT version || ' ' || script FROM schema_history ORDER BY version"
psql -qAtX -c "SELECT 'index valid: ' || indisvalid FROM pg_index
              WHERE indexrelid = 'orders_customer_name_idx'::regclass"

Expected output (excerpt; the long DEBUG line is wrapped; counts vary with timing):

applied V1__create_orders.sql
applied V2__expand_customer_name.sql
backfill complete: 198049 rows updated
applied V3__index_customer_name.sql
applied V4__customer_name_check_not_valid.sql
applied V5__validate_customer_name_check.sql
psql:db/migrations/V6__customer_name_set_not_null.sql:4: DEBUG:  existing constraints
  on column "orders.customer_name" are sufficient to prove that it does not contain nulls
applied V6__customer_name_set_not_null.sql
number of transactions actually processed: 12647
  rows  | out_of_step
--------+-------------
 212641 |           0
index valid: true

Validation: out_of_step is 0 after about 12,600 old-application transactions; the backfill updated fewer than 200,000 rows because the trigger had filled rows the old application touched; the DEBUG line shows SET NOT NULL skipped its scan. Troubleshooting: a lock timeout on V2 to V6 means a long transaction is open (triage queries); rerun safely. … changed after it ran means an applied migration was edited: add a new one instead^[18]. Teardown: keep the cluster for Lab 3.

The pre-merge guard (pattern matching; real linters parse SQL^[37][65]). Example status: executed; V1 to V7 pass (exit 0); a file with a plain CREATE INDEX, a foreign key without NOT VALID, no lock_timeout and an unapproved rename gets four findings (exit 1).

"""Pre-merge guard for PostgreSQL migration files (a teaching aid: real linters
such as Squawk, Atlas or strong_migrations parse SQL; this only pattern-matches).
Usage: python3 migration_guard.py FILE.sql [...]; exit code 1 on any finding."""
import pathlib
import re
import sys

DDL = re.compile(r"^\s*(ALTER|DROP|CREATE\s+(UNIQUE\s+)?INDEX|CREATE\s+TRIGGER)\b",
                 re.I | re.M)
RULES = [
    (re.compile(r"CREATE\s+(UNIQUE\s+)?INDEX\s+(?!CONCURRENTLY)", re.I),
     "build indexes with CREATE INDEX CONCURRENTLY"),
    (re.compile(r"ADD\s+CONSTRAINT\s+\w+\s+(CHECK|FOREIGN\s+KEY)(?![^;]*NOT\s+VALID)",
                re.I | re.S),
     "add CHECK/FOREIGN KEY as NOT VALID, then VALIDATE in a later migration"),
    (re.compile(r"ALTER\s+COLUMN\s+\w+\s+(SET\s+DATA\s+)?TYPE", re.I),
     "type changes usually rewrite the table: expand/contract instead"),
]
DESTRUCTIVE = re.compile(r"DROP\s+(COLUMN|TABLE)|RENAME\s+(COLUMN|TO)", re.I)


def check(path: pathlib.Path) -> list:
    sql = path.read_text()
    found = [msg for rx, msg in RULES if rx.search(sql)]
    first_ddl = DDL.search(sql)
    timeout = re.search(r"SET\s+lock_timeout", sql, re.I)
    if first_ddl and (not timeout or timeout.start() > first_ddl.start()):
        found.append("SET lock_timeout before the first DDL statement")
    if "CONCURRENTLY" in sql.upper():
        conf = path.with_name(path.name + ".conf")
        if not conf.exists() or "executeInTransaction=false" not in conf.read_text():
            found.append("CONCURRENTLY needs executeInTransaction=false (.conf)")
    if DESTRUCTIVE.search(sql) and "contract-approved:" not in sql:
        found.append("destructive change without a 'contract-approved:' line")
    return found


failed = False
for name in sys.argv[1:]:
    for finding in check(pathlib.Path(name)):
        failed = True
        print(f"{name}: {finding}")
print("guard: FAIL" if failed else "guard: OK")
sys.exit(1 if failed else 0)

Objective: measure replication lag under load, then recover from a destructive DELETE that the standby has already applied. Prerequisites: Lab 2 completed (this lab deletes from its orders table).

#!/usr/bin/env bash
# lab3.sh: a replica copies a bad DELETE at once; point-in-time recovery from the
# base backup plus archived WAL brings the rows back.
set -euo pipefail
: "${LAB:?}"; PGBIN="${PGBIN:-/usr/lib/postgresql/13/bin}"
export PGHOST="$LAB/sock" PGPORT=55432 PGUSER=labadmin PGDATABASE=postgres
count() { psql -qAtX -p "$1" -c "SELECT count(*) FROM orders"; }
# 1. A base backup (the PITR starting point) and a streaming standby.
"$PGBIN/pg_basebackup" -D "$LAB/base" -X stream -c fast
"$PGBIN/pg_basebackup" -D "$LAB/standby" -X stream -c fast -R   # -R: standby
printf "port = 55433\narchive_mode = off\n" >> "$LAB/standby/postgresql.conf"
"$PGBIN/pg_ctl" -D "$LAB/standby" -l "$LAB/standby.log" -w start > /dev/null
# 2. Write load on the primary; sample replication lag three times.
"$PGBIN/pgbench" -i -s 5 > /dev/null 2>&1
"$PGBIN/pgbench" -n -c 4 -T 12 > "$LAB/load.log" 2>&1 &
for i in 1 2 3; do
  sleep 3
  psql -X -c "SELECT state, write_lag, flush_lag, replay_lag,
      pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
    FROM pg_stat_replication"
done
wait
# 3. The accident: note the time, then delete half the orders.
T=$(psql -qAtX -c "SELECT now()"); sleep 1
psql -qX -c "DELETE FROM orders WHERE id <= 100000"
psql -qAtX -c "SELECT pg_switch_wal()" > /dev/null   # close and archive the segment
sleep 2
echo "target time: $T"
echo "primary rows: $(count 55432)   standby rows: $(count 55433)"
# 4. Point-in-time recovery into a NEW data directory (never over the original).
cp -a "$LAB/base" "$LAB/pitr"
cat >> "$LAB/pitr/postgresql.conf" <<CONF
port = 55434
archive_mode = off          # a restored copy must not write into the same archive
restore_command = 'cp $LAB/wal-archive/%f %p'
recovery_target_time = '$T'
recovery_target_action = 'promote'
CONF
touch "$LAB/pitr/recovery.signal"
"$PGBIN/pg_ctl" -D "$LAB/pitr" -l "$LAB/pitr.log" -w -t 300 start > /dev/null
until [ "$(psql -qAtX -p 55434 -c 'SELECT pg_is_in_recovery()')" = f ]; do
  sleep 1                                  # wait for replay and promotion
done
echo "recovered rows: $(count 55434)"
grep -E 'starting point-in-time recovery|recovery stopping' "$LAB/pitr.log" \
  | sed 's/^.*LOG: *//'
psql -X -c "SELECT archived_count, failed_count, last_archived_wal,
              now() - last_archived_time AS archive_age
            FROM pg_stat_archiver"

Expected output (excerpt; lag values vary):

   state   |    write_lag    |    flush_lag    |   replay_lag   | bytes_behind
-----------+-----------------+-----------------+----------------+--------------
 streaming | 00:00:00.000026 | 00:00:00.000986 | 00:00:00.00203 |         1408
target time: 2026-10-18 02:12:12.806078+00
primary rows: 112641   standby rows: 112641
recovered rows: 212641
starting point-in-time recovery to 2026-10-18 02:12:12.806078+00
recovery stopping before commit of transaction 43893, time 2026-10-18 02:12:13.867224+00
 archived_count | failed_count |    last_archived_wal     |   archive_age
----------------+--------------+--------------------------+-----------------
             20 |            0 | 000000010000000000000012 | 00:00:04.783863

Validation: the standby lost the rows as fast as the primary; the restored copy has them all, and its log names the transaction it stopped before. Troubleshooting: cp: cannot stat …/00000002.history lines are normal timeline probes; recovery ended before configured recovery target was reached (the server stops) means no archived WAL reaches past the target: check pg_stat_archiver. Teardown: run lab-down.sh.

Three documented cases.

Worked calculation (illustrative numbers). Of 4 failed deployments in a month, 3 involved a schema change: 3 ÷ 4 = 75% DB-related, so invest there. 22 of 25 schema changes went through the pipeline: 88%. Archive age is 7 minutes against a 5-minute target on a busy primary: a breach, because that WAL exists only on the primary. XID age is 1.1 billion: 1.1 ÷ 2.0 = 55% of the limit. The 50% alert fires; freezing should start near 200 million, so something blocks vacuum (mastery question 6).

• Separate roles for owner, migrator, application and read-only use; the service cannot alter or drop its tables^[28] (reference implementation).
• One job holds the migrator credential (environment secrets; required reviewers where the plan offers them, otherwise code-owner review that nobody can bypass)^[71][72].
• Audit trail: history table, pull request and pipeline log; manual DDL breaks it (ComplianceOps).
• Production copies are personal data: anonymise copies used for timing, or keep them inside production controls (PrivacyOps).
• Backups and the WAL archive contain everything, deleted rows included: protect them (BackupOps).

Connections, by Little’s law (L = λW)^[75]. Illustrative: 2,000 queries a second at 4 ms each keep 2,000 × 0.004 = 8 connections busy on average. Twelve pods with client pools of 10 open 120; a transaction-mode pooler can serve the load with about 16–20 server connections, near the wiki’s 2 × 8 cores starting point for cached data^[59]; then tune by measuring latency.

Cost drivers: instance size^[74]; standbys (a Multi-AZ DB instance standby cannot serve reads^[52]); retained WAL and backups; bloat^[51]; unused indexes that slow every write^[54]; Extended Support^[62]. Removing unused indexes and replicas saves money and energy, and, for indexes and synchronous standbys, write latency (FinOps, GreenOps).

Prove the system: a monthly timed PITR drill per tier-1 database; a half-yearly failover game day (reconnection, fencing); a monthly drift check of live schema against the history table; test alerts. Risk signals: manual DDL, a tier-1 drill older than a month, an alert never fired in a test.

Trigger: the primary fails health checks and the failover manager has not acted, or has and must be verified. Owner: the database on-call engineer; the incident commander decides on accepting data loss.

%%{init: {"flowchart": {"wrappingWidth": 200, "nodeSpacing": 14, "rankSpacing": 24, "padding": 8}}}%%
flowchart TB
  classDef core fill:#e8f3f2,stroke:#16756f,color:#122029
  classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029
  classDef decision fill:#fdf3e3,stroke:#c9800f,color:#122029
  A["1 Confirm from two vantage points that the primary is down, not just unreachable from one place"]:::core
  B["2 Fence the old primary: stop it, cut its network or revoke its endpoint"]:::risk
  C{"3 Lag of the best standby at failure within the agreed loss?"}:::decision
  D["4a Promote that standby"]:::core
  E["4b Incident commander chooses: wait for the primary, or promote and accept the loss"]:::risk
  F["5 Repoint clients (endpoint, DNS, pooler) and verify a test write"]:::core
  G["6 Preserve the old primary's data directory, then rebuild it as a standby with pg_rewind"]:::core
  H["7 Restore redundancy and archiving; reconcile lost writes; postmortem"]:::core
  A --> B --> C
  C -->|"yes"| D
  C -->|"no or unknown"| E
  D --> F
  E --> F
  F --> G --> H

Worked through on PostgreSQL with streaming replication:

1. Confirm. pg_isready from two networks; a partition looks like a failure from one side (GitHub 2018).
2. Fence before promoting: PostgreSQL leaves this to you^[25]; Patroni does it on losing the leader lock^[30].
3. Measure the loss: SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn() on each standby; the most advanced loses least^[27].
4. Promote: pg_ctl promote or SELECT pg_promote()^[25].
5. Repoint and test a write; cached DNS can keep clients on the old address^[52].
6. Rebuild: copy the old data directory aside, then pg_rewind (needs checksums or wal_log_hints); unreplicated writes are lost to the rewind^[53] (DR reconciliation).
7. Restore redundancy: new standby, archiving checked in pg_stat_archiver, fresh base backup, postmortem.

• “The ORM generates migrations, so they are safe.” Generators write correct DDL, not safe DDL: a rename is one statement and an outage for every running client.
• Down-migrations as the rollback plan. Undo scripts assume the forward step fully succeeded, and the older schema may not accommodate data written since then^[26][76].
• A global statement_timeout in postgresql.conf: it also kills maintenance; set timeouts per role or session^[22].
• One giant backfill UPDATE: long row locks, replica lag, blocked vacuum. Batch it.
• Timid autovacuum: before its 2015 outage Sentry ran “too high a value for autovacuum_freeze_max_age and the default of 3 autovacuum workers”^[11].
• Alerts nobody owns: Mandrill’s XID monitoring ticket sat in a backlog for about three months^[12].

Use the full practice when a database serves frequently deployed services, holds data you cannot recreate, or must stay writable during changes. Scale it down when a maintenance window is acceptable, the data is a rebuildable cache, or one person owns app and database (keep migrations as code, PITR and lock timeouts anyway). Do not buy a “Database DevOps” platform before the basics work. Hidden costs: owners for the guard’s rules, the restore drill and the upgrade calendar.

Walk this tree before writing a migration; it encodes the lock table.

%%{init: {"flowchart": {"wrappingWidth": 170, "nodeSpacing": 14, "rankSpacing": 26, "padding": 8}}}%%
flowchart TB
  classDef core fill:#e8f3f2,stroke:#16756f,color:#122029
  classDef risk fill:#fbeceb,stroke:#b4332b,color:#122029
  classDef decision fill:#fdf3e3,stroke:#c9800f,color:#122029
  Q1{"Breaks the running app? (rename, drop, type, NOT NULL)"}:::decision
  EC["Expand and contract over several deploys"]:::core
  Q2{"Scans or rewrites under a strong lock?"}:::decision
  D1["Run it with lock_timeout and retries"]:::core
  Q3{"Non-blocking form? (CONCURRENTLY, NOT VALID, INSTANT)"}:::decision
  D2["Use the non-blocking form"]:::core
  Q4{"Small table or agreed window?"}:::decision
  D3["Short window, announced, lock_timeout still set"]:::risk
  D4["Copy-and-swap tool or new table (gh-ost, pt-osc, pgroll)"]:::core
  Q1 -->|"yes"| EC
  Q1 -->|"no"| Q2
  Q2 -->|"no"| D1
  Q2 -->|"yes"| Q3
  Q3 -->|"yes"| D2
  Q3 -->|"no"| Q4
  Q4 -->|"yes"| D3
  Q4 -->|"no"| D4

Where to run the database (weights are this Atlas’s assumptions for a team of 5–30 engineers without dedicated DBAs; score 1–5):

With experienced DBAs and strict extension needs, self-managed can win; with Kubernetes skills but no DBA, the operator often does.

This is this Atlas’s model, not an industry standard.

Migration pull request

• Class stated; breaking changes split across deployments
• SET lock_timeout first; one blocking statement per transaction
• Indexes CONCURRENTLY (non-transactional file); constraints NOT VALID, then VALIDATE
• Timed on a production-sized copy; backfill batched and throttled
• Compatible with the running application; rollback position written down

Before a risky or contract deploy

• No long transactions (triage query 1); replica lag normal
• Restore point verified: archive age small, last drill passed
• On-call aware; not before a weekend or peak

Monthly PITR drill (tier 1)

• Restore to a new instance at a chosen time; record duration and data checks
• Smoke-test the application against the copy, then destroy the copy

Upgrade readiness

• Support end date known^[7]; pg_upgrade --check or a blue/green rehearsal^[61][50]
• Extensions and drivers tested; statistics step planned; rollback route chosen
• Checksum settings match (18’s initdb enables them; pg_upgrade needs a match)^[41]; pg_rewind ready^[53]

MIGRATION PLAN — <service> — <ticket>
Change:          rename orders.cust_name -> customer_name
Class per step:  V2 expand | backfill risky | V3-V6 risky | V7 contract
Size:            rows, table size, measured duration on the copy
Locks:           strongest lock per statement; lock_timeout = 2s; retries = 3
Compatibility:   app versions supported at each step
Rollback:        position after each step; V7 restore-only
Throttle:        batch size, pause when replay_lag > 5 s
Owner / reviewer / window:

RESTORE TEST RECORD — <database> — <date>
Target time:          (UTC)          Base backup used:
Started / consistent / promoted:     (UTC, UTC, UTC)
Duration vs target:                  Data checks (counts, sums):
Problems found:                      Follow-up tickets:
Copy destroyed at:                   Signed off by:

Is DBOps a job title to hire for? Hire for the work; the title appears in some postings^[1] with no common definition.

Do managed databases make DBOps unnecessary? No. You still own schema changes, queries, connections, retention settings and upgrade timing^[33][62].

Is ADD COLUMN safe? A nullable column, or one with a non-volatile default, is a metadata change^[19], but it still takes ACCESS EXCLUSIVE and can queue: set lock_timeout.

Why not one transaction per migration? It is the PostgreSQL default, but locks last until commit^[20], so never combine a brief-lock statement with a long scan; CREATE INDEX CONCURRENTLY cannot run in a transaction at all^[40].

Does this page apply to MySQL? The principles do; the mechanics differ (lock table). MySQL replication is also “asynchronous by default”; semisynchronous mode waits until at least one replica has logged each commit but falls back to asynchronous after a timeout, and Group Replication (single-primary mode) elects a new primary automatically^[77].

• Established: multi-version schemas through views (pgroll)^[48]; logical replication for partitioning and cross-version moves, as Figma did^[74]; Kubernetes operators with continuous backup to object storage^[31]; sharding layers such as Vitess^[78].
• Maturing: declarative schema management with automated lint analysis in CI^[65]; managed blue/green switchovers, still with real limits for PostgreSQL logical replication^[50].
• Speculative: AI agents that apply migrations; the same guard, review and least privilege apply.

Situation (illustrative). A B2B invoicing service must change invoice.amount from integer cents to numeric(12,2) on a 400-million-row table. Option A: a two-hour Sunday maintenance window: stop the service, run ALTER COLUMN amount TYPE numeric(12,2) USING amount / 100.0 (without USING, 1234 cents silently become 1234.00), start the release that reads currency units. Option B: expand and contract over four deployments in two weeks. Assumptions: contracts allow monthly maintenance windows; the rewrite took 70 minutes on a production-sized copy; one engineer has two weeks.

Reasoning. (1) Risk of A: a type change “will normally cause the entire table and its indexes to be rewritten”^[19] under an exclusive lock; 70 measured minutes leave 50 minutes of margin, but the rewrite also writes WAL for the whole table, which standbys and the archive must absorb. (2) Risk of B: two weeks of trigger overhead and dual code paths; a wrong sync trigger corrupts data silently. (3) Rollback: A only by restore (hours); B at any step before contract. (4) What would change the answer: no contractual window, or a rehearsal that does not fit with margin, makes B mandatory; a team unable to verify a sync trigger makes A safer. (5) Fit with the decision tree: it routes breaking changes to expand and contract because a running version would break; the window stops the service, which is what makes A legitimate.

Decision: A, with guardrails: a fresh restore point, a rehearsal that week, standby and archive capacity checked, lock_timeout on the ALTER, a go/no-go at minute 0. Uncertainty: WAL volume and replay speed are estimates until rehearsed; if replay cannot keep up within the window, switch to B.

You can now explain the label and its boundaries, predict which DDL blocks traffic, plan expand and contract with rollback positions, prove PITR, reason about lag, fencing and failover loss, and set up roles, a guarded pipeline and metrics.

“DBOps” is a thin label over a thick craft. The craft has four habits: changes as reviewed code that cannot wait long for a lock; compatibility across one application version, so the database never needs to roll back; recovery proven by timed restores, because replicas copy mistakes; and slow counters (lag, archive age, XID age) watched as closely as CPU. None of it needs a platform purchase; all of it fails quietly until needed. Facts are dated October 2026; re-check versions and limits before relying on them.

All sources opened on 2026-10-18; PostgreSQL pages are the version 18 manual. Ledger: research/dbops-ledger.md.

Changelog. 2026-10-18: first edition; labs, roles, triage queries and guard executed on PostgreSQL 13.13. r1: roles and migrations re-executed, keyset backfill, GitHub plan limits, PostgreSQL 18 NOT NULL, monthly PITR drill, vacuum, attributions. r2: review-gate preconditions, bloat remedies, partitioning, database tests, MySQL FAQ, archive_timeout, steps for dense passages, trust zones in the architecture figure, TOML parsed.

2026-10-18, post-score polish: “deployment-branch rules” and numbered steps in the review gate; the workflow also watches the runner config, guard, tests and itself; test-db.sh and triage query 7 (unused indexes) added and executed; undo wording matched to its sources; socket-path troubleshooting for the labs; shorter decision-tree labels; RDS PITR caveats as a list; date-proof PostgreSQL 14 line; Little’s 1961 paper cited. Checker fixes: the lab hint now quotes the on-screen error and points to primary.log; pgTAP described as optional.