postgresql_info – Gather information about PostgreSQL servers

New in version 2.8.

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
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
    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.
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.

  • login_user or session_role must be able to read from pg_authid.

  • 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 default authentication assumes that you are either logging in as or sudo’ing to the postgres account on the host.

  • 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.

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
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}}}]
      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
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
    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
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
 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', '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.

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
 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}}
  major
integer
 always
Major server version.

Sample:
11
  minor
integer
 always
Minor server version.

Sample:
1


Status

Authors

  • Andrew Klychkov (@Andersson007)

Hint

If you notice any issues in this documentation you can edit this document to improve it.