Ansible Role: PostgreSQL

Opinionated installation and configuration of PostgreSQL, including databases, users, pg_hba.conf rules and optionally TimescaleDB.

Requirements

Operating System	Version
Debian	13
Ubuntu	24.04 LTS

The PostgreSQL version is the one shipped by the distribution (Debian 13: 17, Ubuntu 24.04: 16). Set postgresql_version together with postgresql_pgdg_enabled to use another major version from postgresql.org.

The role never upgrades a cluster between major versions. That is a manual step (pg_upgradecluster).

Dependencies

None.

Variables

Variable	Required	Default	Description
postgresql_version	No	""	Major version to install; empty uses the distribution version
postgresql_pgdg_enabled	No	false	Add the postgresql.org (pgdg) apt repository
postgresql_listen_addresses	No	"localhost"	Value of listen_addresses
postgresql_port	No	"5432"	Value of port
postgresql_config	No	{ max_connections: 100 }	Extra postgresql.conf settings, see below
postgresql_hba_rules	No	[]	Extra pg_hba.conf rules, see HBA rules
postgresql_timescaledb_enabled	No	false	Add the Timescale repository, install TimescaleDB and preload it
postgresql_timescaledb_hold	No	false	Hold the TimescaleDB packages, see TimescaleDB hold
postgresql_users	No	[]	Database users to create, see Users
postgresql_databases	No	[]	Databases (and extensions) to create, see Databases

postgresql_config is rendered to /etc/postgresql/<major>/main/conf.d/90-ansible.conf. Booleans and numbers are written as is, other values are quoted. With postgresql_timescaledb_enabled, shared_preload_libraries is set to timescaledb unless you set it yourself in postgresql_config.

Users

Key	Required	Default	Description
name	Yes		Role name
password	No	not set	Password (sops-encrypted var)
role_attr_flags	No	NOSUPERUSER,NOCREATEDB,NOCREATEROLE	Role attribute flags

Databases

Key	Required	Default	Description
name	Yes		Database name
owner	No	postgres	Database owner
encoding	No	cluster default	Encoding
lc_collate	No	cluster default	Collation
lc_ctype	No	cluster default	Character classification
template	No	cluster default	Template database
extensions	No	[]	Extensions to create in the DB

HBA rules

Key	Required	Default	Description
source	Yes		Address or CIDR
contype	No	host	Connection type
users	No	all	User(s) the rule applies to
databases	No	all	Database(s) the rule applies
method	No	scram-sha-256	Authentication method

Example

Secrets such as database passwords belong in sops-encrypted inventory vars.

postgresql_timescaledb_enabled: true

postgresql_config:
  max_connections: 100
  shared_buffers: "256MB"

postgresql_users:
  - name: "zabbix"
    password: "CHANGE-ME"

postgresql_databases:
  - name: "zabbix"
    owner: "zabbix"
    extensions:
      - "timescaledb"

TimescaleDB hold

The TimescaleDB packages bundle the libraries of several recent extension versions, and older ones get dropped over time. If your database extension is behind the package (for example because Zabbix does not support the newest version yet), set postgresql_timescaledb_hold: true so an apt upgrade cannot remove the library the database still needs. When it is false the role releases the hold. Update the extension first (ALTER EXTENSION timescaledb UPDATE), then release the hold.

Remote access

By default PostgreSQL only listens on localhost. To allow other hosts:

postgresql_listen_addresses: "*"
postgresql_hba_rules:
  - source: "192.168.10.0/24"
    users: "zabbix"
    databases: "zabbix"

Tags

If you call the role without tags, it will execute all of the stages below.

Tags	Purpose
postgresql_install	Only manage PostgreSQL (and TimescaleDB)
postgresql_config	Only manage configuration and HBA rules
postgresql_users	Only manage database users
postgresql_databases	Only manage databases and their extensions

Usage

Run the role through Semaphore using playbook.yaml. The playbook first runs ansible-galaxy install -f -r roles/requirements.yml on localhost, then executes the postgresql role.