Try SpotterChange a PostgreSQL server configuration parameter
Authors: Andrew Klychkov (@Andersson007)
Install with ansible-galaxy collection install community.general:==1.3.14
collections:
- name: community.general
version: 1.3.14Allows to change a PostgreSQL server configuration parameter.
The module uses ALTER SYSTEM command and applies changes by reload server configuration.
ALTER SYSTEM is used for changing server configuration parameters across the entire database cluster.
It can be more convenient and safe than the traditional method of manually editing the postgresql.conf file.
ALTER SYSTEM writes the given parameter setting to the $PGDATA/postgresql.auto.conf file, which is read in addition to postgresql.conf.
The module allows to reset parameter to boot_val (cluster initial value) by I(reset=yes) or remove parameter string from postgresql.auto.conf and reload I(value=default) (for settings with postmaster context restart is required).
After change you can see in the ansible output the previous and the new parameter value and other information using returned values and M(ansible.builtin.debug) module.
- name: Restore wal_keep_segments parameter to initial state
community.general.postgresql_set:
name: wal_keep_segments
reset: yes
# Set work_mem parameter to 32MB and show what's been changed and restart is required or not
# (output example: "msg": "work_mem 4MB >> 64MB restart_req: False")
- name: Set work mem parameter
community.general.postgresql_set:
name: work_mem
value: 32mb
register: set
- ansible.builtin.debug:
msg: "{{ set.name }} {{ set.prev_val_pretty }} >> {{ set.value_pretty }} restart_req: {{ set.restart_required }}"
when: set.changed
# Ensure that the restart of PostgreSQL server must be required for some parameters.
# In this situation you see the same parameter in prev_val_pretty and value_pretty, but 'changed=True'
# (If you passed the value that was different from the current server setting).
- name: Set log_min_duration_statement parameter to 1 second
community.general.postgresql_set:
name: log_min_duration_statement
value: 1s
- name: Set wal_log_hints parameter to default value (remove parameter from postgresql.auto.conf)
community.general.postgresql_set:
name: wal_log_hints
value: default
db:
aliases:
- login_db
description:
- Name of database to connect.
type: str
name:
description:
- Name of PostgreSQL server parameter.
required: true
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
reset:
default: false
description:
- Restore parameter to initial state (boot_val). Mutually exclusive with I(value).
type: bool
value:
description:
- Parameter value to set.
- To remove parameter string from postgresql.auto.conf and reload the server configuration
you must pass I(value=default). With I(value=default) the playbook always returns
changed is true.
type: str
ca_cert:
aliases:
- ssl_rootcert
description:
- 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.
type: str
ssl_mode:
choices:
- allow
- disable
- prefer
- require
- verify-ca
- verify-full
default: prefer
description:
- Determines whether or with what priority a secure SSL TCP/IP connection will be
negotiated with the server.
- See U(https://www.postgresql.org/docs/current/static/libpq-ssl.html) for more information
on the modes.
- Default of C(prefer) matches libpq default.
type: str
login_host:
description:
- Host running the database.
type: str
login_user:
default: postgres
description:
- The username used to authenticate with.
type: str
trust_input:
default: true
description:
- If C(no), check whether values of parameters are potentially dangerous.
- It makes sense to use C(no) only when SQL injections are possible.
type: bool
version_added: 0.2.0
version_added_collection: community.general
session_role:
description:
- 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.
type: str
login_password:
description:
- The password used to authenticate with.
type: str
login_unix_socket:
description:
- Path to a Unix domain socket for local connections.
type: str
context:
description:
- PostgreSQL setting context.
returned: always
sample: user
type: str
name:
description: Name of PostgreSQL server parameter.
returned: always
sample: shared_buffers
type: str
prev_val_pretty:
description: Information about previous state of the parameter.
returned: always
sample: 4MB
type: str
restart_required:
description: Information about parameter current state.
returned: always
sample: true
type: bool
value:
description:
- Dictionary that contains the current parameter value (at the time of playbook
finish).
- Pay attention that for real change some parameters restart of PostgreSQL server
is required.
- Returns the current value in the check mode.
returned: always
sample:
unit: b
value: 67108864
type: dict
value_pretty:
description: Information about current state of the parameter.
returned: always
sample: 64MB
type: str