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

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