Why databases get different rules

Everything else in this part rests on two properties: a run against a converged host is a no-op, and a failed run can be repaired by running it again.

Databases break both, and they break them in a way that is not fixable by writing the tasks more carefully. This lesson is about where the model stops, and about doing less rather than more at that boundary.

Two failures, stated plainly

A schema migration is not idempotent in the way a file is. ALTER TABLE orders ADD COLUMN customer_ref uuid applied twice is an error at best. UPDATE orders SET total = total * 1.2 applied twice is a data corruption that no error reports. There is no “desired state” the statement converges on; it is a transformation of whatever it finds.

A schema migration is not freely reversible. A dropped column’s data is gone. A down migration that recreates the column recreates it empty, and a rollback that restores the schema without the data is a rollback in name only.

Migration frameworks — Flyway, Liquibase, Alembic, Rails, Django — solve the first problem by keeping their own state table recording which migrations have run. That is the right design and Ansible should not attempt to duplicate it. Ansible’s job is to invoke the framework, once, at the right time, with the right preconditions verified.

The second problem is not solved by anything. It is managed, by writing migrations that do not destroy data in the same release that stops using it — the expand-and-contract discipline — and by having a backup you have proven you can restore.

“Restart all database nodes” is the shape of a data-loss incident

This is the sentence to take away if you take one.

A replica and a primary are different machines with the same package list. A play targeting hosts: databases treats them identically, and the two operations are not equivalent:

• Restarting a replica interrupts read traffic and replication. Replication resumes. Nothing is lost.
• Restarting a primary interrupts writes, and depending on configuration can lose recently acknowledged transactions, trigger an automatic failover, or — if it happens across several nodes at once — produce a cluster with no primary at all.

Restarting all of them simultaneously loses quorum in a clustered setup, and in a primary-replica setup produces a window with no writable node while the failover machinery decides what happened.

- name: Ask this node whether it is a standby
ansible.builtin.command:
  argv:
    - psql
    - '-tAc'
    - 'SELECT pg_is_in_recovery();'
become: true
become_user: postgres
register: recovery_state
changed_when: false

- name: Record the observed role
ansible.builtin.set_fact:
  observed_role: "{{ 'replica' if recovery_state.stdout | trim == 't' else 'primary' }}"

- name: Refuse to act when the observed role disagrees with inventory
ansible.builtin.assert:
  that: observed_role == expected_role
  fail_msg: >-
    {{ inventory_hostname }} is inventoried as {{ expected_role }} but
    reports itself as {{ observed_role }}. A failover has happened and
    the inventory has not been updated. Stopping before any change.
  success_msg: '{{ inventory_hostname }} confirmed as {{ observed_role }}'

expected_role comes from group_vars/db_primary.yml and group_vars/db_replicas.yml. The assertion is the whole point: it turns a silent wrong-node action into a stopped play with a message that names the actual situation.

Other engines answer the same question differently — MySQL and MariaDB through replication status, and clustered products through their own membership API — and the shape is identical. Observe, compare against what inventory claims, refuse on disagreement.

- name: Restart database replicas, one at a time
hosts: db_replicas
serial: 1
max_fail_percentage: 0
tasks:
  - name: Confirm this really is a replica
    ansible.builtin.include_tasks: assert-node-role.yml
    vars:
      expected_role: replica

  - name: Restart the database service
    ansible.builtin.systemd_service:
      name: postgresql
      state: restarted

  - name: Wait until replication has caught up before the next node
    ansible.builtin.include_tasks: wait-replication-caught-up.yml

Note the absence: there is no play here that restarts the primary. Promoting, restarting or failing over a primary is a runbook a person executes, with the cluster’s own tooling, watching what happens. That is not a limitation of Ansible; it is a decision about which operations should have a human attached.

Check mode lies about migrations

ansible.builtin.command declares check_mode: partial. In practice, a command task without creates or removes is skipped under --check rather than simulated.

So a --check run of a deployment that includes a migration shows the migration as skipped, which in a wall of green output reads as “this step has no changes”. The largest and least reversible change in the release is the one the dry run says nothing about.

$ ansible-playbook -i inventories/production deploy.yml --check --diff --limit db_primary

TASK [myapp : Install the pinned application version] ***************************
changed: [db01.example.com]

TASK [myapp : Write the application configuration] ******************************
changed: [db01.example.com]

TASK [myapp : Apply pending schema migrations] **********************************
skipping: [db01.example.com]

PLAY RECAP *********************************************************************
db01.example.com  : ok=2  changed=2  unreachable=0  failed=0  skipped=1

run_once is mandatory and insufficient

A migration must run once. run_once: true in a batched play runs it once per batch, verified on ansible-core 2.21.3.

when: inventory_hostname == ansible_play_hosts_all[0] runs it once per play, which is closer and still wrong for a different reason: it runs during batch one, while every remaining batch is serving the old application version against the new schema.

Whether that is survivable is a property of the migration:

The first two are the expand half of expand-and-contract, and they are why the discipline exists. A release that only expands can be deployed in any order. A release that renames or drops has to be split across two releases, with the contraction happening only after every host runs code that does not use the old shape.

- name: Expand the schema
hosts: db_primary
gather_facts: true
tasks:
  - name: Confirm this really is the primary
    ansible.builtin.include_tasks: assert-node-role.yml
    vars:
      expected_role: primary

  - name: Require a verified backup taken for this change
    ansible.builtin.include_tasks: assert-verified-backup.yml

  - name: Apply pending expand migrations
    ansible.builtin.command:
      argv: [/opt/myapp/bin/migrate, 'up', '--tag', 'expand']
    register: migration
    changed_when: "'no migrations to apply' not in migration.stdout"

- name: Roll the application
hosts: appservers
serial: [1, 2, '25%']
max_fail_percentage: 0
tasks:
  - name: Deploy and validate
    ansible.builtin.include_tasks: deploy-one-host.yml

# The contract migration belongs in a LATER release, once every host is
# running code that no longer uses the old schema shape. It is not in
# this playbook, and that is deliberate.

The severity badge on that block is DATA-LOSS-RISK rather than SERVICE-IMPACT, and the badge is doing work: a migration is the only task in this part that can destroy data the backup is the only copy of.

A backup gate that means something

“Take a backup first” is the advice everyone gives, and the way it is usually implemented does not protect anything.

# Not a gate. A hope.
- name: Take a backup before migrating
ansible.builtin.command:
  argv: [/usr/local/bin/backup-database.sh]

The failure modes that task does not catch: the backup wrote a zero-length file; it wrote to a filesystem that is full; it backed up last night’s data because the script uses a cached snapshot; it succeeded and nobody has ever restored one.

- name: Find the most recent backup for this database
ansible.builtin.stat:
  path: '{{ backup_dir }}/{{ myapp_db }}-{{ backup_tag }}.dump'
  get_checksum: false
register: backup_file

- name: Read the restore-test record
ansible.builtin.slurp:
  src: '{{ backup_dir }}/last-verified-restore.json'
register: restore_record
failed_when: false

- name: Refuse to migrate without a fresh, non-trivial, restore-tested backup
ansible.builtin.assert:
  that:
    - backup_file.stat.exists
    - backup_file.stat.size | int > minimum_backup_bytes | int
    - (ansible_date_time.epoch | int) - (backup_file.stat.mtime | int) < 7200
    - (restore_record.content | default('') | b64decode | from_json).age_days
      | default(999) | int <= 30
  fail_msg: >-
    No acceptable backup for {{ myapp_db }}. Required: a dump taken in
    the last two hours, larger than {{ minimum_backup_bytes }} bytes,
    from a backup chain restore-tested within 30 days. Migration
    refused.

Four assertions, each catching a different real failure: the file is missing, the file is empty, the backup is from yesterday, and the backup process has never been proven restorable.

The human gate

Some steps should not proceed without a person. The mechanism has to be one that cannot be satisfied by a scheduler.

- name: Require an explicit change reference for a schema migration
ansible.builtin.fail:
  msg: >-
    Schema migrations require an approved change reference. Re-run with
    -e "change_ref=CHG-12345". This gate exists because a migration is
    not reversible and must not run unattended.
when: change_ref is not defined or change_ref | length == 0

ansible.builtin.pause with a prompt is the obvious alternative and it is the wrong tool here: it blocks forever in a non-interactive context, so a pipeline hangs instead of failing, and someone eventually adds --extra-vars skip_pause=true to fix the hang.

The -e change_ref=... form has the properties you want. It cannot be defaulted in group_vars. It appears in the shell history and in the CI job parameters. And it names, in the run log, the change record that authorised the migration.

Knowledge check