Ansible for Databases, In Depth: PostgreSQL, MySQL, MongoDB Lifecycle, Replication, Backups & Schema Management

In a nutshell

Most Ansible you’ve written so far manages things you can throw away and rebuild: a web server, a container, a config file. Databases are different — they hold the one thing you cannot regenerate from a Git repo: your data. Automating them is like renovating the plumbing of an apartment block while the tenants are still living in it, showering, and flushing. You do it carefully, one flat at a time, always with a way to undo.

The good news is that Ansible talks to databases through three purpose-built collections — community.postgresql, community.mysql, and community.mongodb — that give you a tidy module for each job: make a database, make a user, grant a privilege, install an extension, set up a replica, take a backup. You describe the end state (“this database exists, owned by this user, with these grants”) and Ansible makes it so, skipping the work when it’s already done.

The catch is that not everything a database does is safely repeatable. CREATE DATABASE appdb is safe to run a hundred times — it either exists or it doesn’t. ALTER TABLE users ADD COLUMN ... is not — the second run errors out. Half of getting good at database automation is knowing which operations are naturally idempotent (let the module handle them) and which are one-way changes that need a real migration tool or a guard. The other half is operational nerve: run in check mode first, roll changes out one host at a time, keep every credential in Ansible Vault, and never aim a state: absent task at the wrong host.

If you can already write a role and use Ansible Vault, you have what you need to start. By the end you’ll install and secure all three engines, manage users and grants idempotently, stand up replication, wire in backups, and run schema changes without taking the business offline.

Level: Advanced · Time: ~45 min · You’ll need: roles, inventories, handlers, Ansible Vault, and working SQL.

Stateful services are the long pole of every infrastructure project. You can re-pour stateless apps from a Dockerfile in 30 seconds; you cannot re-pour the customer database. This makes database automation simultaneously the most important and the most dangerous category of Ansible work — a buggy command: task on a stateless web tier rolls back with a redeploy, but a buggy community.mysql.mysql_db: state: absent task on the production primary takes down the company.

This lesson covers the three database collections that handle 90% of real production: community.postgresql, community.mysql, and community.mongodb. You’ll learn install/configure/secure flows for each engine, replication topologies (streaming replication for Postgres, group replication for MySQL, replica sets for Mongo), backup tooling integration (pgbackrest, xtrabackup, mongodump), schema migration patterns that work safely against a running database, role/user/grant management, password rotation, and the operational discipline you need to sleep at night while Ansible runs against your databases.

Learning Objectives

By the end you will be able to:

• Install and configure PostgreSQL 15+, MySQL 8.0+, and MongoDB 7+ with Ansible.
• Create databases, roles, users, schemas, and grants idempotently using postgresql_*, mysql_*, and mongodb_* modules.
• Set up streaming replication (Postgres), group replication (MySQL), and replica sets (Mongo) from playbooks.
• Integrate backup tools (pgbackrest, mariadb-backup, mongodump) with scheduled backup playbooks.
• Run schema migrations safely with community.postgresql.postgresql_query, community.mysql.mysql_query, and external migration tools (Flyway, Liquibase, golang-migrate).
• Rotate database passwords without downtime using *_user modules with update_password: on_create.
• Use Ansible Vault to keep database credentials out of git.
• Apply the operational guardrails (check mode, serial: 1, fail-fast on host) that keep playbooks safe against production databases.

Prerequisites

• Tier 1–3 Ansible fluency: roles, inventories, handlers, vault.
• Practical SQL knowledge: CREATE TABLE, GRANT, EXPLAIN, ALTER.
• Familiarity with at least one of Postgres, MySQL, or MongoDB.
• A test VM (1 GB RAM minimum, 4 GB recommended) — kvm/virtualbox/multipass all work.

Mental Model: Databases as Ansible Targets

1. Databases are SSH targets that happen to listen on a database port

The control node SSHes into the database host, becomes root (or the DB superuser), and runs Python modules that import the matching DB driver (psycopg2 for Postgres, pymysql for MySQL, pymongo for Mongo). Auth, sudo, and inventory work like any Linux host. The DB connection itself is local: host: localhost from the perspective of the module.

2. Each engine has its own collection — names line up

community.postgresql ships postgresql_db, postgresql_user, postgresql_privs, postgresql_query, and ~20 others. community.mysql ships mysql_db, mysql_user, mysql_query, etc. community.mongodb ships mongodb_user, mongodb_replicaset, mongodb_oplog, etc. The pattern is consistent: one module per “thing you do with the engine.”

3. Idempotence in DB modules is real but partial

postgresql_db: name=app state=present is idempotent — exists or doesn’t. postgresql_query: query="ALTER TABLE foo ADD COLUMN bar INT" is not idempotent — running it twice fails the second time. For DDL changes, either guard with when: based on a query that checks for the column’s existence, or use a proper migration tool like Flyway/Liquibase orchestrated by Ansible.

4. Replication setup is a one-shot bootstrap problem

Spinning up a replica is a sequence (snapshot primary, ship to replica, configure recovery, start) that’s mostly idempotent the first time and not at all idempotent on the second run. The right pattern is a replica-bootstrap role that runs once per replica and a state.json flag on the host to prevent re-runs.

5. Production databases need playbook discipline more than fancy modules

The hardest part of DB automation isn’t choosing the right module — it’s running the playbook safely: --check first, --diff to preview, serial: 1 for rolling changes, any_errors_fatal: true to stop on the first failure, fail_when: guards to enforce invariants. Every battle-tested team has these in their DB plays; every team that’s had a midnight incident has them after that incident.

6. Every DB module authenticates with login_* parameters, not SSH

Once Ansible is on the database host, it still has to log in to the database engine — a second, separate authentication. That’s what the login_* family does, and it’s near-identical across the three collections:

• login_host / login_port — where the engine listens (default localhost and the engine’s port).
• login_user / login_password — the DB superuser or admin the module connects as.
• login_unix_socket (Postgres, MySQL) — connect over a local Unix socket instead of TCP. On a freshly installed engine this is how you authenticate as the OS superuser via peer/socket auth before any password exists.
• login_db — the database to connect to for the operation.

Two consequences trip people up. First, become_user: postgres (an OS identity) and login_user: postgres (a DB identity) are different things — Postgres’s default peer auth ties them together over the local socket, which is why so many Postgres tasks use become_user: postgres and no login_* at all. Second, MySQL treats login_host: localhost (socket) and login_host: 127.0.0.1 (TCP) as two different accounts — a grant to one is not a grant to the other.

The community.postgresql Collection

Ships modules for the full Postgres lifecycle. The most-used:

Install and bootstrap (RHEL 9 example)

- hosts: postgres_primary
  become: true
  vars:
    pg_version: "15"
  tasks:

    - name: Install Postgres repo
      ansible.builtin.dnf:
        name: "https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm"
        state: present
        disable_gpg_check: true

    - name: Install Postgres server
      ansible.builtin.dnf:
        name:
          - "postgresql{{ pg_version }}-server"
          - "postgresql{{ pg_version }}-contrib"
          - "python3-psycopg2"
        state: present

    - name: Initialize cluster (idempotent — checks if PGDATA exists)
      ansible.builtin.command:
        cmd: "/usr/pgsql-{{ pg_version }}/bin/postgresql-{{ pg_version }}-setup initdb"
        creates: "/var/lib/pgsql/{{ pg_version }}/data/PG_VERSION"

    - name: Configure listen_addresses
      community.postgresql.postgresql_set:
        name: listen_addresses
        value: "*"
      become_user: postgres

    - name: Configure pg_hba (allow internal subnet)
      community.postgresql.postgresql_pg_hba:
        dest: "/var/lib/pgsql/{{ pg_version }}/data/pg_hba.conf"
        contype: host
        users: all
        databases: all
        source: 10.0.0.0/8
        method: scram-sha-256

    - name: Enable and start Postgres
      ansible.builtin.systemd:
        name: "postgresql-{{ pg_version }}"
        enabled: true
        state: started

    - name: Create application database
      community.postgresql.postgresql_db:
        name: appdb
        owner: appuser
        encoding: UTF8
        lc_collate: en_US.UTF-8
        lc_ctype: en_US.UTF-8
        template: template0
      become_user: postgres

    - name: Create application user
      community.postgresql.postgresql_user:
        name: appuser
        password: "{{ vault_appuser_password }}"
        role_attr_flags: "LOGIN"
      become_user: postgres
      no_log: true

    - name: Grant on database
      community.postgresql.postgresql_privs:
        database: appdb
        roles: appuser
        type: database
        privs: CONNECT,TEMPORARY
        state: present
      become_user: postgres

    - name: Install pg_stat_statements extension
      community.postgresql.postgresql_ext:
        name: pg_stat_statements
        db: appdb
      become_user: postgres

Why this is idempotent (mostly). Re-run the play and every declarative task reports ok, not changed: postgresql_db checks pg_database, postgresql_user checks pg_roles, postgresql_privs diffs the live ACL against what you declared, postgresql_ext checks pg_extension. The one blind spot is the password — Postgres stores it hashed, so the module can’t tell your declared password from the stored one. By default postgresql_user therefore treats the password as always-potentially-changed and re-sets it every run. Add update_password: on_create to set the password only when the role is first created; without it, a steady-state convergence run needlessly re-hashes the credential every time (harmless, but it reports a permanent false changed and defeats drift detection). Note the ordering above too: the user task follows the db task even though the db declares owner: appuser — Postgres tolerates declaring an owner that will exist, but in a role you’d create the role first to be safe.

Streaming replication

# On the primary
- name: Create replication user
  community.postgresql.postgresql_user:
    name: replicator
    password: "{{ vault_replicator_password }}"
    role_attr_flags: "LOGIN,REPLICATION"
  become_user: postgres
  no_log: true

- name: Allow replication connections
  community.postgresql.postgresql_pg_hba:
    dest: "/var/lib/pgsql/15/data/pg_hba.conf"
    contype: host
    databases: replication
    users: replicator
    source: "{{ replica_subnet }}"
    method: scram-sha-256

# On the replica (run from a different play targeting replicas)
- name: Stop Postgres on replica before pg_basebackup
  ansible.builtin.systemd:
    name: postgresql-15
    state: stopped

- name: Wipe PGDATA (only on first bootstrap!)
  ansible.builtin.file:
    path: /var/lib/pgsql/15/data
    state: absent
  when: replica_bootstrap | default(false)

- name: Run pg_basebackup
  ansible.builtin.command:
    cmd: >
      /usr/pgsql-15/bin/pg_basebackup -h {{ primary_ip }} -U replicator
      -D /var/lib/pgsql/15/data -P -v -R -X stream -C -S replica_{{ inventory_hostname_short }}
    creates: /var/lib/pgsql/15/data/standby.signal
  become_user: postgres
  environment:
    PGPASSWORD: "{{ vault_replicator_password }}"
  no_log: true

- name: Start Postgres on replica
  ansible.builtin.systemd:
    name: postgresql-15
    state: started

pg_basebackup -R writes the standby.signal file and primary connection info into postgresql.auto.conf, making it a one-shot replica bootstrap. The creates: parameter ensures the task is idempotent — it won’t re-run if standby.signal exists.

Schema migrations

Ansible-native DDL is a dead end past trivial cases. The right pattern is to call a real migration tool from Ansible:

- name: Run Flyway migrations
  ansible.builtin.command:
    cmd: >
      flyway -url=jdbc:postgresql://localhost/appdb
      -user=migrator -password={{ vault_migrator_password }}
      -locations=filesystem:/opt/app/db/migrations migrate
  register: flyway
  changed_when: "'Successfully applied' in flyway.stdout"
  no_log: true

Flyway tracks applied migrations in flyway_schema_history table — running twice is idempotent. Same pattern for golang-migrate, Liquibase, Alembic, etc.

This lesson covers the mechanics of running a migration from a play; for the zero-downtime playbook patterns — expand/contract, online backfills in batches, blue-green cutovers — see Online & blue-green database migrations.

For simple “add a column if missing” tasks, you can stay in pure Ansible:

- name: Check if column exists
  community.postgresql.postgresql_query:
    db: appdb
    query: >
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'users' AND column_name = 'last_login_at'
  register: col_check
  become_user: postgres

- name: Add column if missing
  community.postgresql.postgresql_query:
    db: appdb
    query: ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ
  when: col_check.rowcount == 0
  become_user: postgres

Backup with pgBackRest

- name: Install pgBackRest
  ansible.builtin.dnf:
    name: pgbackrest
    state: present

- name: Configure pgBackRest
  ansible.builtin.copy:
    dest: /etc/pgbackrest/pgbackrest.conf
    content: |
      [global]
      repo1-path=/var/lib/pgbackrest
      repo1-retention-full=2
      log-level-console=info
      log-level-file=detail

      [main]
      pg1-path=/var/lib/pgsql/15/data
    owner: postgres
    group: postgres
    mode: '0640'

- name: Initialize backup stanza
  ansible.builtin.command:
    cmd: pgbackrest --stanza=main --log-level-console=info stanza-create
    creates: /var/lib/pgbackrest/backup/main/backup.info
  become_user: postgres

- name: Schedule daily full backups via systemd timer
  ansible.builtin.copy:
    dest: /etc/systemd/system/pgbackrest-full.service
    content: |
      [Unit]
      Description=pgBackRest full backup
      [Service]
      Type=oneshot
      User=postgres
      ExecStart=/usr/bin/pgbackrest --stanza=main --type=full backup

- name: Schedule daily full backup timer
  ansible.builtin.copy:
    dest: /etc/systemd/system/pgbackrest-full.timer
    content: |
      [Unit]
      Description=Daily pgBackRest full backup
      [Timer]
      OnCalendar=02:00
      Persistent=true
      [Install]
      WantedBy=timers.target

- name: Enable timer
  ansible.builtin.systemd:
    name: pgbackrest-full.timer
    enabled: true
    state: started
    daemon_reload: true

The community.mysql Collection

Mirrors community.postgresql for MySQL/MariaDB.

Install and bootstrap (Ubuntu 22.04)

- hosts: mysql_primary
  become: true
  tasks:

    - name: Install MySQL 8.0 server and Python client
      ansible.builtin.apt:
        name:
          - mysql-server-8.0
          - python3-pymysql
        state: present
        update_cache: true

    - name: Set root authentication via socket (Debian/Ubuntu default)
      community.mysql.mysql_user:
        name: root
        host: localhost
        password: "{{ vault_mysql_root_password }}"
        login_unix_socket: /var/run/mysqld/mysqld.sock
        plugin: caching_sha2_password
      no_log: true

    - name: Drop anonymous users
      community.mysql.mysql_user:
        name: ""
        host_all: true
        state: absent
        login_user: root
        login_password: "{{ vault_mysql_root_password }}"
      no_log: true

    - name: Drop test database
      community.mysql.mysql_db:
        name: test
        state: absent
        login_user: root
        login_password: "{{ vault_mysql_root_password }}"
      no_log: true

    - name: Create application database
      community.mysql.mysql_db:
        name: appdb
        encoding: utf8mb4
        collation: utf8mb4_0900_ai_ci
        state: present
        login_user: root
        login_password: "{{ vault_mysql_root_password }}"
      no_log: true

    - name: Create application user
      community.mysql.mysql_user:
        name: appuser
        password: "{{ vault_appuser_password }}"
        host: "10.%"
        priv: "appdb.*:SELECT,INSERT,UPDATE,DELETE"
        plugin: caching_sha2_password
        state: present
        login_user: root
        login_password: "{{ vault_mysql_root_password }}"
      no_log: true

MySQL Group Replication

MySQL 8.0’s group replication provides automatic primary failover. Setup is involved — three nodes minimum:

- hosts: mysql_cluster
  become: true
  tasks:
    - name: Configure group replication (each node)
      community.mysql.mysql_variables:
        variable: "{{ item.name }}"
        value: "{{ item.value }}"
        login_user: root
        login_password: "{{ vault_mysql_root_password }}"
      loop:
        - { name: server_id, value: "{{ groups['mysql_cluster'].index(inventory_hostname) + 1 }}" }
        - { name: gtid_mode, value: "ON" }
        - { name: enforce_gtid_consistency, value: "ON" }
        - { name: binlog_format, value: "ROW" }
        - { name: binlog_checksum, value: "NONE" }
      no_log: true

For full group-replication setup, the practical answer is: use a tool like Vitess, Orchestrator, or MySQL InnoDB Cluster (which provides mysqlsh as a higher-level orchestration tool) rather than building it by hand in Ansible. Ansible’s job is to install MySQL, drop the cluster definition file, and run mysqlsh --execute "dba.createCluster(...)" once.

Backup with xtrabackup / mariabackup

- name: Install xtrabackup
  ansible.builtin.apt:
    name: percona-xtrabackup-80
    state: present

- name: Run a full backup
  ansible.builtin.command:
    cmd: >
      xtrabackup --backup --target-dir=/var/backup/mysql/{{ ansible_date_time.date }}
      --user=backup --password={{ vault_backup_password }} --no-lock
    creates: "/var/backup/mysql/{{ ansible_date_time.date }}/xtrabackup_info"
  no_log: true

The creates: ensures idempotence within a single day. For a production scheduler, use a systemd timer like the pgBackRest example above.

The community.mongodb Collection

Smaller than the Postgres/MySQL collections but covers the essentials.

Install and bootstrap

- hosts: mongo_replica_set
  become: true
  tasks:

    - name: Add MongoDB repo (Ubuntu)
      ansible.builtin.apt_repository:
        repo: "deb [arch=amd64 signed-by=/etc/apt/keyrings/mongodb.gpg] https://repo.mongodb.org/apt/ubuntu jammy/mongodb-org/7.0 multiverse"
        state: present
        filename: mongodb-org-7

    - name: Install MongoDB
      ansible.builtin.apt:
        name:
          - mongodb-org
          - python3-pymongo
        state: present

    - name: Configure replica set name
      ansible.builtin.lineinfile:
        path: /etc/mongod.conf
        regexp: '^#?replication:'
        line: |
          replication:
            replSetName: "rs0"

    - name: Bind to 0.0.0.0 (cluster needs network access)
      ansible.builtin.lineinfile:
        path: /etc/mongod.conf
        regexp: '^  bindIp:'
        line: '  bindIp: 0.0.0.0'

    - name: Restart mongod
      ansible.builtin.systemd:
        name: mongod
        state: restarted

    - name: Initialize replica set (only on first node)
      community.mongodb.mongodb_replicaset:
        login_host: localhost
        replica_set: rs0
        members:
          - "{{ groups['mongo_replica_set'][0] }}:27017"
          - "{{ groups['mongo_replica_set'][1] }}:27017"
          - "{{ groups['mongo_replica_set'][2] }}:27017"
      run_once: true

    - name: Wait for replica set to elect primary
      community.mongodb.mongodb_status:
        login_host: "{{ groups['mongo_replica_set'][0] }}"
        replica_set: rs0
      register: rs_status
      until: rs_status.replicaset.members | selectattr('state','equalto','PRIMARY') | list | length == 1
      retries: 30
      delay: 5
      run_once: true

    - name: Create application user (only on primary)
      community.mongodb.mongodb_user:
        login_host: "{{ groups['mongo_replica_set'][0] }}"
        replica_set: rs0
        database: admin
        name: app
        password: "{{ vault_mongo_app_password }}"
        roles:
          - { db: "appdb", role: "readWrite" }
        state: present
      no_log: true
      run_once: true

Backup with mongodump

- name: Run a logical dump (small DBs only)
  ansible.builtin.command:
    cmd: >
      mongodump --host=rs0/{{ groups['mongo_replica_set'][0] }}:27017
      --username=backup --password={{ vault_backup_password }}
      --authenticationDatabase=admin
      --out=/var/backup/mongo/{{ ansible_date_time.date }}
      --gzip
    creates: "/var/backup/mongo/{{ ansible_date_time.date }}"
  no_log: true

For larger Mongo clusters use mongodump with --oplog for point-in-time, or commercial tools like Ops Manager.

Connections, credentials, and Vault

Every example above quietly did two things right that deserve to be pulled into the open: it authenticated with login_* parameters, and it kept passwords out of the codebase with Vault plus no_log. Get these right and database automation is safe; get them wrong and you either can’t connect or you leak the crown jewels into a CI log.

Where credentials come from

Never write a database password as a literal in a playbook. Store it encrypted with Ansible Vault and reference it as a variable. The community convention is a two-file split — the real secret lives in a vault_-prefixed name inside an encrypted file, and a plaintext vars file indirects to it:

# group_vars/postgres/vault.yml   (encrypt: ansible-vault encrypt group_vars/postgres/vault.yml)
vault_appuser_password: "S3cr3t-App-Passw0rd"
vault_replicator_password: "S3cr3t-Repl-Passw0rd"

# group_vars/postgres/vars.yml    (plaintext — indirects to the encrypted var)
appuser_password: "{{ vault_appuser_password }}"
replicator_password: "{{ vault_replicator_password }}"

Tasks reference the plain appuser_password, and a single grep -r vault_ group_vars/ audit shows you every secret in the repo. See Ansible Vault & vault IDs for multi-environment vault IDs and rekeying.

no_log is not optional

Vault protects a secret at rest in your repo. It does nothing at runtime: the moment a task executes, the value is decrypted and handed to the module, and Ansible will happily print it in verbose (-vvv) output, in --diff, or in a failed task’s result dump. Every task that touches a credential needs no_log: true:

- name: Create application user
  community.postgresql.postgresql_user:
    name: appuser
    password: "{{ appuser_password }}"
    role_attr_flags: LOGIN
    update_password: on_create
  become_user: postgres
  no_log: true          # without this, -vvv or a failure prints the password

The trade-off: no_log also hides the task’s diagnostics, so when a credential task fails you’ll see the output has been hidden due to the fact that 'no_log: true' and nothing else. Debug by temporarily removing no_log on a throwaway host with a throwaway password — never on production.

Connecting to managed databases (RDS, Cloud SQL, Atlas)

You don’t always have SSH to the database host — with RDS, Cloud SQL, or Mongo Atlas there is no host to SSH into. Run the DB modules locally and let the Python driver reach the endpoint over the network:

- hosts: localhost
  connection: local
  gather_facts: false
  tasks:
    - name: Create a database on RDS PostgreSQL
      community.postgresql.postgresql_db:
        name: appdb
        login_host: mydb.abc123.us-east-1.rds.amazonaws.com
        login_user: "{{ rds_admin_user }}"
        login_password: "{{ rds_admin_password }}"
        ssl_mode: require
      no_log: true

Here become is irrelevant (nothing runs on the DB host); the login_* parameters and the driver do all the work. The control node itself needs psycopg2 / pymysql / pymongo installed and network reachability to the endpoint.

Hands-on Free Lab: PostgreSQL Primary + Replica with Backups

Free, runs on two VMs (or two kind containers, or two Multipass instances).

# On your control node
mkdir -p ~/ansible-postgres-lab && cd ~/ansible-postgres-lab

cat > inventory.yml <<'EOF'
all:
  children:
    pg_primary:
      hosts:
        pg-primary:
          ansible_host: 192.168.64.10
    pg_replicas:
      hosts:
        pg-replica-1:
          ansible_host: 192.168.64.11
EOF

cat > group_vars/all.yml <<'EOF'
pg_version: "15"
vault_appuser_password: "AppPassw0rd!"
vault_replicator_password: "ReplPassw0rd!"
EOF

# (Use the install + replication playbooks shown earlier)
ansible-playbook -i inventory.yml install-primary.yml
ansible-playbook -i inventory.yml setup-replication.yml -e replica_bootstrap=true
ansible-playbook -i inventory.yml install-pgbackrest.yml

# Verify
ansible -i inventory.yml pg_primary -m community.postgresql.postgresql_query \
  -a "db=appdb query='SELECT now()'" --become --become-user=postgres
ansible -i inventory.yml pg_replicas -m community.postgresql.postgresql_query \
  -a "db=appdb query='SELECT pg_is_in_recovery()'" --become --become-user=postgres

A working primary+replica with backup = the core of any production Postgres deployment.

Going deeper

Check mode against a live database: what it really does

--check tells Ansible to predict changes without making them. For database modules the support is real but uneven, and knowing the boundary is what keeps a dry run honest:

The practical rule: --check --diff is trustworthy for the declarative modules (db/user/privs/schema/ext) and blind for the imperative one (*_query). Never treat a clean check run as proof that a migration is safe — that safety lives in the migration tool, not in Ansible. For a deep tour of dry runs and diffs, see Check mode, diff & debugging.

Idempotency has a password-shaped hole

Because Postgres, MySQL, and Mongo all store passwords hashed (or salted-hashed), no module can compare your declared password to the stored one. Every *_user module resolves this the same way — via update_password:

• update_password: always (the default) — set the password on every run. A steady-state play therefore reports changed forever and silently re-hashes each pass. Fine for a deliberate rotation play; wrong for a convergence play.
• update_password: on_create — set it only when the account doesn’t yet exist. This is what makes a “create the app user” task idempotent. Use it everywhere except deliberate rotation runs.

This single option is the most common source of “why does my DB play always say changed?” tickets.

Replication is a bootstrap, not a convergence

Config management assumes convergence: describe the end state, re-run safely forever. Replica setup breaks that assumption. pg_basebackup clones a primary’s data directory exactly once; run it against a populated PGDATA and it refuses. The disciplined patterns:

• Guard with creates:. The standby.signal file (Postgres) or a state flag proves the bootstrap already ran, so the task no-ops on re-run. That’s the trick in the streaming-replication example earlier.
• Use replication slots (pg_basebackup -C -S <slot>) so the primary retains WAL for a replica that briefly disconnects, instead of the replica falling irrecoverably behind and needing a fresh rebuild.
• Roll cluster changes with serial and run_once. Initialize a Mongo replica set exactly once (run_once: true), and touch cluster members one at a time (serial: 1) so a bad change can’t take the whole cluster down at once. See delegation, run_once & serial strategies for the rolling patterns.

Scale changes the tool, not the play

At laptop scale pg_basebackup and mongodump are fine. At production scale they become footguns:

• pg_basebackup streams the whole cluster through the primary and pins WAL for the duration — on a multi-terabyte primary that is hours of extra load and disk. Restore a replica from a pgBackRest repository instead (decoupled from the primary) and let it catch up via WAL.
• mongodump is a logical dump — it reads every document through the query engine. Past tens of GB, prefer filesystem/volume snapshots or Ops Manager; keep mongodump --oplog only for point-in-time on modest datasets.
• xtrabackup / mariabackup are physical hot backups — vastly faster than mysqldump for large InnoDB datasets, and the only sane choice past a few GB.

Match forks and serial to the blast radius: a fleet-wide mysql_variables read-tweak can run at high forks; anything that restarts a primary should be serial: 1 with any_errors_fatal: true.

Authentication internals worth knowing

• SCRAM-SHA-256 is the modern Postgres password mechanism (method: scram-sha-256 in pg_hba.conf, and the default hashing since PG 14). Prefer it over the legacy md5. caching_sha2_password is MySQL 8.0’s equivalent default plugin — the plugin: parameter on mysql_user selects it.
• TLS is a per-connection setting on the module: ssl_mode: require (or verify-full) for community.postgresql, with ca_cert/client_cert for verified chains; --ssl-mode semantics for MySQL. Encrypt the wire, especially for managed endpoints reached over the network.
• IAM / token auth (AWS RDS/Aurora, Cloud SQL) removes the stored password entirely — you pass a short-lived token as login_password. No secret to rotate, no secret to leak; the trade is token-generation plumbing at connect time.

Collection and version caveats

• Always use FQCN. community.postgresql.postgresql_db, not the short postgresql_db. Short names depend on collection search order and break the day someone installs a colliding collection.
• Pin collections in requirements.yml. Database collections occasionally rename parameters across majors. Pinning (for example community.postgresql: ">=3.0.0,<4.0.0") keeps a galaxy install from silently changing behaviour under you.
• The driver must exist where the module runs. psycopg2 (Postgres), PyMySQL/mysqlclient (MySQL), pymongo (Mongo) are imported by the module at execution — on the managed host for SSH plays, on the control node for connection: local cloud plays. A missing driver is the single most common first-run failure. Point ansible_python_interpreter at the interpreter that actually has the driver.

Common beginner mistakes

• “The DB modules run on my control node.” They don’t, unless you set connection: local. By default Ansible SSHes to the target and runs the module there, importing the DB driver on the target. That’s why psycopg2/pymysql/pymongo must be installed on the managed host, not on your laptop.
• “postgresql_query is idempotent like postgresql_db.” No. The declarative modules (_db, _user, _privs) converge to a state; _query runs whatever SQL you hand it, every time. CREATE DATABASE twice is safe; ALTER TABLE ... ADD COLUMN twice errors. Treat _query as imperative and guard it, or use a migration tool.
• “Vault means my secrets are safe.” Vault protects secrets in the repo, not in the run. Decrypted values flow into module output and can land in -vvv logs or a failed-task dump. no_log: true on every credential task is what actually protects them at runtime.
• “--check will catch problems in my migration.” Check mode is honest for db/user/privs and blind for *_query — it cannot preview arbitrary DDL/DML. A green dry run says nothing about whether your ALTER is safe.
• “MySQL localhost and 127.0.0.1 are the same account.” MySQL treats 'user'@'localhost' (Unix socket) and 'user'@'127.0.0.1' (TCP) as distinct grants. Create/grant for the host your app actually connects from, or use 'user'@'%' deliberately.
• “update_password defaults to something safe.” It defaults to always, which re-sets the password every run. For a create-once app user you want on_create, or your convergence play rotates the credential (and reports changed) on every pass.
• “become_user: postgres and login_user: postgres are the same thing.” One is an OS identity (sudo), the other a database identity. Postgres’s peer auth links them for local socket connections, which is why so many Postgres tasks use only become_user — but over TCP, or on MySQL/Mongo, they are unrelated.
• “A backup job that runs is a backup I can restore.” An untested backup is theater. Only a completed restore drill proves the backup, the credentials, and the procedure all work together. Schedule the drill, not just the dump.

Common Mistakes & Troubleshooting

1. psycopg2 import error on the target The Python interpreter Ansible chose doesn’t have psycopg2. Either install it (dnf install python3-psycopg2) or set ansible_python_interpreter to a venv that has it.

2. mysql_user succeeds but the user can’t log in You probably set host: localhost but the connection comes via TCP/IP (which counts as 127.0.0.1, not localhost). MySQL distinguishes them. Use host: "%" for any host or specify the actual source.

3. postgresql_db errors with “must be owner of database” You’re running as postgres user but trying to drop a DB owned by another role. Use state: absent after become_user: postgres and ensure the play’s user is the actual owner, or use force: true (Postgres 15+ has this option).

4. Mongo replica set initialization hangs The members can’t reach each other on port 27017. Check bindIp: (must be 0.0.0.0, not 127.0.0.1) and firewall rules.

5. postgresql_query keeps reporting changed: true for SELECT statements SELECTs don’t modify state but the module reports changed based on rows. Use changed_when: false for read-only queries.

6. Schema migration runs twice and fails on duplicate key error Pure postgresql_query for DDL is not idempotent. Use Flyway/Liquibase, or wrap with IF NOT EXISTS SQL constructs (CREATE TABLE IF NOT EXISTS, ALTER TABLE ... ADD COLUMN IF NOT EXISTS).

7. Password leaks into Ansible logs You forgot no_log: true on the user-creation task. Vault doesn’t help here — the value is decrypted before the task runs and ends up in stdout. Always no_log: true on credential tasks.

Best Practices

• no_log: true on every task that handles credentials. Vault decryption surfaces the secret in plain text.
• serial: 1 for any change that touches a primary. Replica failovers are not free; one-by-one rollouts let you abort if the first one breaks.
• any_errors_fatal: true for cluster plays. A half-failed replica setup is worse than a fully-failed one.
• Use --check first on every DB play. Most modules support check mode (won’t catch DDL changes, but catches user/grant changes).
• Use a dedicated migration tool for DDL. Flyway, Liquibase, golang-migrate, or Alembic — Ansible coordinates the run, but the tool tracks state.
• Take a backup before destructive plays. Add a pre-task that runs pg_basebackup / xtrabackup / mongodump if the play modifies schemas.
• Pin module versions in requirements.yml. Database collections occasionally rename parameters between major versions.
• Use update_password: on_create. Re-running a postgresql_user task with a different password rotates it; on_create only sets the password if the user doesn’t exist (idempotent for steady-state).
• Audit backup restore procedures. A backup you’ve never restored is theater. Run a restore drill quarterly with a separate playbook.

Security Notes

• Use SSL/TLS for all DB connections. sslmode=require for Postgres, --ssl-mode=REQUIRED for MySQL, tls=true for Mongo.
• Rotate database passwords. Use Ansible Vault keys that rotate monthly, and update_password: always on rotation runs (with a maintenance window).
• Use IAM database authentication on cloud RDS/Aurora. AWS RDS supports IAM auth tokens — no password to leak.
• Never store DB credentials in plaintext. Ansible Vault, HashiCorp Vault, AWS Secrets Manager — pick one.
• Audit DDL changes. Postgres pg_audit extension, MySQL audit log plugin, MongoDB audit log — enable them on every production DB.
• Lock down pg_hba.conf / bind-address / bindIp. Default-deny, allow only from app subnets.
• Drop test database, anonymous users, and remote root. MySQL especially ships with these by default — first task after install is to remove them.

Practice challenges

Six graded exercises, beginner → advanced. Try each before opening the solution. Every playbook fragment uses FQCN and assumes the login_* / Vault patterns from this lesson.

1. (Beginner) Idempotent app database + user. Write two tasks so that appdb exists, owned by a login role appuser, and a second run of the play reports ok, not changed.

<details><summary>Solution</summary>

- name: App login role exists (password set only on create)
  community.postgresql.postgresql_user:
    name: appuser
    password: "{{ appuser_password }}"
    role_attr_flags: LOGIN
    update_password: on_create
  become_user: postgres
  no_log: true

- name: App database exists, owned by appuser
  community.postgresql.postgresql_db:
    name: appdb
    owner: appuser
  become_user: postgres

Why: update_password: on_create plus the declarative postgresql_db/postgresql_user modules mean the second run finds the desired state already present and reports ok. </details>

2. (Beginner) Read-only reporting user in MySQL. Create a reporter account that can only SELECT on appdb, reachable only from the 10.% app subnet.

<details><summary>Solution</summary>

- name: Read-only reporting user on appdb
  community.mysql.mysql_user:
    name: reporter
    password: "{{ reporter_password }}"
    host: "10.%"
    priv: "appdb.*:SELECT"
    plugin: caching_sha2_password
    state: present
    login_user: root
    login_password: "{{ mysql_root_password }}"
  no_log: true

Why: priv: "appdb.*:SELECT" grants exactly one privilege on one database, and scoping host to 10.% avoids the classic '%' wildcard over-grant. </details>

3. (Intermediate) Idempotent column add. Add last_login_at TIMESTAMPTZ to users, but only if it doesn’t already exist, using pure Ansible (no migration tool).

<details><summary>Solution</summary>

- name: Does last_login_at already exist?
  community.postgresql.postgresql_query:
    db: appdb
    query: >
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'users' AND column_name = 'last_login_at'
  register: col
  become_user: postgres
  changed_when: false

- name: Add last_login_at when missing
  community.postgresql.postgresql_query:
    db: appdb
    query: ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ
  when: col.rowcount == 0
  become_user: postgres

Why: the changed_when: false probe plus when: col.rowcount == 0 guard turns non-idempotent DDL into a re-runnable pair. (Postgres also accepts ADD COLUMN IF NOT EXISTS — either works.) </details>

4. (Intermediate) Encrypt a credential and consume it safely. Store reporter’s password in Vault, indirect it through a plaintext var, and use it with no_log.

<details><summary>Solution</summary>

# 1. Create the encrypted file
ansible-vault create group_vars/db/vault.yml
#    vault_reporter_password: "R3port-Only!"

# 2. group_vars/db/vars.yml  (plaintext indirection)
reporter_password: "{{ vault_reporter_password }}"

# 3. Run — every consuming task still carries no_log: true
ansible-playbook site.yml --ask-vault-pass

Why: the vault_-in-vault + plain-name-in-vars split keeps a greppable audit trail; the vault password (or a vault-id) decrypts at runtime, and no_log stops the decrypted value reaching the logs. </details>

5. (Advanced) One-shot replica bootstrap. Write the pg_basebackup task so it clones the primary exactly once and no-ops on every later run — no external state file of your own.

<details><summary>Solution</summary>

- name: Bootstrap standby from primary (runs once)
  ansible.builtin.command:
    cmd: >
      /usr/pgsql-15/bin/pg_basebackup -h {{ primary_ip }} -U replicator
      -D /var/lib/pgsql/15/data -R -X stream -C -S {{ inventory_hostname_short }}
    creates: /var/lib/pgsql/15/data/standby.signal
  become_user: postgres
  environment:
    PGPASSWORD: "{{ replicator_password }}"
  no_log: true

Why: pg_basebackup -R writes standby.signal, and creates: keys idempotence off that file — a genuine one-shot bootstrap that is safe to leave in a convergence play. -C -S also creates a replication slot so the primary retains WAL for this replica. </details>

6. (Advanced) Rotate a password without a stampede. Write the rotation play that sets a new appuser password (already updated in Vault) on the primary, one host at a time.

<details><summary>Solution</summary>

- hosts: db_primary
  serial: 1
  become: true
  tasks:
    - name: Rotate appuser password to the new Vault value
      community.postgresql.postgresql_user:
        name: appuser
        password: "{{ appuser_password }}"   # new value already in Vault
        update_password: always
      become_user: postgres
      no_log: true

Why: update_password: always forces the new hash (the module can’t detect the change on its own), serial: 1 limits blast radius, and the app rollout that consumes the new secret must follow immediately so no tier is left authenticating with the stale credential. </details>

Q&A — 13 Questions

Q1. Should I run schema migrations from Ansible or from a CI tool? From CI, ideally — but invoked through Ansible if your deployment pipeline is Ansible-based. The migration tool (Flyway/Liquibase/golang-migrate) tracks state; Ansible just calls it.

Q2. Why does postgresql_query not support check mode for DML? Because the module doesn’t know whether your INSERT or UPDATE would change rows without running it. SELECTs are check-mode-friendly with changed_when: false.

Q3. How do I do an online column add? ALTER TABLE users ADD COLUMN x INT is online for nullable columns in modern Postgres/MySQL/Mongo. For non-nullable with a default, run two migrations: add nullable, backfill in batches, alter to NOT NULL.

Q4. Should I use community.postgresql.postgresql_table for DDL? Rarely. It only handles simple cases. Real DDL belongs in a migration tool, not in Ansible.

Q5. How do I bootstrap a Postgres replica with TB-scale data? pg_basebackup works up to a point but locks WAL retention on the primary while it streams. For huge clusters use pgbackrest to restore from a backup repository (decoupled from primary), then catch up via WAL.

Q6. Can Ansible promote a replica? Yes — community.postgresql.postgresql_query: query="SELECT pg_promote()" on the replica. But promoting a replica during an incident usually requires more than Ansible — it needs failover orchestration (Patroni, repmgr, pg_auto_failover).

Q7. What’s the safe pattern for password rotation?

1. Set new password in Vault. 2. Run a play that sets it on the DB with update_password: always. 3. Update apps that reference it (rolling deploy). 4. Don’t drop the old password until step 3 completes. The window between (2) and (3) is when both passwords are invalid for half the apps.

Q8. How do I dump just one schema in MySQL with Ansible? community.mysql.mysql_db: state=dump name=appdb target=/tmp/dump.sql ignore_tables=appdb.audit_log. For more control, shell out to mysqldump.

Q9. Mongo replica set — how do I add a fourth member? community.mongodb.mongodb_replicaset with the new member added to members:. The module computes the diff and adds it. Note the new member needs to do an initial sync, which can take hours.

Q10. Should I encrypt backups? Yes — if your DB has any PII or business-sensitive data. pgbackrest supports repo1-cipher-type=aes-256-cbc, MySQL with xtrabackup --encrypt, or use file-system-level encryption (LUKS) on the backup disk.

Q11. How do I run a play against an RDS instance (no SSH)? hosts: localhost, connection: local, and use the DB modules with login_host: <rds-endpoint>. The Python DB driver reaches out to RDS over TCP. No SSH required since you’re not running anything on the DB host.

Q12. What’s the equivalent of flyway info in Ansible? There isn’t one — but community.postgresql.postgresql_query against flyway_schema_history works: SELECT version, description, success FROM flyway_schema_history ORDER BY installed_rank.

Q13. How do I handle community.mongodb.mongodb_replicaset failing on a re-run? The module is mostly idempotent but members: ordering matters. Use mongodb_status to read the existing config first, then only run mongodb_replicaset if the desired state differs.

Quick Check

1. Which collection ships postgresql_db?
2. What does creates: do in the pg_basebackup task?
3. How do you mark a SELECT query as not changing state?
4. Which Mongo module initializes a replica set?
5. What’s the safe pattern for rotating DB passwords without breaking apps?
6. Why is no_log: true mandatory on credential tasks?
7. Should DDL migrations live in Ansible or in Flyway/Liquibase?
8. What does update_password: on_create mean?

Exercise

Build a complete role postgres_cluster that:

1. Installs Postgres 15 on every host in pg_cluster group.
2. Designates one host as primary (group var pg_role: primary) and the rest as replicas.
3. Configures streaming replication with replication slots.
4. Installs and configures pgBackRest with daily full + 6-hourly incremental backups, retaining 7 days.
5. Creates appdb and appuser with appropriate grants.
6. Installs pg_stat_statements and pg_repack extensions.
7. Configures pg_hba.conf to allow connections only from app subnets.
8. Includes a validate.yml task list that confirms the primary is writable and each replica is replicating (pg_is_in_recovery() = true and replay_lag < 1 second).

Test it on a 3-node setup (1 primary, 2 replicas) and verify failover by stopping the primary and running pg_promote() on a replica via Ansible.

Cert Mapping

• EX374 — Database automation is one of the seven domain blocks. Expect a hands-on task: install Postgres or MySQL, create users/databases, configure backups.
• PostgreSQL Associate / Professional — Hands-on Postgres knowledge maps directly.
• MongoDB DBA — Replica set management is a major exam topic.

Glossary

• WAL — Write-Ahead Log, Postgres’s transaction log. Streaming replication ships WAL records to replicas.
• GTID — Global Transaction ID, MySQL’s identifier for transactions. Required for group replication.
• Oplog — MongoDB’s operation log, equivalent to WAL/binlog.
• Replica set — A MongoDB primary + secondaries cluster.
• Group replication — MySQL 8.0’s multi-primary or single-primary cluster mode.
• pgBackRest — Best-in-class Postgres backup tool with parallelism and incremental support.
• xtrabackup / mariabackup — Percona’s hot-backup tool for MySQL/MariaDB.
• Flyway / Liquibase — Schema migration tools that track applied migrations in a DB table.
• FQCN — Fully-Qualified Collection Name, e.g. community.postgresql.postgresql_db. Always use it; short names depend on collection search order.
• Idempotence — Running a task repeatedly leaves the system in the same state and reports changed only when it actually changed something. True for the declarative DB modules, not for arbitrary *_query.
• DDL / DML — Data Definition Language (CREATE, ALTER, DROP) versus Data Manipulation Language (INSERT, UPDATE, DELETE). DDL is where idempotence and check mode get hard.
• login_* parameters — How a DB module authenticates to the engine after Ansible reaches the host: login_host, login_port, login_user, login_password, login_unix_socket, login_db.
• no_log — Task keyword that suppresses a task’s arguments and output from logs. Mandatory on any task handling a credential, because Vault only protects secrets at rest.
• Check mode (--check) — Ansible’s dry run. Trustworthy for db/user/privs modules, blind for *_query.
• update_password — *_user option controlling when the password is (re)set: always (default, re-sets every run) or on_create (sets only for a new account).
• psycopg2 / PyMySQL / pymongo — The Python drivers the modules import to talk to Postgres / MySQL / Mongo. Must be present wherever the module executes.
• Streaming replication — Postgres HA where a standby continuously replays WAL streamed from the primary.
• Replication slot — A primary-side marker that reserves WAL for a specific replica so it can reconnect and catch up without a rebuild.
• standby.signal — Marker file that tells a Postgres instance to start as a read-only standby; written by pg_basebackup -R.
• pg_hba.conf — Postgres’s host-based authentication file: which hosts, users, and databases may connect, and with which method (scram-sha-256, md5, peer).
• SCRAM-SHA-256 / caching_sha2_password — Modern default password mechanisms for Postgres and MySQL 8.0 respectively; prefer them over legacy md5.
• Stanza — pgBackRest’s name for a configured backup set tied to one Postgres cluster.
• InnoDB Cluster / mysqlsh — MySQL’s built-in HA solution and its admin shell, the practical way to stand up group replication rather than wiring it by hand.

Next Steps

You can now bring databases into your Ansible-managed infrastructure with the same discipline as stateless services. The next lesson covers Ansible for VMware: the community.vmware collection, vCenter automation, VM lifecycle, templates, and NSX networking — the patterns that turn vSphere from a click-ops platform into infrastructure-as-code.