Automate PostgreSQL Backups with Ansible Playbook

Discover how to automate PostgreSQL database backups with Ansible. This guide includes a live Playbook example and detailed execution steps for seamless data management.

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] *