postgresql_info – Gather information about PostgreSQL servers

From Get docs
Ansible/docs/2.8/modules/postgresql info module


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

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


© 2012–2018 Michael DeHaan
© 2018–2019 Red Hat, Inc.
Licensed under the GNU General Public License version 3.
https://docs.ansible.com/ansible/2.8/modules/postgresql_info_module.html