Ansible Pilot

Rename a PostgreSQL Database - Ansible module postgresql_db

Access the Complete Video Course and Learn Quick Ansible by 200+ Practical Lessons

How to automate the delete of a "testdb" database on PostgreSQL using Ansible Playbook and postgresql_db module.

How to Rename 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 Rename a PostgreSQL Database

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

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” specify the desired state or the operation for the selected database. The option “present” means that the database should be Renamed and the option “absent” means that the database should be deleted. Other useful operations are “dump” and “restore” that uses 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 uses psycopg2, a Python PostgreSQL database library. You must ensure that python3-psycopg2 is installed on the host before using this module.

demo

Let’s jump into a real-life Ansible Playbook to Rename a PostgreSQL Database. I’m going to show you how to Rename from the testdb database to exampledb in the current PostgreSQL server.

code

---
- name: postgresql demo
  hosts: all
  become: true
  vars:
    db_name: testdb
    db_newname: exampledb
  tasks:
    - name: Utility present
      ansible.builtin.package:
        name: python3-psycopg2
        state: present
    - name: Rename db
      community.postgresql.postgresql_db:
        state: rename
        name: "{{ db_name }}"
        target: "{{ db_newname }}"
      become: true
      become_user: postgres

execution

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

idempotency

$ ansible-playbook -i virtualmachines/demo/inventory postgresql/db_rename.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Rename db] ******************************************************************************************
ok: [demo.example.com]
PLAY RECAP ************************************************************************************************
demo.example.com           : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

before execution

$ ssh [email protected]
Last login: Thu Jun 16 13:22:55 2022 from 192.168.178.26
[[email protected] ~]$ sudo su
[[email protected] devops]# su - postgres
Last login: Wed Jun 15 13:37:58 UTC 2022 on pts/0
[[email protected] ~]$ 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=#

after execution

$ ssh [email protected]
Last login: Thu Jun 16 13:24:53 2022 from 192.168.178.26
[[email protected] ~]$ sudo su
[[email protected] devops]# su - postgres
Last login: Thu Jun 16 13:23:26 UTC 2022 on pts/0
[[email protected] ~]$ psql
psql (10.21)
Type "help" for help.
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 exampledb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 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
(4 rows)
postgres=#

code with ❤️ in GitHub

Recap

Now you know how to Rename a PostgreSQL Database with Ansible. Subscribe to the YouTube channel, Medium, Website and Twitter 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: 100+ Automation Examples For Linux and Windows System Administrator and DevOps

Access the Complete Book and Learn Quick Ansible by 200+ Examples

Want to keep this project going? Please donate

Access the Complete Video Course and Learn Quick Ansible by 200+ Practical Lessons
Trustpilot
Follow me

Subscribe not to miss any new releases

June 16, 2022

FREE Top 10 Best Practices

Top 10 Best Practices of Ansible Automation: save time, reduce errors and stress