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

#ansible #database #module #postgresql_query #sql #postgresql #query #command #version #table

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

community.postgresql.postgresql_query
Run PostgreSQL queries

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

db string - Name of database to connect to and run queries against
query string - SQL query to run
positional_args / named_args list - List of values to be passed to the query
encoding string - Set the client encoding for the current session (e.g. UTF-8)
autocommit boolean - autocommit mode
login_user / login_password / login_unix_socket / login_host / port string - connection 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.

demo

Run a SQL Command/Query on PostgreSQL with Ansible Playbook

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.