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

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. Links - [`community.postgresql.postgresql_query`](https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_query_module.html) ## 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 ```yaml --- - name: postgresql Playbook hosts: all become: true vars: db_name: testdb tasks: - name: Utility present