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.


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.
Links
The Best Resources For Ansible
Video Course
Printed Book
eBooks
- Ansible by Examples: 200+ Automation Examples For Linux and Windows System Administrator and DevOps
- Ansible For Windows By Examples: 50+ Automation Examples For Windows System Administrator And DevOps
- Ansible For Linux by Examples: 100+ Automation Examples For Linux System Administrator and DevOps
- Ansible Linux Filesystem By Examples: 40+ Automation Examples on Linux File and Directory Operation for Modern IT Infrastructure
- Ansible For Containers and Kubernetes By Examples: 20+ Automation Examples To Automate Containers, Kubernetes and OpenShift
- Ansible For Security by Examples: 100+ Automation Examples to Automate Security and Verify Compliance for IT Modern Infrastructure
- Ansible Tips and Tricks: 10+ Ansible Examples to Save Time and Automate More Tasks
- Ansible Linux Users & Groups By Examples: 20+ Automation Examples on Linux Users and Groups Operation for Modern IT Infrastructure
- Ansible For PostgreSQL by Examples: 10+ Examples To Automate Your PostgreSQL database
- Ansible For Amazon Web Services AWS By Examples: 10+ Examples To Automate Your AWS Modern Infrastructure
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 $
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
Donate
Want to keep this project going? Please donate