postgresql_info – Gather information about PostgreSQL servers¶
New in version 2.8.
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 , 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. |
Notes¶
Note
- 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
- postgresql_ping – Check remote PostgreSQL server availability
- The official documentation on the 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
postgresql_info:
filter: ver*,ext*
- name: Collect all info except settings and roles
become: yes
become_user: postgres
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
postgresql_info:
db: postgres
filter:
- tablesp*
- repl_sl*
- name: Collect all info except databases
become: yes
become_user: postgres
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
|
||||
size
string
|
always |
Database size in bytes.
Sample:
8189415
|
||||
pending_restart_settings
list
|
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
|
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', 'vartype': 'integer', 'val_in_bytes': 4194304}}]
|
||||
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
|
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}}
|
||||
major
integer
|
always |
Major server version.
Sample:
11
|
||||
minor
integer
|
always |
Minor server version.
Sample:
1
|
Status¶
- This module is not guaranteed to have a backwards compatible interface. [preview]
- This module is maintained by the Ansible Community. [community]
Authors¶
- Andrew Klychkov (@Andersson007)
Hint
If you notice any issues in this documentation, you can edit this document to improve it.