Ansible for Database Automation: PostgreSQL, MySQL, MongoDB, and Redis

By Luca Berton · Published 2024-01-01 · Category: installation

Automate database deployment and management with Ansible. Install, configure, backup, replicate, and manage PostgreSQL, MySQL, MongoDB, and Redis at enterprise.

Introduction

Databases are the backbone of every application, and managing them at scale requires automation. Ansible provides modules for installing, configuring, backing up, and managing the most popular database engines — without needing agents on database servers. From initial deployment through replication setup to automated backups, Ansible handles the full database lifecycle.

PostgreSQL

Install and Configure

---
- name: Deploy PostgreSQL
  hosts: postgresql_servers
  become: true
  vars:
    pg_version: 16
    pg_data_dir: /var/lib/postgresql/{{ pg_version }}/main
    pg_max_connections: 200
    pg_shared_buffers: 4GB
    pg_work_mem: 64MB
  tasks:
    - name: Install PostgreSQL
      ansible.builtin.apt:
        name:
          - "postgresql-{{ pg_version }}"
          - "postgresql-client-{{ pg_version }}"
          - python3-psycopg2
        state: present

    - name: Configure postgresql.conf
      ansible.builtin.lineinfile:
        path: "/etc/postgresql/{{ pg_version }}/main/postgresql.conf"
        regexp: "^#?{{ item.key }}"
        line: "{{ item.key }} = {{ item.value }}"
      loop:
        - { key: listen_addresses, value: "'*'" }
        - { key: max_connections, value: "{{ pg_max_connections }}" }
        - { key: shared_buffers, value: "{{ pg_shared_buffers }}" }
        - { key: work_mem, value: "{{ pg_work_mem }}" }
        - { key: effective_cache_size, value: "12GB" }
        - { key: maintenance_work_mem, value: "512MB" }
        - { key: wal_level, value: "replica" }
        - { key: max_wal_senders, value: "5" }
        - { key: archive_mode, value: "on" }
        - { key: log_statement, value: "'ddl'" }
      notify: restart postgresql

    - name: Configure pg_hba.conf
      community.postgresql.postgresql_pg_hba:
        dest: "/etc/postgresql/{{ pg_version }}/main/pg_hba.conf"
        contype: host
        databases: "{{ item.db }}"
        users: "{{ item.user }}"
        source: "{{ item.source }}"
        method: scram-sha-256
      loop:
        - { db: all, user: all, source: "10.0.0.0/8" }
        - { db: replication, user: replicator, source: "10.0.0.0/8" }
      notify: reload postgresql

  handlers:
    - name: restart postgresql
      ansible.builtin.systemd:
        name: postgresql
        state: restarted
    - name: reload postgresql
      ansible.builtin.systemd:
        name: postgresql
        state: reloaded

Create Databases and Users

    - name: Create application database
      community.postgresql.postgresql_db:
        name: "{{ item }}"
        encoding: UTF-8
        lc_collate: en_US.UTF-8
        lc_ctype: en_US.UTF-8
      become_user: postgres
      loop:
        - myapp_production
        - myapp_staging

    - name: Create application user
      community.postgresql.postgresql_user:
        name: app_user
        password: "{{ vault_db_password }}"
        role_attr_flags: NOSUPERUSER,NOCREATEDB
      become_user: postgres
      no_log: true

    - name: Grant privileges
      community.postgresql.postgresql_privs:
        database: myapp_production
        roles: app_user
        type: database
        privs: CONNECT
      become_user: postgres

    - name: Grant schema privileges
      community.postgresql.postgresql_privs:
        database: myapp_production
        roles: app_user
        type: schema
        objs: public
        privs: ALL
      become_user: postgres

PostgreSQL Replication

- name: Configure PostgreSQL streaming replication
  hosts: pg_replica
  become: true
  tasks:
    - name: Stop PostgreSQL
      ansible.builtin.systemd:
        name: postgresql
        state: stopped

    - name: Clear data directory
      ansible.builtin.file:
        path: "{{ pg_data_dir }}"
        state: absent

    - name: Base backup from primary
      ansible.builtin.command: >
        pg_basebackup -h {{ pg_primary_host }}
        -D {{ pg_data_dir }}
        -U replicator -Fp -Xs -P -R
      become_user: postgres

    - name: Start PostgreSQL (replica mode)
      ansible.builtin.systemd:
        name: postgresql
        state: started

MySQL

Install and Secure

- name: Deploy MySQL
  hosts: mysql_servers
  become: true
  tasks:
    - name: Install MySQL
      ansible.builtin.apt:
        name:
          - mysql-server
          - python3-pymysql
        state: present

    - name: Start MySQL
      ansible.builtin.systemd:
        name: mysql
        state: started
        enabled: true

    - name: Set root password
      community.mysql.mysql_user:
        name: root
        password: "{{ vault_mysql_root_pass }}"
        host: localhost
        login_unix_socket: /var/run/mysqld/mysqld.sock
      no_log: true

    - name: Remove anonymous users
      community.mysql.mysql_user:
        name: ''
        host_all: true
        state: absent
        login_user: root
        login_password: "{{ vault_mysql_root_pass }}"

    - name: Remove test database
      community.mysql.mysql_db:
        name: test
        state: absent
        login_user: root
        login_password: "{{ vault_mysql_root_pass }}"

    - name: Create application database
      community.mysql.mysql_db:
        name: myapp
        encoding: utf8mb4
        collation: utf8mb4_unicode_ci
        login_user: root
        login_password: "{{ vault_mysql_root_pass }}"

    - name: Create application user
      community.mysql.mysql_user:
        name: app_user
        password: "{{ vault_mysql_app_pass }}"
        host: "10.0.%"
        priv: "myapp.*:ALL"
        login_user: root
        login_password: "{{ vault_mysql_root_pass }}"
      no_log: true

    - name: Configure MySQL
      ansible.builtin.template:
        src: my.cnf.j2
        dest: /etc/mysql/mysql.conf.d/custom.cnf
      notify: restart mysql

MongoDB

Deploy Replica Set

- name: Deploy MongoDB Replica Set
  hosts: mongodb_servers
  become: true
  vars:
    mongo_version: "7.0"
    mongo_replset: "rs0"
  tasks:
    - name: Install MongoDB
      ansible.builtin.apt:
        name: mongodb-org
        state: present

    - name: Configure MongoDB
      ansible.builtin.template:
        src: mongod.conf.j2
        dest: /etc/mongod.conf
      notify: restart mongod

    - name: Start MongoDB
      ansible.builtin.systemd:
        name: mongod
        state: started
        enabled: true

- name: Initialize replica set
  hosts: mongodb_servers[0]
  tasks:
    - name: Initialize replica set
      community.mongodb.mongodb_replicaset:
        login_host: localhost
        login_port: 27017
        replica_set: "{{ mongo_replset }}"
        members:
          - host: "{{ groups['mongodb_servers'][0] }}:27017"
            priority: 2
          - host: "{{ groups['mongodb_servers'][1] }}:27017"
            priority: 1
          - host: "{{ groups['mongodb_servers'][2] }}:27017"
            priority: 1

    - name: Create admin user
      community.mongodb.mongodb_user:
        database: admin
        name: admin
        password: "{{ vault_mongo_admin_pass }}"
        roles:
          - { db: admin, role: userAdminAnyDatabase }
          - { db: admin, role: clusterAdmin }
        replica_set: "{{ mongo_replset }}"
      no_log: true

Redis

Deploy with Sentinel HA

- name: Deploy Redis
  hosts: redis_servers
  become: true
  tasks:
    - name: Install Redis
      ansible.builtin.apt:
        name: redis-server
        state: present

    - name: Configure Redis
      ansible.builtin.template:
        src: redis.conf.j2
        dest: /etc/redis/redis.conf
      notify: restart redis

    - name: Start Redis
      ansible.builtin.systemd:
        name: redis-server
        state: started
        enabled: true

- name: Configure Redis Sentinel
  hosts: redis_sentinel
  become: true
  tasks:
    - name: Configure Sentinel
      ansible.builtin.template:
        src: sentinel.conf.j2
        dest: /etc/redis/sentinel.conf
      notify: restart sentinel

# templates/redis.conf.j2
bind {{ ansible_default_ipv4.address }} 127.0.0.1
port 6379
requirepass {{ vault_redis_password }}
maxmemory {{ redis_maxmemory | default('2gb') }}
maxmemory-policy allkeys-lru

{% if redis_role == 'replica' %}
replicaof {{ redis_primary_host }} 6379
masterauth {{ vault_redis_password }}
{% endif %}

# Security
rename-command FLUSHDB ""
rename-command FLUSHALL ""
rename-command DEBUG ""

Automated Backups

- name: Database backup schedule
  hosts: database_servers
  become: true
  tasks:
    - name: PostgreSQL backup
      ansible.builtin.cron:
        name: "PostgreSQL daily backup"
        hour: "2"
        minute: "0"
        job: >
          pg_dump -Fc myapp_production >
          /backup/pg-myapp-$(date +\%Y\%m\%d).dump &&
          find /backup -name 'pg-myapp-*.dump' -mtime +30 -delete
        user: postgres
      when: "'postgresql' in group_names"

    - name: MySQL backup
      ansible.builtin.cron:
        name: "MySQL daily backup"
        hour: "2"
        minute: "0"
        job: >
          mysqldump --all-databases --single-transaction
          -u backup -p'{{ vault_mysql_backup_pass }}' |
          gzip > /backup/mysql-all-$(date +\%Y\%m\%d).sql.gz &&
          find /backup -name 'mysql-all-*.sql.gz' -mtime +30 -delete
      when: "'mysql' in group_names"

    - name: MongoDB backup
      ansible.builtin.cron:
        name: "MongoDB daily backup"
        hour: "2"
        minute: "0"
        job: >
          mongodump --out /backup/mongo-$(date +\%Y\%m\%d)
          -u backup -p '{{ vault_mongo_backup_pass }}' --authenticationDatabase admin &&
          find /backup -maxdepth 1 -name 'mongo-*' -type d -mtime +30 -exec rm -rf {} +
      when: "'mongodb' in group_names"

Required Collections

ansible-galaxy collection install community.postgresql
ansible-galaxy collection install community.mysql
ansible-galaxy collection install community.mongodb
ansible-galaxy collection install community.general

pip install psycopg2-binary pymysql pymongo redis

Best Practices

Always use no_log: true for password-related tasks
Replication before backups — High availability first, then backup the replica
Test restores — A backup you can't restore is worthless
Parameterize tuning — Use Ansible variables for memory/connection settings per environment
Encrypt connections — TLS/SSL for all database connections
Separate backup user — Dedicated read-only user for backups
Monitor replication lag — Alert when replicas fall behind
Use connection pooling — Deploy PgBouncer/ProxySQL alongside databases

FAQ

Can Ansible handle database migrations?

Ansible can run migration commands (alembic upgrade head, flask db upgrade, rails db:migrate), but dedicated migration tools provide better rollback and versioning.

How to handle large databases?

Use pg_basebackup for PostgreSQL, xtrabackup for MySQL — both support online, non-blocking backups that Ansible can orchestrate.

Should databases be in containers?

For production: bare metal or VMs are generally preferred for performance and data persistence. Containers work well for dev/test environments.

Conclusion

Ansible provides comprehensive database automation — from initial deployment through replication setup, security hardening, and automated backups. By managing PostgreSQL, MySQL, MongoDB, and Redis through playbooks, you ensure consistent, reproducible database infrastructure across all environments.

Category: installation

Browse all Ansible tutorials · AnsiblePilot Home

AnsiblePilot — Master Ansible Automation

Popular Topics

About Luca Berton