Drop a PostgreSQL Database - Ansible module postgresql_db
By Luca Berton · Published 2024-01-01 · Category: installation
How to automate the delete of a "testdb" database on PostgreSQL using Ansible Playbook and postgresql_db module.
How to Drop a PostgreSQL Database with Ansible?
See also: Automate PostgreSQL Backups with Ansible Playbook
Ansible Drop a PostgreSQL Database
community.postgresql.postgresql_db- Add or remove PostgreSQL databases from a remote host
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
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 Dropd 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.
See also: community.postgresql.postgresql_db: Create & Manage PostgreSQL Databases
Links
## Playbook
Let's jump into a real-life Ansible Playbook to Drop a PostgreSQL Database.
code
---
- name: postgresql Playbook
hosts: all
become: true
vars:
db_name: testdb
tasks:
- name: Utility present
ansible.builtin.package:
name: python3-psycopg2
state: present
- name: Drop db
community.postgresql.postgresql_db:
state: present
name: "{{ db_name }}"
become: true
become_user: postgresexecution
$ ansible-playbook -i virtualmachines/demo/inventory postgresql/db_drop.yml
PLAY [postgresql Playbook] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Drop db] ********************************************************************************************
changed: [demo.example.com]
PLAY RECAP ************************************************************************************************
demo.example.com : ok=3 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0idempotency
$ ansible-playbook -i virtualmachines/demo/inventory postgresql/db_drop.yml
PLAY [postgresql Playbook] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Drop db] ********************************************************************************************
ok: [demo.example.com]
PLAY RECAP ************************************************************************************************
demo.example.com : ok=3 changed=0 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0before execution
$ ssh devops@demo.example.com
Last login: Wed Jun 15 13:24:58 2022 from 192.168.178.26
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Wed Jun 15 13:24:11 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=#after execution
$ ssh devops@demo.example.com
Last login: Wed Jun 15 13:37:41 2022 from 192.168.178.26
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Wed Jun 15 13:36:41 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
(3 rows)
postgres=#Conclusion
Now you know how to Drop a PostgreSQL Database with Ansible.
See also: Rename a PostgreSQL Database - Ansible module postgresql_db
Related Articles
Category: installation
Watch the video: Drop a PostgreSQL Database - Ansible module postgresql_db — Video Tutorial