ANXS - PostgreSQL

---

Help Wanted! If you are able and willing to help maintain this Ansible role then please open a GitHub issue. A lot of people seem to use this role and we (quite obviously) need assistance! 💖

Ansible role which installs and configures PostgreSQL, extensions, databases and users.

Installation

This has been tested on Ansible 2.4.0 and higher.

To install:

ansible-galaxy install anxs.postgresql

Example Playbook

An example how to include this role:

---
- hosts: postgresql-serverroles:
- role: ANXS.postgresqlbecome: yes

An example how to include this role as a task:

---
- hosts: postgresql-servertasks:
- block: # workaround, see https://stackoverflow.com/a/56558842
- name: PSQL installation and configurationinclude_role:
name: ANXS.postgresqlvars:
postgresql_users:
- name: abcpassword: abcbecome: true

Dependencies

• ANXS.monit (Galaxy/GH) if you want monit protection (in that case, you should set monit_protection: true)

Compatibility matrix

Distribution / PostgreSQL	11	12	13	14	15	16
Debian 11.x	⛔	✅	✅	✅	✅	✅
Debian 12.x	⛔	✅	✅	✅	✅	✅
Rockylinux 8.x	⛔	✅	✅	✅	✅	✅
Rockylinux 9.x	⛔	✅	✅	✅	✅	✅
Ubuntu 20.04.x	⛔	✅	✅	✅	✅	✅
Ubuntu 22.04.x	⛔	✅	✅	✅	✅	✅

• ✅ - tested, works fine
• ⚠️ - not for production use
• ❔ - will work in the future (help out if you can)
• ⁉️ - maybe works, not tested
• ⛔ - has reached End of Life (EOL)

Variables

# Basic settingspostgresql_version: 16postgresql_encoding: "UTF-8"postgresql_locale: "en_US.UTF-8"postgresql_ctype: "en_US.UTF-8"postgresql_admin_user: "postgres"postgresql_default_auth_method: "peer"postgresql_cluster_name: mainpostgresql_cluster_reset: false# List of databases to be created (optional)# Note: for more flexibility with extensions use the postgresql_database_extensions setting.postgresql_databases:
- name: foobarowner: baz # optional; specify the owner of the databasehstore: yes # flag to install the hstore extension on this database (yes/no)uuid_ossp: yes # flag to install the uuid-ossp extension on this database (yes/no)citext: yes # flag to install the citext extension on this database (yes/no)encoding: "UTF-8"# override global {{ postgresql_encoding }} variable per databasestate: "present"# optional; one of 'present', 'absent', 'dump', 'restore'lc_collate: "en_GB.UTF-8"# override global {{ postgresql_locale }} variable per databaselc_ctype: "en_GB.UTF-8"# override global {{ postgresql_ctype }} variable per database# List of database extensions to be created (optional)postgresql_database_extensions:
- db: foobarextensions:
- hstore
- citext# List of users to be created (optional)postgresql_users:
- name: bazpass: passencrypted: yes # if password should be encrypted, postgresql >= 10 does only accepts encrypted passwordsstate: "present"# optional; one of 'present', 'absent'# List of schemas to be created (optional)postgresql_database_schemas:
- database: foobar # database nameschema: acme # schema namestate: present
- database: foobar # database nameschema: acme_baz # schema nameowner: baz # owner namestate: present# List of user privileges to be applied (optional)postgresql_user_privileges:
- name: baz # user namedb: foobar # databasepriv: "ALL"# privilege string format: example: INSERT,UPDATE/table:SELECT/anothertable:ALLrole_attr_flags: "CREATEDB"# role attribute flags

There's a lot more knobs and bolts to set, which you can find in the defaults/main.yml

Testing - Molecule

This project comes with a molecule configuration. Please see ./molecule/README.md

Examples:

molecule test

Testing - Vagrant

This project comes with a Vagrantfile, this is a fast and easy way to test changes to the role, fire it up with vagrant up

See vagrant docs for getting setup with vagrant

Once your VM is up, you can reprovision it using either vagrant provision, or ansible-playbook tests/playbook.yml -i vagrant-inventory

If you want to toy with the test play, see tests/playbook.yml, and change the variables in tests/vars.yml

If you are contributing, please first test your changes within the vagrant environment, (using the targeted distribution), and if possible, ensure your change is covered in the tests found in .travis.yml

License

Licensed under the MIT License. See the LICENSE file for details.

Thanks

Creator:

• Pjan Vandaele

Maintainers:

• Jonathan Lozada D.
• Jonathan Freedman
• Sergei Antipov
• Greg Clough
• Magnus Lübeck
• Leo C.
• Laurent Lavaud

Top Contributors:

• David Farrington
• Jesse Lang
• Michael Conrad
• Sébastien Alix
• Copperfield
• T. Soulabail
• Ralph von der Heyden

Feedback, bug-reports, requests, ...

Are welcome!