Try SpotterRun PostgreSQL queries
| "added in version" 2.8 of ansible.builtin"
Authors: Felix Archambault (@archf), Andrew Klychkov (@Andersson007), Will Rouesnel (@wrouesnel)
preview | supported by community
pipInstall with pip install ansible==2.9.24
Runs arbitrary PostgreSQL queries.
Can run queries from SQL script files.
Does not run against backup files. Use M(postgresql_db) with I(state=restore) to run queries on files made by pg_dump/pg_dumpall utilities.
- name: Simple select query to acme db
postgresql_query:
db: acme
query: SELECT version()
- name: Select query to db acme with positional arguments and non-default credentials
postgresql_query:
db: acme
login_user: django
login_password: mysecretpass
query: SELECT * FROM acme WHERE id = %s AND story = %s
positional_args:
- 1
- test
- name: Select query to test_db with named_args
postgresql_query:
db: test_db
query: SELECT * FROM test WHERE id = %(id_val)s AND story = %(story_val)s
named_args:
id_val: 1
story_val: test
- name: Insert query to test_table in db test_db
postgresql_query:
db: test_db
query: INSERT INTO test_table (id, story) VALUES (2, 'my_long_story')
- name: Run queries from SQL script
postgresql_query:
db: test_db
path_to_script: /var/lib/pgsql/test.sql
positional_args:
- 1
- name: Example of using autocommit parameter
postgresql_query:
db: test_db
query: VACUUM
autocommit: yes
- name: >
Insert data to the column of array type using positional_args.
Note that we use quotes here, the same as for passing JSON, etc.
postgresql_query:
query: INSERT INTO test_table (array_column) VALUES (%s)
positional_args:
- '{1,2,3}'
# Pass list and string vars as positional_args
- name: Set vars
set_fact:
my_list:
- 1
- 2
- 3
my_arr: '{1, 2, 3}'
- name: Select from test table by passing positional_args as arrays
postgresql_query:
query: SELECT * FROM test_array_table WHERE arr_col1 = %s AND arr_col2 = %s
positional_args:
- '{{ my_list }}'
- '{{ my_arr|string }}'
db:
aliases:
- login_db
description:
- Name of database to connect to and run queries against.
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
query:
description:
- SQL query to run. Variables can be escaped with psycopg2 syntax U(http://initd.org/psycopg/docs/usage.html).
type: str
ca_cert:
aliases:
- ssl_rootcert
description:
- Specifies the name of a file containing SSL certificate authority (CA) certificate(s).
- If the file exists, the server's certificate will be verified to be signed by one
of these authorities.
type: str
ssl_mode:
choices:
- allow
- disable
- prefer
- require
- verify-ca
- verify-full
default: prefer
description:
- Determines whether or with what priority a secure SSL TCP/IP connection will be
negotiated with the server.
- See U(https://www.postgresql.org/docs/current/static/libpq-ssl.html) for more information
on the modes.
- Default of C(prefer) matches libpq default.
type: str
autocommit:
default: false
description:
- Execute in autocommit mode when the query can't be run inside a transaction block
(e.g., VACUUM).
- Mutually exclusive with I(check_mode).
type: bool
version_added: '2.9'
version_added_collection: ansible.builtin
login_host:
default: ''
description:
- Host running the database.
- If you have connection issues when using C(localhost), try to use C(127.0.0.1) instead.
type: str
login_user:
default: postgres
description:
- The username this module should use to establish its PostgreSQL session.
type: str
named_args:
description:
- Dictionary of key-value arguments to pass to the query. When the value is a list,
it will be converted to PostgreSQL array.
- Mutually exclusive with I(positional_args).
type: dict
session_role:
description:
- Switch to session_role after connecting. The specified session_role must be a role
that the current login_user is a member of.
- Permissions checking for SQL commands is carried out as though the session_role
were the one that had logged in originally.
type: str
login_password:
default: ''
description:
- The password this module should use to establish its PostgreSQL session.
type: str
path_to_script:
description:
- Path to SQL script on the remote host.
- Returns result of the last query in the script.
- Mutually exclusive with I(query).
type: path
positional_args:
description:
- List of values to be passed as positional arguments to the query. When the value
is a list, it will be converted to PostgreSQL array.
- Mutually exclusive with I(named_args).
type: list
login_unix_socket:
default: ''
description:
- Path to a Unix domain socket for local connections.
type: str
query: description: Query that was tried to be executed. returned: always sample: SELECT * FROM bar type: str query_result: description: - List of dictionaries in column:value form representing returned rows. returned: changed sample: - Column: Value1 - Column: Value2 type: list rowcount: description: Number of affected rows. returned: changed sample: 5 type: int statusmessage: description: Attribute containing the message returned by the command. returned: always sample: INSERT 0 1 type: str