Database automation with Ansible

Symptom

Goal: Make database installation, configuration, user and backup tasks repeatable and idempotent (re-running the same playbook does not break the system) with Ansible.

1. Define the inventory and groups

Group servers in the inventory by database role; put primary, replica and backup servers in separate groups. Use a static INI/YAML inventory or a dynamic inventory for the cloud (aws_ec2, gcp_compute). Put group variables under group_vars/ and host-specific ones under host_vars/.

PostgreSQL

[pg_primary]
db1.example.local

[pg_replica]
db2.example.local

[postgres:children]
pg_primary
pg_replica

2. Install the right collection (community.postgresql / community.mysql / community.mongodb)

Ansible core now ships database modules in separate collections; install the collection for your engine via requirements.yml. Use community.postgresql (postgresql_db, postgresql_user, postgresql_privs) for PostgreSQL, community.mysql (mysql_db, mysql_user) for MySQL/MariaDB and community.mongodb for MongoDB. The corresponding Python client (psycopg2, PyMySQL, pymongo) must be installed on the target host.

PostgreSQL

# requirements.yml
collections:
  - name: community.postgresql

# install:
ansible-galaxy collection install -r requirements.yml

MySQL

collections:
  - name: community.mysql

MongoDB

collections:
  - name: community.mongodb

3. Encrypt secrets with Ansible Vault

Do not leave database passwords, replication secrets and superuser credentials in plain text; keep them in an encrypted variable file with ansible-vault. Decrypt at run time with --ask-vault-pass or --vault-password-file. Secrets can then be stored safely in Git and the playbook stays idempotent.

PostgreSQL

ansible-vault create group_vars/postgres/vault.yml
# contents:
vault_pg_admin_password: "S3cret!"

# run:
ansible-playbook site.yml --ask-vault-pass

4. Set up the role structure (roles/postgres, roles/mysql)

Create one role per engine and split the logic into tasks/, defaults/, templates/ and handlers/ subfolders. This structure improves reusability: defaults/main.yml for default values, templates/ for jinja2 configuration templates, handlers/ for 'restart only if something changed' logic. You can scaffold the role with ansible-galaxy init roles/postgres.

PostgreSQL

ansible-galaxy init roles/postgres
# roles/postgres/
#   defaults/main.yml
#   tasks/main.yml
#   templates/postgresql.conf.j2
#   handlers/main.yml

5. Write installation and configuration tasks idempotently

Install the package, initialize the data directory and render postgresql.conf/my.cnf from a template; restart the service via a handler when the configuration changes. Modules are idempotent: an already installed package is not reinstalled, and the handler is not triggered if the file did not change. Render the configuration template with the template module and link it to the handler with 'notify'.

PostgreSQL

- name: postgresql.conf template
  ansible.builtin.template:
    src: postgresql.conf.j2
    dest: /etc/postgresql/16/main/postgresql.conf
  notify: restart postgresql

# handlers/main.yml
- name: restart postgresql
  ansible.builtin.service:
    name: postgresql
    state: restarted

MySQL

- name: my.cnf template
  ansible.builtin.template:
    src: my.cnf.j2
    dest: /etc/mysql/my.cnf
  notify: restart mysql

6. Define database and user tasks

Create the database, users and privileges with modules; take the password from a Vault variable. The community.postgresql modules are idempotent: if the user/DB exists they do not recreate it, they apply only the difference. Grant privileges separately with postgresql_privs; in MySQL the priv field of mysql_user is used.

PostgreSQL

- name: application database
  community.postgresql.postgresql_db:
    name: appdb
    state: present

- name: application user
  community.postgresql.postgresql_user:
    name: app_user
    password: "{{ vault_app_password }}"
    db: appdb
    state: present

MySQL

- name: application user
  community.mysql.mysql_user:
    name: app_user
    password: "{{ vault_app_password }}"
    priv: 'appdb.*:ALL'
    state: present

7. Add the backup task and schedule

Deploy the backup script from a template and schedule it with ansible.builtin.cron; the backup job is then also managed as code. Make the backup target directory, retention period and schedule variables. The cron module is idempotent and identifies the entry by 'name', so re-running does not duplicate it.

PostgreSQL

- name: nightly backup (cron)
  ansible.builtin.cron:
    name: "pg dump nightly"
    minute: "0"
    hour: "2"
    job: "pg_dump -Fc appdb > /backup/appdb_$(date +\\%F).dump"