How to Run a SQL Command/Query on PostgreSQL with Ansible?

I’m going to show you a live Playbook 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.

Join 50+ hours of courses in our exclusive community

Playbook

  • 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 Playbook
  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 Playbook] ************************************************************************************
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 Playbook] ************************************************************************************
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

Conclusion

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, 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 Udemy 300+ Lessons Video Course.

BUY the Complete Udemy 300+ Lessons Video Course

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 250+ Ansible code

Want to keep this project going? Please donate

Patreon Buy me a Pizza