Create a PostgreSQL Database - Ansible module postgresql_db
How to automate the creation of a "testdb" database on PostgreSQL using Ansible Playbook and postgresql_db module.
How to Create 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 Create 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
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 created 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.
Links
The Best Resources For Ansible
Certifications
The Linux Foundation Linux Performance Tuning - Build a broad toolkit to optimize Linux performance from the kernel up
Coursera Pro - Unlimited access to 7,000+ world-class courses, hands-on projects, and job-ready certificate programs—all included in your subscription
Video Course
Printed Book
Ansible For VMware by Examples
Ansible for Kubernetes by Example
Hands-on Ansible Automation
Red Hat Ansible Automation Platform
eBooks
Ansible by Examples: 200+ Automation Examples For Linux and Windows System Administrator and DevOps
Ansible Cookbook: A Comprehensive Guide to Unleashing the Power of Ansible via Best Practices, Troubleshooting, and Linting Rules with Luca Berton
Terraform By Example: A Practical Approach for Beginners to Learn Cloud Infrastructure with Terraform
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 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
Ansible Automation Platform By Example: A step-by-step guide for the most common user scenarios
demo
Let’s jump into a real-life Ansible Playbook to Create a PostgreSQL Database.
I’m going to show you how to create the testdb database in the current PostgreSQL server.
code
---
- name: postgresql demo
hosts: all
become: true
vars:
db_name: testdb
tasks:
- name: Utility present
ansible.builtin.package:
name: python3-psycopg2
state: present
- name: Create db
community.postgresql.postgresql_db:
state: present
name: "{{ db_name }}"
become: true
become_user: postgres
execution
$ ansible-playbook -i virtualmachines/demo/inventory postgresql/db_create.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Create db] ******************************************************************************************
[WARNING]: Module remote_tmp /var/lib/pgsql/.ansible/tmp did not exist and was created with a mode of
0700, this may cause issues when running as another user. To avoid this, create the remote_tmp dir with
the correct permissions manually
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_create.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Create 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: Wed Jun 8 15:09:44 2022 from 192.168.178.26
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Wed Jun 8 15:07:05 UTC 2022 on pts/1
[postgres@demo ~]$ psql testdb
psql: FATAL: database "testdb" does not exist
[postgres@demo ~]$
after execution
$ ssh [email protected]
Last login: Wed Jun 8 15:39:11 2022 from 192.168.178.26
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Wed Jun 8 15:37:54 UTC 2022 on pts/0
[postgres@demo ~]$ psql testdb
psql (10.21)
Type "help" for help.
testdb=# \q
[postgres@demo ~]$
Recap
Now you know how to Create a PostgreSQL Database with Ansible. Subscribe to the YouTube channel, Medium, and Website, X (formerly 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: 200+ Automation Examples For Linux and Windows System Administrator and DevOps
Donate
Want to keep this project going? Please donate
