Ansible for PostgreSQL and MySQL: Database Automation Complete Guide

Why Ansible for Database Automation?

Database setup and maintenance is repetitive, error-prone, and often done manually. Ansible automates the entire database lifecycle: installation, configuration, user/permission management, replication setup, backups, and performance tuning — all version-controlled and repeatable.

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

Collections

# PostgreSQL
ansible-galaxy collection install community.postgresql
# MySQL / MariaDB
ansible-galaxy collection install community.mysql
# Dependencies on control node
pip install psycopg2-binary  # PostgreSQL
pip install PyMySQL           # MySQL/MariaDB

PostgreSQL

Install PostgreSQL

---
- name: Install and configure PostgreSQL
  hosts: db_servers
  become: true
vars:
    pg_version: 16
    pg_data_dir: "/var/lib/postgresql/{{ pg_version }}/main"
    pg_conf_dir: "/etc/postgresql/{{ pg_version }}/main"
tasks:
    - name: Install PostgreSQL
      ansible.builtin.apt:
        name:
          - "postgresql-{{ pg_version }}"
          - "postgresql-client-{{ pg_version }}"
          - "postgresql-contrib-{{ pg_version }}"
          - python3-psycopg2
        state: present
        update_cache: true
- name: Start PostgreSQL
      ansible.builtin.systemd:
        name: postgresql
        state: started
        enabled: true

Configure postgresql.conf

    - name: Configure PostgreSQL
      ansible.builtin.lineinfile:
        path: "{{ pg_conf_dir }}/postgresql.conf"
        regexp: "^#?{{ item.key }} ="
        line: "{{ item.key }} = {{ item.value }}"
      loop:
        - { key: listen_addresses, value: "'*'" }
        - { key: max_connections, value: "200" }
        - { key: shared_buffers, value: "'4GB'" }
        - { key: effective_cache_size, value: "'12GB'" }
        - { key: work_mem, value: "'64MB'" }
        - { key: maintenance_work_mem, value: "'1GB'" }
        - { key: wal_level, value: "'replica'" }
        - { key: max_wal_senders, value: "5" }
        - { key: max_replication_slots, value: "5" }
        - { key: log_min_duration_statement, value: "1000" }
        - { key: log_checkpoints, value: "on" }
        - { key: log_connections, value: "on" }
        - { key: log_disconnections, value: "on" }
      notify: restart postgresql
- name: Configure pg_hba.conf
      community.postgresql.postgresql_pg_hba:
        dest: "{{ pg_conf_dir }}/pg_hba.conf"
        contype: host
        users: "{{ item.user }}"
        source: "{{ item.source }}"
        databases: "{{ item.db }}"
        method: scram-sha-256
      loop:
        - { user: app_user, source: "10.0.0.0/24", db: app_db }
        - { user: readonly, source: "10.0.0.0/24", db: app_db }
        - { user: replication, source: "10.0.1.0/24", db: replication }
      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: app_db
        encoding: UTF-8
        lc_collate: en_US.UTF-8
        lc_ctype: en_US.UTF-8
        template: template0
        state: present
      become_user: postgres
- name: Create application user
      community.postgresql.postgresql_user:
        name: app_user
        password: "{{ vault_pg_app_password }}"
        encrypted: true
        state: present
      become_user: postgres
      no_log: true
- name: Grant privileges
      community.postgresql.postgresql_privs:
        database: app_db
        privs: ALL
        type: database
        role: app_user
        state: present
      become_user: postgres
- name: Grant schema privileges
      community.postgresql.postgresql_privs:
        database: app_db
        privs: ALL
        type: schema
        objs: public
        role: app_user
        state: present
      become_user: postgres
- name: Create read-only user
      community.postgresql.postgresql_user:
        name: readonly
        password: "{{ vault_pg_readonly_password }}"
        state: present
      become_user: postgres
      no_log: true
- name: Grant read-only access
      community.postgresql.postgresql_privs:
        database: app_db
        privs: SELECT
        type: table
        objs: ALL_IN_SCHEMA
        schema: public
        role: readonly
        state: present
      become_user: postgres

Extensions

    - name: Install extensions
      community.postgresql.postgresql_ext:
        name: "{{ item }}"
        db: app_db
        state: present
      loop:
        - pg_stat_statements
        - pgcrypto
        - uuid-ossp
        - pg_trgm
      become_user: postgres

Backup Automation

---
- name: PostgreSQL backup
  hosts: db_servers
  become: true
  become_user: postgres
vars:
    backup_dir: /backups/postgresql
    retention_days: 30
tasks:
    - name: Create backup directory
      ansible.builtin.file:
        path: "{{ backup_dir }}"
        state: directory
        mode: '0700'
        owner: postgres
- name: Full database dump
      community.postgresql.postgresql_db:
        name: app_db
        state: dump
        target: "{{ backup_dir }}/app_db_{{ ansible_date_time.date }}.sql.gz"
- name: Clean old backups
      ansible.builtin.find:
        paths: "{{ backup_dir }}"
        patterns: "*.sql.gz"
        age: "{{ retention_days }}d"
      register: old_backups
- name: Remove old backups
      ansible.builtin.file:
        path: "{{ item.path }}"
        state: absent
      loop: "{{ old_backups.files }}"
- name: Deploy backup cron job
      ansible.builtin.cron:
        name: "PostgreSQL daily backup"
        minute: "0"
        hour: "2"
        job: "pg_dump -Fc app_db | gzip > {{ backup_dir }}/app_db_$(date +\\%Y-\\%m-\\%d).sql.gz"
        user: postgres

Streaming Replication

---
- name: Configure PostgreSQL primary
  hosts: pg_primary
  become: true
  tasks:
    - name: Create replication user
      community.postgresql.postgresql_user:
        name: replication
        password: "{{ vault_replication_password }}"
        role_attr_flags: REPLICATION,LOGIN
      become_user: postgres
- name: Create replication slot
      community.postgresql.postgresql_slot:
        name: replica_slot_1
        slot_type: physical
        state: present
      become_user: postgres
---
- name: Configure PostgreSQL replica
  hosts: pg_replica
  become: true
  tasks:
    - name: Stop PostgreSQL
      ansible.builtin.systemd:
        name: postgresql
        state: stopped
- name: Remove existing data
      ansible.builtin.file:
        path: "{{ pg_data_dir }}"
        state: absent
- name: Base backup from primary
      ansible.builtin.command:
        cmd: >
          pg_basebackup
          -h {{ hostvars['pg_primary']['ansible_host'] }}
          -U replication
          -D {{ pg_data_dir }}
          -Fp -Xs -P -R
          --slot=replica_slot_1
      become_user: postgres
      environment:
        PGPASSWORD: "{{ vault_replication_password }}"
- name: Start PostgreSQL replica
      ansible.builtin.systemd:
        name: postgresql
        state: started

See also: ansible.mysql 5.0.0 Release: Migrate from community.mysql Collection

MySQL / MariaDB

Install MySQL

---
- name: Install MySQL 8
  hosts: mysql_servers
  become: true
tasks:
    - name: Install MySQL
      ansible.builtin.apt:
        name:
          - mysql-server-8.0
          - mysql-client-8.0
          - python3-pymysql
        state: present
        update_cache: true
- 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_password }}"
        host: localhost
        login_unix_socket: /var/run/mysqld/mysqld.sock
        state: present
      no_log: true
- name: Create .my.cnf for root
      ansible.builtin.template:
        src: my.cnf.j2
        dest: /root/.my.cnf
        mode: '0600'

Create Databases and Users

    - name: Create application database
      community.mysql.mysql_db:
        name: app_db
        encoding: utf8mb4
        collation: utf8mb4_unicode_ci
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock
- name: Create application user
      community.mysql.mysql_user:
        name: app_user
        password: "{{ vault_mysql_app_password }}"
        host: "10.0.0.%"
        priv: "app_db.*:SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,INDEX,DROP"
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock
      no_log: true
- name: Create read-only user
      community.mysql.mysql_user:
        name: readonly
        password: "{{ vault_mysql_readonly_password }}"
        host: "10.0.0.%"
        priv: "app_db.*:SELECT"
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock
      no_log: true
- name: Create backup user
      community.mysql.mysql_user:
        name: backup
        password: "{{ vault_mysql_backup_password }}"
        host: localhost
        priv: "*.*:SELECT,SHOW VIEW,TRIGGER,LOCK TABLES,PROCESS,RELOAD"
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock
      no_log: true

Configure MySQL

    - name: Deploy MySQL configuration
      ansible.builtin.template:
        src: mysqld.cnf.j2
        dest: /etc/mysql/mysql.conf.d/mysqld.cnf
        mode: '0644'
      notify: restart mysql
# templates/mysqld.cnf.j2
    # [mysqld]
    # bind-address = 0.0.0.0
    # max_connections = 200
    # innodb_buffer_pool_size = 4G
    # innodb_log_file_size = 1G
    # innodb_flush_log_at_trx_commit = 1
    # slow_query_log = 1
    # slow_query_log_file = /var/log/mysql/slow.log
    # long_query_time = 2
    # log_error = /var/log/mysql/error.log
    # server-id = {{ mysql_server_id }}
    # log_bin = /var/log/mysql/mysql-bin
    # binlog_format = ROW
    # expire_logs_days = 7
handlers:
    - name: restart mysql
      ansible.builtin.systemd:
        name: mysql
        state: restarted

Run SQL Queries

    - name: Run schema migration
      community.mysql.mysql_query:
        login_db: app_db
        query:
          - CREATE TABLE IF NOT EXISTS users (
              id BIGINT AUTO_INCREMENT PRIMARY KEY,
              email VARCHAR(255) NOT NULL UNIQUE,
              name VARCHAR(255),
              created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB;
          - CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
        login_unix_socket: /var/run/mysqld/mysqld.sock
- name: Import SQL file
      community.mysql.mysql_db:
        name: app_db
        state: import
        target: /tmp/schema.sql
        login_unix_socket: /var/run/mysqld/mysqld.sock

MySQL Backup

    - name: Backup MySQL database
      community.mysql.mysql_db:
        name: app_db
        state: dump
        target: "/backups/mysql/app_db_{{ ansible_date_time.date }}.sql.gz"
        login_unix_socket: /var/run/mysqld/mysqld.sock
- name: Deploy backup script
      ansible.builtin.copy:
        content: |
          #!/bin/bash
          BACKUP_DIR=/backups/mysql
          DATE=$(date +%Y-%m-%d)
          mysqldump --single-transaction --routines --triggers app_db | gzip > ${BACKUP_DIR}/app_db_${DATE}.sql.gz
          find ${BACKUP_DIR} -name "*.sql.gz" -mtime +30 -delete
        dest: /usr/local/bin/mysql-backup.sh
        mode: '0755'
- name: Schedule backup
      ansible.builtin.cron:
        name: "MySQL daily backup"
        minute: "0"
        hour: "3"
        job: /usr/local/bin/mysql-backup.sh

Database Security Hardening

---
- name: Harden database security
  hosts: db_servers
  become: true
  tasks:
    # PostgreSQL
    - name: Remove default postgres trust auth
      community.postgresql.postgresql_pg_hba:
        dest: /etc/postgresql/16/main/pg_hba.conf
        contype: local
        users: all
        databases: all
        method: scram-sha-256
      when: "'postgresql' in group_names"
# MySQL — remove anonymous users and test database
    - name: Remove anonymous MySQL users
      community.mysql.mysql_user:
        name: ""
        host_all: true
        state: absent
        login_unix_socket: /var/run/mysqld/mysqld.sock
      when: "'mysql' in group_names"
- name: Remove test database
      community.mysql.mysql_db:
        name: test
        state: absent
        login_unix_socket: /var/run/mysqld/mysqld.sock
      when: "'mysql' in group_names"
- name: Disallow root remote login
      community.mysql.mysql_user:
        name: root
        host: "%"
        state: absent
        login_unix_socket: /var/run/mysqld/mysqld.sock
      when: "'mysql' in group_names"

See also: Ansible for PostgreSQL: PGMeetUp Bari 2022 Highlights

FAQ

Should I use become_user: postgres or login_user?

For PostgreSQL, become_user: postgres uses Unix socket authentication (peer auth), which is the default and most secure for local connections. Use login_user/login_password for remote connections or when peer auth isn't configured.

How do I handle database migrations with Ansible?

For simple schema changes, use community.postgresql.postgresql_query or community.mysql.mysql_query. For complex applications, use a dedicated migration tool (Alembic, Flyway, Liquibase) and call it from Ansible with ansible.builtin.command. Ansible handles infrastructure, the migration tool handles schema versioning.

Can Ansible manage RDS / Cloud SQL?

Yes. Use the same community.postgresql and community.mysql modules with login_host, login_user, and login_password pointed at your cloud database endpoint. For provisioning the database instance itself, use amazon.aws.rds_instance, azure.azcollection.azure_rm_postgresqlserver, or google.cloud.gcp_sql_instance.

How do I encrypt database passwords in Ansible?

Use Ansible Vault: ansible-vault encrypt_string 'my-password' --name vault_pg_password. Store encrypted values in group_vars/db_servers/vault.yml and reference them as {{ vault_pg_password }}. Always use no_log: true on tasks that handle passwords.

Conclusion

Ansible automates the complete database lifecycle — PostgreSQL and MySQL installation, configuration tuning, user and permission management, replication setup, backup automation, and security hardening. Use community.postgresql and community.mysql collections for idempotent database management. Combine with Ansible Vault for secrets, scheduled backups via cron, and streaming replication for high availability.

Related Articles

• Ansible for SAP HANA and S/4HANA • Ansible Vault Complete Guide • AAP 2.6 Backup and Disaster Recovery • Ansible for Docker and Podman • Install Ansible Complete Guide • Ansible for PostgreSQL: PGMeetUp Bari 2022

See also

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