Run a SQL Command/Query on PostgreSQL - Ansible module postgresql_query
By Luca Berton · Published 2024-01-01 · Category: installation
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 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
## 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
execution
idempotency
Conclusion
Now you know how to Run a SQL Command/Query on PostgreSQL to check the running PostgreSQL version with Ansible.
Related Articles • Ansible Become Guide • Ansible Inventory Guide
Category: installation
Watch the video: Run a SQL Command/Query on PostgreSQL - Ansible module postgresql_query — Video Tutorial