AnsibleByExample

Create PostgreSQL Databases with Ansible: Easy Guide

Beginneransibledatabasemodulepostgresql_db
2024-01-013By Luca Berton

Lesson 1 of 15 in Foundations

Ansible Foundations to Production — full curriculum
  1. 1. Introduction to Ansible and Automation
  2. 2. Installing and Configuring Ansible
  3. 3. Running Ad-Hoc Commands
  4. 4. Writing Your First Playbook
  5. 5. Understanding and Using Modules
  6. 6. Variables and Facts
  7. 7. Conditionals and Loops
  8. 8. Handlers and Idempotency
  9. 9. Structuring Projects with Roles
  10. 10. Inventory Management
  11. 11. Using Ansible Vault for Secrets
  12. 12. Debugging and Troubleshooting
  13. 13. Real-World Automation Examples
  14. 14. Final Project: Deploy a Web Server
  15. 15. Bonus - What's Next with Ansible?
View path overview →

How to Create a PostgreSQL Database with Ansible?

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.

Playbook

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 Playbook
  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 Playbook] ************************************************************************************
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 Playbook] ************************************************************************************
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 devops@demo.example.com
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@Playbook ~]$ psql testdb
psql: FATAL:  database "testdb" does not exist
[postgres@Playbook ~]$

after execution

$ ssh devops@demo.example.com
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@Playbook ~]$ psql testdb
psql (10.21)
Type "help" for help.
testdb=# \q
[postgres@Playbook ~]$

code with ❤️ in GitHub

Conclusion

Now you know how to Create a PostgreSQL Database with Ansible.

All Tutorials