Ansible Pilot

Run a SQL Command/Query on PostgreSQL - Ansible module postgresql_query

How to automate the running of SQL Query / Command in a “testdb” database on PostgreSQL using Ansible Playbook and postgresql_query module.

June 18, 2022
Access the Complete Video Course and Learn Quick Ansible by 200+ Practical Lessons

How to Run a SQL Command/Query on PostgreSQL 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 Run a SQL Command/Query on PostgreSQL

Let’s talk about the Ansible module postgresql_query. The full name is community.postgresql.postgresql_query, 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 Run PostgreSQL queries. This module uses psycopg2, a Python PostgreSQL database library. You must ensure that python3-psycopg2 is installed on the host before using this module.

Parameters

Let me summarize the main parameters of the module postgresql_query. Ansible supposes that PostgreSQL is running in the target node. First of all, you need to specify the parameter db, the name of the database to connect to and run queries against. Another important parameter is query, the SQL query to run. The query could have positional or named arguments that you could specify using positional_args and named_args lists. You could also specify the encoding of the current session, for example, UTF-8. You could run the query in auto-commit mode using the autocommit parameter (default disabled) to execute in auto-commit mode when the query can’t be run inside a transaction block (e.g., VACUUM). You could also specify the connections parameters, such as host (login_host), the username (login_user), password (login_password) or Unix socket (login_unix_socket). The default connection is to localhost (127.0.0.1) on port (5432), using postgres login user. Please note that the peer authentication method must be enabled on the parameters of this connection.

The Best Resources For Ansible

Video Course

Books

demo

Let’s jump into a real-life Ansible Playbook to Run a SQL Command/Query on PostgreSQL. I’m going to show you how to run a simple query in the testdb database in showing the current version of 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: run sql
      community.postgresql.postgresql_query:
        db: "{{ db_name }}"
        query: SELECT version()
      become: true
      become_user: postgres
      register: sql_data
    - name: print
      ansible.builtin.debug:
        var: sql_data

execution

$ ansible-playbook -i virtualmachines/demo/inventory postgresql/query_version.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [run sql] ********************************************************************************************
ok: [demo.example.com]
TASK [print] **********************************************************************************************
ok: [demo.example.com] => {
    "sql_data": {
        "changed": false,
        "failed": false,
        "query": "SELECT version()",
        "query_all_results": [
            [
                {
                    "version": "PostgreSQL 10.21 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit"
                }
            ]
        ],
        "query_list": [
            "SELECT version()"
        ],
        "query_result": [
            {
                "version": "PostgreSQL 10.21 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit"
            }
        ],
        "rowcount": 1,
        "statusmessage": "SELECT 1"
    }
}
PLAY RECAP ************************************************************************************************
demo.example.com           : ok=4    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
ansible-pilot $

idempotency

$ ansible-playbook -i virtualmachines/demo/inventory postgresql/query_version.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [run sql] ********************************************************************************************
ok: [demo.example.com]
TASK [print] **********************************************************************************************
ok: [demo.example.com] => {
    "sql_data": {
        "changed": false,
        "failed": false,
        "query": "SELECT version()",
        "query_all_results": [
            [
                {
                    "version": "PostgreSQL 10.21 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit"
                }
            ]
        ],
        "query_list": [
            "SELECT version()"
        ],
        "query_result": [
            {
                "version": "PostgreSQL 10.21 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit"
            }
        ],
        "rowcount": 1,
        "statusmessage": "SELECT 1"
    }
}
PLAY RECAP ************************************************************************************************
demo.example.com           : ok=4    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
ansible-pilot $

code with ❤️ in GitHub

Recap

Now you know how to Run a SQL Command/Query on PostgreSQL to check the running PostgreSQL version with Ansible. Subscribe to the YouTube channel, Medium, Website, Twitter, and Substack 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

BUY the Complete PDF BOOK to easily Copy and Paste the 200+ Ansible code

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

FREE Top 10 Best Practices

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