Learn Ansible - Database Management & Automation

#Introduction

After episode 23, where we covered Kubernetes & Container Orchestration — managing containers and clusters with the kubernetes.core and community.docker collections — in this episode we'll descend to the most sensitive layer of a system: the database.

Let's think of it this way. Containers are like a modern kitchen in a restaurant: if one stove or pan breaks, you just replace it with a new one in minutes. But the database is the records warehouse where all business records are stored — customers, transactions, stock, reports. If this warehouse is managed carelessly, damaged, or its data is lost, the restaurant could go out of business. A broken container can be redeployed; lost data may not be recoverable.

The problem is, in many teams, database management is still done very manually and error-prone: an engineer SSHs into a server, types psql or mysql, then runs SQL commands one by one with no documentation. Repetitive, slow, and error-prone. Worse, SQL is imperative and often not idempotent — running CREATE DATABASE twice actually throws an error, rather than finishing cleanly.

That's where Ansible comes in. Through the official community database collections, we can create databases and users, manage privileges, run automatic backups, and configure replication declaratively and idempotently — all in the same YAML you've known since episode 1. In this episode we'll cover the community.postgresql, community.mysql, and community.mongodb collections, deployment patterns like master-slave replication and connection pooling, and the best practice of storing database credentials using Ansible Vault (episode 14).

#Main Discussion

#Getting to Know Ansible's Database Collections

These three collections are the backbone of database automation in the Ansible ecosystem:

What's interesting about these modules: they talk directly to the database server (TCP) from the control node, not via SSH. That means you don't need an agent on the database server, just make sure the database port is accessible from the control node — exactly the agentless Ansible philosophy we covered in episode 1.

Install all three collections along with their dependencies:

ansible-galaxy collection install community.postgresql community.mysql community.mongodb --with-deps

#Preparing Database Credentials with Ansible Vault

Before writing playbooks, one thing you must do is never write database passwords in playbooks or in Git. We covered the Ansible Vault concept in detail in episode 14, so here we'll practice it directly. Store all credentials in a single encrypted variables file:

ansible-vault create group_vars/databases/vault.yml

Fill that file with the credentials of each database we'll manage:

---
vault_postgres_password: "S3cure-R0ot#Pass"
vault_db_user_password: "App-Us3r#Pass"
vault_mysql_root_password: "My5ql-R00t#Pass"
vault_mongo_admin_password: "M0ng0-Adm1n#Pass"
vault_mongo_user_password: "M0ng0-App#Pass"

When running playbooks, add the --ask-vault-pass flag (or use --vault-password-file for CI/CD). Don't forget to mark tasks that touch credentials with no_log: true so passwords don't leak into playbook output.

#Creating Databases and Users in PostgreSQL

Let's start with the most common scenario: creating an ecommerce database along with an application user who has access to it. Because all operations run over TCP, we need to provide login_user and login_password (admin) to each module:

- name: Kelola database dan user PostgreSQL
  hosts: postgresql_servers
  become: true
  vars:
    pg_login_user: postgres
    db_name: ecommerce
    db_user: app_ecommerce
    db_owner: app_ecommerce
  tasks:
    - name: Buat database ecommerce
      community.postgresql.postgresql_db:
        name: "{{ db_name }}"
        owner: "{{ db_owner }}"
        encoding: UTF8
        login_user: "{{ pg_login_user }}"
        login_password: "{{ vault_postgres_password }}"
        no_log: true
 
    - name: Buat user aplikasi ecommerce
      community.postgresql.postgresql_user:
        name: "{{ db_user }}"
        password: "{{ vault_db_user_password }}"
        login_user: "{{ pg_login_user }}"
        login_password: "{{ vault_postgres_password }}"
        no_log: true

Notice several important things:

• Idempotency: the postgresql_db module with state: present (default) creates the database if it doesn't exist, and does nothing if it already does — unlike a manual CREATE DATABASE that errors when the database already exists.
• no_log: true: ensures passwords aren't printed in logs, while also suppressing secrets from verbose mode.
• owner: declares the database owner. Setting the right owner from the start avoids ALTER DATABASE operations later.

#Privilege Management: The Least Privilege Principle

Creating a user isn't enough — the application user must be given the smallest possible access rights so the application still runs. For PostgreSQL, the right module is postgresql_privs:

- name: Kelola privilege PostgreSQL
  hosts: postgresql_servers
  become: true
  tasks:
    - name: Berikan akses schema kepada user aplikasi
      community.postgresql.postgresql_privs:
        database: ecommerce
        roles: app_ecommerce
        type: schema
        objs: public
        privs: USAGE
        login_user: postgres
        login_password: "{{ vault_postgres_password }}"
 
    - name: Berikan akses tabel di schema public
      community.postgresql.postgresql_privs:
        database: ecommerce
        roles: app_ecommerce
        type: table
        objs: "public.*"
        privs: SELECT,INSERT,UPDATE,DELETE
        login_user: postgres
        login_password: "{{ vault_postgres_password }}"

Why bother separating privileges? Because in the real world, breaches most often start from credentials with too broad access. If the application user is a superuser and its credentials leak, an attacker could wipe the entire database. With minimal privileges, the impact of a leak can be contained. Common patterns:

• Admin user (e.g., postgres) — only used during provisioning and migrations, not for the application.
• Application user — only SELECT/INSERT/UPDATE/DELETE on its own database.
• Replication user — exclusively for streaming replication, can't log into the application.

#Creating Databases and Users in MySQL / MariaDB

For MySQL/MariaDB, the pattern is nearly the same with community.mysql. Note that MySQL users are identified by the combination of name and host, and privileges are granted directly through the priv parameter:

- name: Kelola database dan user MySQL
  hosts: mysql_servers
  become: true
  vars:
    mysql_login_user: root
    db_name: ecommerce
    db_user: app_ecommerce
  tasks:
    - name: Buat database ecommerce
      community.mysql.mysql_db:
        name: "{{ db_name }}"
        encoding: utf8mb4
        login_user: "{{ mysql_login_user }}"
        login_password: "{{ vault_mysql_root_password }}"
        login_unix_socket: /var/run/mysqld/mysqld.sock
 
    - name: Buat user aplikasi dengan privilege spesifik
      community.mysql.mysql_user:
        name: "{{ db_user }}"
        host: "10.0.0.%"
        password: "{{ vault_db_user_password }}"
        priv: "ecommerce.*:SELECT,INSERT,UPDATE,DELETE"
        state: present
        login_user: "{{ mysql_login_user }}"
        login_password: "{{ vault_mysql_root_password }}"

The priv syntax in MySQL is "database.table:SETTINGS". Some examples:

#Creating Users in MongoDB

MongoDB is a bit different: users are managed per authentication database (database: admin for administrative users), and roles is a list of objects consisting of db and role:

- name: Kelola user MongoDB
  hosts: mongodb_servers
  become: true
  vars:
    mongo_host: 127.0.0.1
    db_name: ecommerce
    db_user: app_ecommerce
  tasks:
    - name: Buat user aplikasi di MongoDB
      community.mongodb.mongodb_user:
        login_host: "{{ mongo_host }}"
        login_user: "{{ mongo_admin_user }}"
        login_password: "{{ vault_mongo_admin_password }}"
        database: admin
        name: "{{ db_user }}"
        password: "{{ vault_mongo_user_password }}"
        roles:
          - db: "{{ db_name }}"
            role: readWrite
        state: present

Notice the mongodb_user module needs login_user/login_password from an administrative user (in the admin authentication database) to create other users. MongoDB is a document store, so "schema migration" here more often means schema validation at the application level or collections with validators — a conceptual difference you should keep in mind.

#Backup Automation

Backup is one of the tasks most often "forgotten" until a disaster happens. With Ansible, backups can be scheduled and run consistently — and the results can be verified.

The most idiomatic approach for PostgreSQL is to use the postgresql_db module with state: dump, which outputs an SQL dump file to target:

- name: Backup otomatis database PostgreSQL
  hosts: postgresql_servers
  become: true
  vars:
    backup_dir: /var/backups/postgres
    db_name: ecommerce
  tasks:
    - name: Siapkan direktori backup
      ansible.builtin.file:
        path: "{{ backup_dir }}"
        state: directory
        owner: postgres
        group: postgres
        mode: "0750"
 
    - name: Dump database ecommerce
      community.postgresql.postgresql_db:
        name: "{{ db_name }}"
        state: dump
        target: "{{ backup_dir }}/ecommerce-{{ ansible_date_time.date }}.sql"
        login_user: postgres
        login_password: "{{ vault_postgres_password }}"

Notice the use of {{ ansible_date_time.date }} — a fact Ansible collects from the server — so each day produces a new filename. Don't forget to add a retention task using the ansible.builtin.find + ansible.builtin.file modules to delete backups older than, say, 7 days, so the disk doesn't fill up.

Many teams also combine the module approach with direct pg_dump/mysqldump commands via shell + register, especially for needs the modules don't cover yet (e.g., backups with compression or specific options). For example:

- name: Dump via pg_dump dengan kompresi
  ansible.builtin.shell: |
    pg_dump -h 127.0.0.1 -U postgres -Fc {{ db_name }} > "{{ backup_dir }}/{{ db_name }}-{{ ansible_date_time.date }}.dump"
  become_user: postgres
  become: true
  args:
    executable: /bin/bash
  changed_when: false
  register: backup_result

#Deployment Pattern: Master-Slave Replication

When a database starts being used by many servers or its needs are read-heavy, you need replication. Instead of typing pg_hba.conf, postgresql.auto.conf, and pg_basebackup configurations manually on every server, the community.postgresql collection provides the postgresql_primary and postgresql_standby modules:

- name: Konfigurasi streaming replication
  hosts: db_servers
  become: true
  vars:
    pg_primary_host: db-01.internal
  tasks:
    - name: Konfigurasi server primary
      community.postgresql.postgresql_primary:
        login_host: "{{ pg_primary_host }}"
        login_user: postgres
        login_password: "{{ vault_postgres_password }}"
        host: "{{ pg_primary_host }}"
        replication_user: repl
        replication_password: "{{ vault_pg_replication_password }}"
      when: inventory_hostname == groups['db_primary'][0]
 
    - name: Konfigurasi server standby
      community.postgresql.postgresql_standby:
        login_host: "{{ pg_primary_host }}"
        login_user: postgres
        login_password: "{{ vault_postgres_password }}"
        host: "{{ pg_primary_host }}"
        primary_conninfo: "host={{ pg_primary_host }} port=5432 user=repl password={{ vault_pg_replication_password }} application_name=standby1"
      when: inventory_hostname in groups['db_standby']

#Connection Pooling: PgBouncer and ProxySQL

Database connections are expensive. Every TCP connection to PostgreSQL consumes memory and time. When an application's traffic spikes, the number of connections can exceed max_connections, and the database starts refusing connections — the application collapses with it. The standard solution is a connection pooler: the application only holds a few connections to the pooler, and the pooler reuses connections to the database.

• PgBouncer for PostgreSQL — transaction mode is the most popular choice.
• ProxySQL for MySQL/MariaDB — besides pooling, it also has query routing and query rewrite features.

Deploy PgBouncer with a template + handler as usual:

[databases]
ecommerce = host=127.0.0.1 port=5432 dbname=ecommerce
 
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

- name: Deploy PgBouncer
  hosts: pgbouncer_servers
  become: true
  tasks:
    - name: Install PgBouncer
      ansible.builtin.apt:
        name: pgbouncer
        state: present
 
    - name: Render konfigurasi PgBouncer
      ansible.builtin.template:
        src: templates/pgbouncer.ini.j2
        dest: /etc/pgbouncer/pgbouncer.ini
        owner: postgres
        group: postgres
        mode: "0644"
      notify: restart pgbouncer

The application then just points its connections to port 6432 — minimal changes on the application side, while database resilience improves dramatically. The same pattern applies to ProxySQL on MySQL/MariaDB.

#Best Practices for Managing Databases with Ansible

Here are practices that separate amateur database engineers from professional ones:

1. All credentials in Ansible Vault — no plaintext database passwords in Git, playbooks, or inventory. Use --ask-vault-pass locally and --vault-password-file in CI/CD. (Full details in episode 14.)
2. Idempotent operations — use modules (postgresql_db, mysql_user, etc.) not shell + raw SQL. Modules know how to compare state; shell doesn't. If you must use shell, wrap it with changed_when and register conditions.
3. Least privilege principle — separate admin, application, and replication users. Grant the narrowest privileges possible, restrict host.
4. Test changes in staging first — run playbooks in a staging environment whose data is a production backup restore (anonymized data if needed). Use ansible-playbook --check for dry runs and Molecule (episode 18) for role testing.
5. Schema migrations must be versioned — manage migration SQL files in Git, run them in order, and record applied versions in a ledger table (e.g., schema_migrations) so changed_when is accurate and migrations don't run twice.
6. Tested backups — automate backups, retention, and restore verification processes.

#Common Pitfalls

1. Plaintext database passwords in playbooks or Git

This is the most fatal mistake. A password that's ever entered Git history can't be truly removed (episode 14). Always store it in Ansible Vault and use no_log: true on tasks that touch it.

2. Using shell + SQL when a module exists

mysql and psql via shell aren't idempotent. A real example: a task running CREATE DATABASE will fail on the second run. Modules like mysql_db and postgresql_db handle this with state: present/absent.

3. Targeting the wrong database host

Database modules connect via TCP to the database server. If the port isn't accessible from the control node (firewall, bind address 127.0.0.1), the task fails with Connection refused. Use login_host, login_port, and make sure the network path is correct. Conversely, don't change the database bind address to 0.0.0.0 without a strong security reason.

4. Superuser privileges for application users

It's tempting to grant ALL PRIVILEGES or superuser to "avoid hassle." The effect: one credential leak = total database loss. Apply least privilege from the start.

5. Non-idempotent schema migrations

Running ALTER TABLE ... ADD COLUMN twice will error. Solution: guard with IF NOT EXISTS, or record migration versions in a ledger table. Don't rely on "run once" in production.

6. Backups without retention and without restore verification

A full disk from accumulating backups, or a corrupt dump only noticed at disaster time — both are equally painful. Automate both: retention (delete backups older than N days) and verification (restore to staging).

#Conclusion

In this episode we've covered database automation with Ansible: the community.postgresql, community.mysql, and community.mongodb collections for creating databases, users, and managing privileges; idempotent and tested automatic backups; deployment patterns like master-slave replication with postgresql_primary/postgresql_standby; connection pooling with PgBouncer and ProxySQL; and the best practice of storing credentials in Ansible Vault with no_log: true.

The core of all this is simple: a database is the most expensive asset to replace, so every change to it must be auditable, reproducible, and testable. With Ansible, repetitive and error-prone DBA work becomes declarative, documented, and idempotent configuration.

In episode 25, we'll cover Monitoring & Observability Stack Automation — deploying Prometheus and Alertmanager, Grafana with dashboards as code, Node Exporter, and logging stacks like ELK and Loki/Promtail — all managed with Ansible so all the infrastructure we've built throughout this series can be monitored well. Keep your enthusiasm up!