Restore a PostgreSQL Database — Ansible module postgresql_db

By Luca Berton · Published 2024-01-01 · Category: installation

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

Restore a PostgreSQL Database — Ansible module postgresql_db

How to Restore a PostgreSQL Database with Ansible?

Ansible Restore 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
targer _string_ - filename

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

## Playbook Let's jump into a real-life Ansible Playbook to Restore a PostgreSQL Database. I'm going to show you how to restore a backup file stored in /backups/testdb.gzin the testdb database in the current PostgreSQL server.

code

---
- 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: Restore db
      community.postgresql.postgresql_db:
        state: restore
        name: "{{ db_name }}"
        target: "{{ backup_dir }}/{{ db_name }}.gz"
      become: true
      become_user: postgres

execution

$ ansible-playbook -i virtualmachines/demo/inventory postgresql/db_restore.yml
PLAY [postgresql Playbook] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Backup directory] ***********************************************************************************
ok: [demo.example.com]
TASK [Restore db] *****************************************************************************************
changed: [demo.example.com]
PLAY RECAP ************************************************************************************************
demo.example.com           : ok=4    changed=1    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

NOT idempotency

$ ansible-playbook -i virtualmachines/demo/inventory postgresql/db_restore.yml
PLAY [postgresql Playbook] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Backup directory] ***********************************************************************************
ok: [demo.example.com]
TASK [Restore 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 devops@demo.example.com
Last login: Mon Jun 13 14:07:09 2022 from 192.168.178.26
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Mon Jun 13 14:06:32 UTC 2022 on pts/0
[postgres@Playbook ~]$ psql
psql (10.21)
Type "help" for help.
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)
postgres=# \q
[postgres@Playbook ~]$ psql testdb
psql (10.21)
Type "help" for help.
testdb=# \dt
Did not find any relations.
testdb=# \q
[postgres@Playbook ~]$ exit
logout
[root@demo devops]# ls -al /backups/
total 4
drwxrwxrwx.  2 postgres root      23 Jun 13 11:47 .
dr-xr-xr-x. 18 root     root     239 Jun 13 11:47 ..
-rw-r--r--.  1 postgres postgres 583 Jun 13 11:47 testdb.gz
[root@demo devops]# file /backups/testdb.gz 
/backups/testdb.gz: gzip compressed data, last modified: Mon Jun 13 11:47:49 2022, from Unix, original size 1157
[root@demo 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
--
[root@demo devops]#

after execution

$ ssh devops@demo.example.com
Last login: Mon Jun 13 14:10:08 2022 from 192.168.178.26
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Mon Jun 13 14:07:44 UTC 2022 on pts/0
[postgres@Playbook ~]$ 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
 Ansible managed
(2 rows)
testdb=#

code with ❤️ in GitHub

Conclusion

Now you know how to automate the Restore of a backup in a PostgreSQL Database with Ansible.

Category: installation

Watch the video: Restore a PostgreSQL Database — Ansible module postgresql_db — Video Tutorial

Browse all Ansible tutorials · AnsiblePilot Home

AnsiblePilot — Master Ansible Automation

Popular Topics

About Luca Berton