skoneka.anxs-postgresql

ANXS - PostgreSQL

Help Wanted! If you can help maintain this Ansible role, please open a GitHub issue. Many people use this role and we need assistance! 💖

This Ansible role installs and sets up PostgreSQL, along with its extensions, databases, and users.

Installation

This setup works with Ansible version 2.4.0 and higher.

To install:

ansible-galaxy install ANXS.postgresql

Dependencies

ANXS.monit (Galaxy/GitHub): Needed if you want to use monit for protection. Set monit_protection: true in that case.

Compatibility Matrix

Distribution / PostgreSQL	<= 9.2	9.3	9.4	9.5	9.6	10	11
Ubuntu 14.04	❌	✔️	✔️	✔️	✔️	✔️	❓
Ubuntu 16.04	❌	✔️	✔️	✔️	✔️	✔️	❓
Debian 8.x	❌	✔️	✔️	✔️	✔️	✔️	❓
Debian 9.x	❌	✔️	✔️	✔️	✔️	✔️	❓
CentOS 6.x	❌	✔️	✔️	✔️	✔️	✔️	❓
CentOS 7.x	❌	✔️	✔️	✔️	✔️	✔️	❓
Fedora latest	❌	❌	✔️	✔️	✔️	✔️	❓

✔️ - tested, works well
❓ - expected to work in the future (please help if you can)
❗ - maybe works, not tested
❌ - PostgreSQL is no longer supported

Variables

# Basic settings
postgresql_version: 9.6
postgresql_encoding: "UTF-8"
postgresql_locale: "en_US.UTF-8"
postgresql_ctype: "en_US.UTF-8"

postgresql_admin_user: "postgres"
postgresql_default_auth_method: "trust"

postgresql_service_enabled: false # whether the service should be enabled, default is true

postgresql_cluster_name: "main"
postgresql_cluster_reset: false

# List of databases to create (optional)
postgresql_databases:
  - name: foobar
    owner: baz          # optional; specify the database owner
    hstore: 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"   # use this encoding for the database
    lc_collate: "en_GB.UTF-8"   # use this locale for the database
    lc_ctype: "en_GB.UTF-8"     # use this ctype for the database

# List of database extensions to create (optional)
postgresql_database_extensions:
  - db: foobar
    extensions:
      - hstore
      - citext

# List of users to create (optional)
postgresql_users:
  - name: baz
    pass: pass
    encrypted: no       # indicates if the password is already encrypted.

# List of schemas to create (optional)
postgresql_schemas:
  - database: foobar           # database name
    schema: acme               # schema name
    state: present

  - database: foobar           # database name
    schema: acme_baz           # schema name
    owner: baz                 # owner name
    state: present

# List of user privileges (optional)
postgresql_user_privileges:
  - name: baz                   # user name
    db: foobar                  # database
    priv: "ALL"                 # privilege string format, e.g. INSERT,UPDATE/table:SELECT/anothertable:ALL
    role_attr_flags: "CREATEDB" # role attributes

There are many more options to customize, which you can find in the defaults/main.yml

Testing

This project includes a Vagrantfile, which is an easy way to test changes to the role. Use vagrant up to start.

For more info, see the Vagrant documentation.

Once your VM is running, you can reapply the configuration using vagrant provision, or ansible-playbook tests/playbook.yml -i vagrant-inventory.

To experiment with the test play, see tests/playbook.yml and change the variables in tests/vars.yml.

If you're contributing, please test your changes in the Vagrant environment (with the targeted distribution), and ensure your change is covered in the tests found in .travis.yml.