Automate PostgreSQL Backups with Ansible Playbook — Video Tutorial

How to Backup a PostgreSQL Database with Ansible? I’m going to show you a live Playbook with some simple Ansible code. I’m Luca Berton and welcome to today’s episode of Ansible Pilot. Ansible Backup a PostgreSQL Database > `community.postgresql.postgresql_db`: Add or remove PostgreSQL databases from a remote host Let’s talk about the Ansible module postgresql_db. The full name is `community.postgresql.postgresql_db`, which means that is part of the collection of modules “`community.postgresql`” maintained by the Ansible Community to interact with PostgreSQL. The collection is tested with `ansible-core` version 2.11+, prior versions such as 2.9 or 2.10 are not supported. The purpose of the module is to add or remove PostgreSQL databases from a remote host. Parameters - `name` _string_ — name of database - `state` _string_ — `present`/`absent`/`dump`/`restore`/`rename` — the operation - `target` - file name Let me summarize the main parameters of the module postgresql_db. Ansible supposes that PostgreSQL is in the target node. The only required parameter is `name`, the name of the database to interact with. The parameter “`state`” specifies the desired state or the operation for the selected database. The option “present” means that the database should be created and the option `absent` means that the database should be deleted. Other useful operations are `dump` and `restore` which use `pg_dump`, the embedded PostgreSQL utility to backup and restore to the target file. Another useful operation is `rename`, from name to target. This module use psycopg2, a Python PostgreSQL database library. You must ensure that `python3-psycopg2` is installed on the host before using this module. Links - [community.postgresql.postgresql_db](https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_db_module.html) ## Playbook Let’s jump into a real-life Ansible Playbook to Backup a PostgreSQL Database. I’m going to show you how to backup the testdb database in a /backups folder in the current PostgreSQL server. code ```yaml --- - name: postgresql Playbook hosts: all become: true vars: db_name: testdb backup_dir: "/backups" tasks: - name: Utility present ansible.builtin.package: name: python3-psycopg2 state: present - name: Backup directory ansible.builtin.file: path: "{{ backup_dir }}" mode: 0777 owner: postgres state: directory - name: Backup db community.postgresql.postgresql_db: state: dump name: "{{ db_name }}" target: "{{ backup_dir }}/{{ db_name }}.gz" become: true become_user: postgres ``` execution ```bash $ ansible-playbook -i virtualmachines/demo/inventory postgresql/db_backup.yml PLAY [postgresql Playbook] ************************************************************************************ TASK [Gathering Facts] ***************************************************************************