community.postgresql.postgresql_info – Gather information about PostgreSQL servers¶
Note
This plugin is part of the community.postgresql collection (version 1.2.0).
To install it use: ansible-galaxy collection install community.postgresql
.
To use it in a playbook, specify: community.postgresql.postgresql_info
.
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.
|
|
login_password
string
|
The password used to authenticate with.
|
|
login_unix_socket
string
|
Path to a Unix domain socket for local connections.
|
|
login_user
string
|
Default: "postgres"
|
The username used to authenticate with.
|
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
|
|
Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server.
See https://www.postgresql.org/docs/current/static/libpq-ssl.html for more information on the modes.
Default of
prefer matches libpq default. |
trust_input
boolean
added in 0.2.0 of community.postgresql
|
|
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 |
Database collation https://www.postgresql.org/docs/current/collation.html.
Sample:
en_US.UTF-8
|
||||
ctype
string
|
always |
Database LC_CTYPE https://www.postgresql.org/docs/current/multibyte.html.
Sample:
en_US.UTF-8
|
||||
encoding
string
|
always |
Database encoding https://www.postgresql.org/docs/current/multibyte.html.
Sample:
UTF8
|
||||
extensions
dictionary
|
always |
Sample:
[{'plpgsql': {'description': 'PL/pgSQL procedural language', 'extversion': {'major': 1, 'minor': 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.
|
||||
nspname
string
|
always |
Namespace where the extension is.
Sample:
pg_catalog
|
||||
languages
dictionary
|
always |
Procedural languages https://www.postgresql.org/docs/current/xplang.html.
Sample:
{'sql': {'lanacl': '', 'lanowner': 'postgres'}}
|
||||
lanacl
string
|
always |
Language access privileges https://www.postgresql.org/docs/current/catalog-pg-language.html.
Sample:
{postgres=UC/postgres,=U/postgres}
|
||||
lanowner
string
|
always |
Language owner https://www.postgresql.org/docs/current/catalog-pg-language.html.
Sample:
postgres
|
||||
namespaces
dictionary
|
always |
Namespaces (schema) https://www.postgresql.org/docs/current/sql-createschema.html.
Sample:
{'pg_catalog': {'nspacl': '{postgres=UC/postgres,=U/postgres}', 'nspowner': 'postgres'}}
|
||||
nspacl
string
|
always |
Access privileges https://www.postgresql.org/docs/current/catalog-pg-namespace.html.
Sample:
{postgres=UC/postgres,=U/postgres}
|
||||
nspowner
string
|
always |
Sample:
postgres
|
||||
owner
string
|
always |
Database owner https://www.postgresql.org/docs/current/sql-createdatabase.html.
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 |
Information about roles https://www.postgresql.org/docs/current/user-manag.html.
Sample:
[{'test_role': {'canlogin': True, 'member_of': ['user_ro'], 'superuser': False, 'valid_until': '9999-12-31T23:59:59.999999+00:00'}}]
|
||||
canlogin
boolean
|
always |
Login privilege https://www.postgresql.org/docs/current/role-attributes.html.
Sample:
True
|
||||
member_of
list
/ elements=string
|
always |
Role membership https://www.postgresql.org/docs/current/role-membership.html.
Sample:
['read_only_users']
|
||||
superuser
boolean
|
always |
User is a superuser or not.
|
||||
valid_until
string
|
always |
Password expiration date https://www.postgresql.org/docs/current/sql-alterrole.html.
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.
For more information see https://www.postgresql.org/docs/current/view-pg-settings.html.
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 |
Information about tablespaces https://www.postgresql.org/docs/current/catalog-pg-tablespace.html.
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 |
Database server version https://www.postgresql.org/support/versioning/.
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)