W3cubDocs

/Ansible

community.postgresql.postgresql_info – Gather information about PostgreSQL servers

Note

This plugin is part of the community.postgresql collection (version 1.5.0).

You might already have this collection installed if you are using the ansible package. It is not included in ansible-core. To check whether it is installed, run ansible-galaxy collection list.

To install it, use: ansible-galaxy collection install community.postgresql.

To use it in a playbook, specify: community.postgresql.postgresql_info.

Synopsis

  • Gathers information about PostgreSQL servers.

Requirements

The below requirements are needed on the host that executes this module.

  • psycopg2

Parameters

Parameter Choices/Defaults Comments
ca_cert
string
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.

aliases: ssl_rootcert
db
string
Name of database to connect.

aliases: login_db
filter
list / elements=string
Limit the collected information by comma separated string or YAML list.
Allowable values are version, databases, in_recovery, settings, tablespaces, roles, replications, repl_slots.
By default, collects all subsets.
You can use shell-style (fnmatch) wildcard to pass groups of values (see Examples).
You can use '!' before value (for example, !settings) to exclude it from the information.
If you pass including and excluding values to the filter, for example, filter=!settings,ver, the excluding values will be ignored.
login_host
string
Host running the database.
If you have connection issues when using localhost, try to use 127.0.0.1 instead.
login_password
string
The password this module should use to establish its PostgreSQL session.
login_unix_socket
string
Path to a Unix domain socket for local connections.
login_user
string
Default:
"postgres"
The username this module should use to establish its PostgreSQL session.
port
integer
Default:
5432
Database port to connect to.

aliases: login_port
session_role
string
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.
ssl_mode
string
    Choices:
  • allow
  • disable
  • prefer ←
  • require
  • verify-ca
  • verify-full
Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server.
Default of prefer matches libpq default.
trust_input
boolean
added in 0.2.0 of community.postgresql
    Choices:
  • no
  • yes ←
If no, check whether a value of session_role is potentially dangerous.
It makes sense to use no only when SQL injections via session_role are possible.

Notes

Note

  • Supports check_mode.
  • The default authentication assumes that you are either logging in as or sudo’ing to the postgres account on the host.
  • To avoid “Peer authentication failed for user postgres” error, use postgres user as a become_user.
  • This module uses psycopg2, a Python PostgreSQL database adapter. You must ensure that psycopg2 is installed on the host before using this module.
  • If the remote host is the PostgreSQL server (which is the default case), then PostgreSQL must also be installed on the remote host.
  • For Ubuntu-based systems, install the postgresql, libpq-dev, and python-psycopg2 packages on the remote host before using this module.
  • The ca_cert parameter requires at least Postgres version 8.4 and psycopg2 version 2.4.3.

See Also

See also

community.postgresql.postgresql_ping

The official documentation on the community.postgresql.postgresql_ping module.

Examples

# Display info from postgres hosts.
# ansible postgres -m postgresql_info

# Display only databases and roles info from all hosts using shell-style wildcards:
# ansible all -m postgresql_info -a 'filter=dat*,rol*'

# Display only replications and repl_slots info from standby hosts using shell-style wildcards:
# ansible standby -m postgresql_info -a 'filter=repl*'

# Display all info from databases hosts except settings:
# ansible databases -m postgresql_info -a 'filter=!settings'

- name: Collect PostgreSQL version and extensions
  become: yes
  become_user: postgres
  community.postgresql.postgresql_info:
    filter: ver*,ext*

- name: Collect all info except settings and roles
  become: yes
  become_user: postgres
  community.postgresql.postgresql_info:
    filter: "!settings,!roles"

# On FreeBSD with PostgreSQL 9.5 version and lower use pgsql user to become
# and pass "postgres" as a database to connect to
- name: Collect tablespaces and repl_slots info
  become: yes
  become_user: pgsql
  community.postgresql.postgresql_info:
    db: postgres
    filter:
    - tablesp*
    - repl_sl*

- name: Collect all info except databases
  become: yes
  become_user: postgres
  community.postgresql.postgresql_info:
    filter:
    - "!databases"

Return Values

Common return values are documented here, the following are the fields unique to this module:

Key Returned Description
databases
dictionary
always
Information about databases.

Sample:
[{'postgres': {'access_priv': '', 'collate': 'en_US.UTF-8', 'ctype': 'en_US.UTF-8', 'encoding': 'UTF8', 'owner': 'postgres', 'size': '7997 kB'}}]
database_name
dictionary
always
Database name.

Sample:
template1
access_priv
string
always
Database access privileges.

Sample:
=c/postgres_npostgres=CTc/postgres
collate
string
always
Sample:
en_US.UTF-8
ctype
string
always
Sample:
en_US.UTF-8
encoding
string
always
Sample:
UTF8
extensions
dictionary
always
Sample:
[{'plpgsql': {'description': 'PL/pgSQL procedural language', 'extversion': {'major': 1, 'minor': 0, 'raw': '1.0'}}}]
extdescription
string
if existent
Extension description.

Sample:
PL/pgSQL procedural language
extversion
dictionary
always
Extension description.
major
integer
always
Extension major version.

Sample:
1
minor
integer
always
Extension minor version.
raw
string
always
Extension full version.

Sample:
1.0
nspname
string
always
Namespace where the extension is.

Sample:
pg_catalog
languages
dictionary
always
Sample:
{'sql': {'lanacl': '', 'lanowner': 'postgres'}}
lanacl
string
always
Sample:
{postgres=UC/postgres,=U/postgres}
lanowner
string
always
Sample:
postgres
namespaces
dictionary
always
Sample:
{'pg_catalog': {'nspacl': '{postgres=UC/postgres,=U/postgres}', 'nspowner': 'postgres'}}
nspacl
string
always
Sample:
{postgres=UC/postgres,=U/postgres}
nspowner
string
always
Sample:
postgres
owner
string
always
Sample:
postgres
publications
dictionary
added in 0.2.0 of community.postgresql
 if configured
Information about logical replication publications (available for PostgreSQL 10 and higher) https://www.postgresql.org/docs/current/logical-replication-publication.html.
Content depends on PostgreSQL server version.

Sample:
{'pub1': {'ownername': 'postgres', 'puballtables': True, 'pubinsert': True, 'pubupdate': True}}
size
string
always
Database size in bytes.

Sample:
8189415
subscriptions
dictionary
added in 0.2.0 of community.postgresql
 if configured
Information about replication subscriptions (available for PostgreSQL 10 and higher) https://www.postgresql.org/docs/current/logical-replication-subscription.html.
Content depends on PostgreSQL server version.

Sample:
[{'my_subscription': {'ownername': 'postgres', 'subenabled': True, 'subpublications': ['first_publication']}}]
in_recovery
boolean
always
Indicates if the service is in recovery mode or not.
pending_restart_settings
list / elements=string
always
List of settings that are pending restart to be set.

Sample:
['shared_buffers']
repl_slots
dictionary
if existent
Replication slots (available in 9.4 and later) https://www.postgresql.org/docs/current/view-pg-replication-slots.html.

Sample:
{'slot0': {'active': False, 'database': None, 'plugin': None, 'slot_type': 'physical'}}
active
boolean
always
True means that a receiver has connected to it, and it is currently reserving archives.

Sample:
True
database
string
always
Database name this slot is associated with, or null.

Sample:
acme
plugin
string
always
Base name of the shared object containing the output plugin this logical slot is using, or null for physical slots.

Sample:
pgoutput
slot_type
string
always
The slot type - physical or logical.

Sample:
logical
replications
dictionary
if pg_stat_replication view existent
Information about the current replications by process PIDs https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE.

Sample:
[{'76580': {'app_name': 'standby1', 'backend_start': '2019-02-03 00:14:33.908593+03', 'client_addr': '10.10.10.2', 'client_hostname': '', 'state': 'streaming', 'usename': 'postgres'}}]
app_name
string
if existent
Name of the application that is connected to this WAL sender.

Sample:
acme_srv
backend_start
string
always
Time when this process was started, i.e., when the client connected to this WAL sender.

Sample:
2019-02-03 00:14:33.908593+03
client_addr
string
always
IP address of the client connected to this WAL sender.
If this field is null, it indicates that the client is connected via a Unix socket on the server machine.

Sample:
10.0.0.101
client_hostname
string
always
Host name of the connected client, as reported by a reverse DNS lookup of client_addr.
This field will only be non-null for IP connections, and only when log_hostname is enabled.

Sample:
dbsrv1
state
string
always
Current WAL sender state.

Sample:
streaming
usename
string
always
Name of the user logged into this WAL sender process ('usename' is a column name in pg_stat_replication view).

Sample:
replication_user
roles
dictionary
always
Sample:
[{'test_role': {'canlogin': True, 'member_of': ['user_ro'], 'superuser': False, 'valid_until': '9999-12-31T23:59:59.999999+00:00'}}]
canlogin
boolean
always
Sample:
True
member_of
list / elements=string
always
Sample:
['read_only_users']
superuser
boolean
always
User is a superuser or not.
valid_until
string
always
Sample:
9999-12-31T23:59:59.999999+00:00
settings
dictionary
always
Information about run-time server parameters https://www.postgresql.org/docs/current/view-pg-settings.html.

Sample:
[{'work_mem': {'boot_val': '4096', 'context': 'user', 'max_val': '2147483647', 'min_val': '64', 'setting': '8192', 'sourcefile': '/var/lib/pgsql/10/data/postgresql.auto.conf', 'unit': 'kB', 'val_in_bytes': 4194304, 'vartype': 'integer'}}]
boot_val
string
always
Parameter value assumed at server startup if the parameter is not otherwise set.

Sample:
4096
context
string
always
Context required to set the parameter's value.

Sample:
user
max_val
string
always
Maximum allowed value of the parameter (null for non-numeric values).

Sample:
2147483647
min_val
string
always
Minimum allowed value of the parameter (null for non-numeric values).

Sample:
64
pending_restart
boolean
always
True if the value has been changed in the configuration file but needs a restart; or false otherwise.
Returns only if settings is passed.
pretty_val
string
always
Value presented in the pretty form.

Sample:
2MB
setting
string
always
Current value of the parameter.

Sample:
49152
sourcefile
string
always
Configuration file the current value was set in.
Null for values set from sources other than configuration files, or when examined by a user who is neither a superuser or a member of pg_read_all_settings.
Helpful when using include directives in configuration files.

Sample:
/var/lib/pgsql/10/data/postgresql.auto.conf
unit
string
always
Implicit unit of the parameter.

Sample:
kB
val_in_bytes
integer
if supported
Current value of the parameter in bytes.

Sample:
2147483647
vartype
string
always
Parameter type (bool, enum, integer, real, or string).

Sample:
integer
tablespaces
dictionary
always
Sample:
[{'test': {'spcacl': '{postgres=C/postgres,andreyk=C/postgres}', 'spcoptions': ['seq_page_cost=1'], 'spcowner': 'postgres'}}]
spcacl
string
always
Tablespace access privileges.

Sample:
{postgres=C/postgres,andreyk=C/postgres}
spcoptions
list / elements=string
always
Tablespace-level options.

Sample:
['seq_page_cost=1']
spcowner
string
always
Owner of the tablespace.

Sample:
test_user
version
dictionary
always
Sample:
{'version': {'major': 10, 'minor': 6}}
full
string
added in 1.2.0 of community.postgresql
 always
Full server version.

Sample:
13.2
major
integer
always
Major server version.

Sample:
11
minor
integer
always
Minor server version.

Sample:
1
patch
integer
added in 1.2.0 of community.postgresql
 if supported
Patch server version.

Sample:
5
raw
string
added in 1.2.0 of community.postgresql
 always
Full output returned by ``SELECT version()``.

Sample:
PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.2.1 20201125 (Red Hat 10.2.1-9), 64-bit


Authors

  • Andrew Klychkov (@Andersson007)

© 2012–2018 Michael DeHaan
© 2018–2021 Red Hat, Inc.
Licensed under the GNU General Public License version 3.
https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_info_module.html