postgresql_info – Gather information about PostgreSQL servers¶

New in version 2.8.

Synopsis
Requirements
Parameters
Notes
See Also
Examples
Return Values
Status

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.

See

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.

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 Sample: en_US.UTF-8
		ctype string			always	Database LC_CTYPE Sample: en_US.UTF-8
		encoding string			always	Database encoding Sample: UTF8
		extensions dictionary			always	Extensions 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 Sample: {'sql': {'lanacl': '', 'lanowner': 'postgres'}}
			lanacl string		always	Language access privileges Sample: {postgres=UC/postgres,=U/postgres}
			lanowner string		always	Language owner Sample: postgres
		namespaces dictionary			always	Namespaces (schema) Sample: {'pg_catalog': {'nspacl': '{postgres=UC/postgres,=U/postgres}', 'nspowner': 'postgres'}}
			nspacl string		always	Access privileges Sample: {postgres=UC/postgres,=U/postgres}
			nspowner string		always	Schema owner Sample: postgres
		owner string			always	Database owner 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) 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 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 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 Sample: True
	member_of list				always	Role membership Sample: ['read_only_users']
	superuser boolean				always	User is a superuser or not.
	valid_until string				always	Password expiration date Sample: 9999-12-31T23:59:59.999999+00:00
settings dictionary					always	Information about run-time server parameters 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 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 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 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.