Backup a PostgreSQL Database - Ansible module postgresql_db
How to automate the backup of a “testdb” database on PostgreSQL using Ansible Playbook and postgresql_db module.


How to Backup a PostgreSQL Database with Ansible?
I’m going to show you a live demo 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
The Best Resources For Ansible
Video Course
Printed Book
eBooks
- Ansible by Examples: 200+ Automation Examples For Linux and Windows System Administrator and DevOps
- Ansible For Windows By Examples: 50+ Automation Examples For Windows System Administrator And DevOps
- Ansible For Linux by Examples: 100+ Automation Examples For Linux System Administrator and DevOps
- Ansible Linux Filesystem By Examples: 40+ Automation Examples on Linux File and Directory Operation for Modern IT Infrastructure
- Ansible For Containers and Kubernetes By Examples: 20+ Automation Examples To Automate Containers, Kubernetes and OpenShift
- Ansible For Security by Examples: 100+ Automation Examples to Automate Security and Verify Compliance for IT Modern Infrastructure
- Ansible Tips and Tricks: 10+ Ansible Examples to Save Time and Automate More Tasks
- Ansible Linux Users & Groups By Examples: 20+ Automation Examples on Linux Users and Groups Operation for Modern IT Infrastructure
- Ansible For PostgreSQL by Examples: 10+ Examples To Automate Your PostgreSQL database
- Ansible For Amazon Web Services AWS By Examples: 10+ Examples To Automate Your AWS Modern Infrastructure
demo
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
---
- name: postgresql demo
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
$ ansible-playbook -i virtualmachines/demo/inventory postgresql/db_backup.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Backup directory] ***********************************************************************************
changed: [demo.example.com]
TASK [Backup db] ******************************************************************************************
changed: [demo.example.com]
PLAY RECAP ************************************************************************************************
demo.example.com : ok=4 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
(partially) idempotency
$ ansible-playbook -i virtualmachines/demo/inventory postgresql/db_backup.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Backup directory] ***********************************************************************************
ok: [demo.example.com]
TASK [Backup db] ******************************************************************************************
changed: [demo.example.com]
PLAY RECAP ************************************************************************************************
demo.example.com : ok=4 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
before execution
$ ssh [email protected]
Last login: Mon Jun 13 11:29:28 2022 from 192.168.178.26
[[email protected] ~]$ sudo su
[[email protected] devops]# su - postgres
Last login: Fri Jun 10 16:50:48 UTC 2022 on pts/0
[[email protected] ~]$ psql testdb
psql (10.21)
Type "help" for help.
testdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | example | table | postgres
(1 row)
testdb=# select * from example;
message
-----------------
Ansible managed
(1 row)
testdb=# \q
[[email protected] ~]$
after execution
$ ssh [email protected]
Last login: Mon Jun 13 11:37:13 2022 from 192.168.178.26
[[email protected] ~]$ sudo su
[[email protected] devops]# ls -al /backups/
total 4
drwxrwxrwx. 2 postgres root 23 Jun 13 11:36 .
dr-xr-xr-x. 18 root root 239 Jun 13 11:36 ..
-rw-r--r--. 1 postgres postgres 583 Jun 13 11:37 testdb.gz
[[email protected] devops]# zcat /backups/testdb.gz
--
-- PostgreSQL database dump
--
-- Dumped from database version 10.21
-- Dumped by pg_dump version 10.21
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: example; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.example (
message character varying(255) NOT NULL
);
ALTER TABLE public.example OWNER TO postgres;
--
-- Data for Name: example; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.example (message) FROM stdin;
Ansible managed
\.
--
-- PostgreSQL database dump complete
--
Recap
Now you know how to automate the Backup of a PostgreSQL Database with Ansible. Subscribe to the YouTube channel, Medium, Website, Twitter, and Substack to not miss the next episode of the Ansible Pilot.
Academy
Learn the Ansible automation technology with some real-life examples in my
My book Ansible By Examples: 200+ Automation Examples For Linux and Windows System Administrator and DevOps
Donate
Want to keep this project going? Please donate