Ansible for PostgreSQL and MySQL: Database Automation Complete Guide
By Luca Berton · Published 2024-01-01 · Category: installation
Automate PostgreSQL and MySQL with Ansible. Create databases and users, manage permissions, configure replication, automate backups, tune performance, and deploy database infrastructure at scale.
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.
Collections
PostgreSQL
Install PostgreSQL
Configure postgresql.conf
Create Databases and Users
Extensions
Backup Automation
Streaming Replication
MySQL / MariaDB
Install MySQL
Create Databases and Users
Configure MySQL
Run SQL Queries
MySQL Backup
Database Security Hardening
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
Category: installation