Ansible Lesson 30 of 42

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

18 min read Vinod

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:

Prerequisites

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.

The community.postgresql Collection

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

Module Purpose
postgresql_db Create/drop databases
postgresql_user Create/drop roles (a.k.a. users)
postgresql_privs Manage GRANT/REVOKE
postgresql_schema Create schemas
postgresql_table Create simple tables (rarely used — DDL via SQL is cleaner)
postgresql_query Run arbitrary SQL (for DDL, DML, admin queries)
postgresql_info Read DB metadata (gather facts)
postgresql_set Set runtime config parameters (work_mem, max_connections)
postgresql_pg_hba Manage pg_hba.conf rules
postgresql_ext Manage extensions (pgcrypto, pg_stat_statements)
postgresql_publication, postgresql_subscription Logical replication
postgresql_membership Add/remove users from roles

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

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.

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.

Module Purpose
mysql_db Create/drop databases
mysql_user Create/drop users with grants
mysql_query Run arbitrary SQL
mysql_replication Configure replica/source roles
mysql_role MySQL 8.0+ roles
mysql_variables Manage runtime variables
mysql_info Read MySQL state (gather facts)

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.

Module Purpose
mongodb_user Create/drop users with roles
mongodb_replicaset Initialize and configure replica sets
mongodb_shutdown Cleanly stop a Mongo instance
mongodb_oplog Configure oplog size
mongodb_index Create/drop indexes
mongodb_shard Add shards to a sharded cluster
mongodb_balancer Enable/disable the sharding balancer

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.

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.

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

Security Notes

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

Glossary

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.

ansibledatabasespostgresqlmysqlmongodbreplicationbackupschema-migrationhigh-availabilitykloudvin
Need this built for real?

Vinod is a Senior Cloud Architect (22+ yrs) — available for Azure / AWS / GCP architecture, landing zones, and migrations.

Work with me
VH

Written by Vinod

Senior Cloud Architect with 22+ years of experience. Sharing practical knowledge from production environments.

Comments