nephosolutions.postgresql

NephoSolutions - Ansible Role for PostgreSQL

This Ansible role helps you install and set up PostgreSQL, along with any required extensions, databases, and users.

It is based on the ANXS/postgresql project.

Installation

This role has been tested with Ansible versions 2.4.0 and higher.

To install it, run:

ansible-galaxy install ANXS.postgresql

Example Playbook

Here’s how to use this role in an Ansible playbook:

    - hosts: postgresql-server
      become: yes
      roles:
         - { role: anxs.postgresql }

Dependencies

If you want to use Monit for protection, include the ANXS.monit role (Galaxy/GitHub). In this case, set monit_protection: true.

Compatibility Matrix

This table shows compatibility between different operating systems and PostgreSQL versions:

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

✅ - tested and works well
⚠️ - not recommended for production
❓ - may work in the future (help if you can)
❗ - might work, but not tested
❌ - PostgreSQL is no longer supported

Variables

Here are some key configuration options you can set:

# Basic settings
postgresql_version: 11
postgresql_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_service_enabled: false # Set to true to enable the service

postgresql_cluster_name: "main"
postgresql_cluster_reset: false

# Databases to create (optional)
postgresql_databases:
  - name: foobar
    owner: baz          # optional owner
    hstore: yes         # install hstore extension (yes/no)
    uuid_ossp: yes      # install uuid-ossp extension (yes/no)
    citext: yes         # install citext extension (yes/no)
    encoding: "UTF-8"   # specific encoding for this database
    lc_collate: "en_GB.UTF-8"   # collation for this database
    lc_ctype: "en_GB.UTF-8"     # ctype for this database

# Database extensions to create (optional)
postgresql_database_extensions:
  - db: foobar
    extensions:
      - hstore
      - citext

# Users to create (optional)
postgresql_users:
  - name: baz
    pass: pass               # user password
    encrypted: yes           # whether to encrypt the password

# Schemas to create (optional)
postgresql_database_schemas:
  - database: foobar
    schema: acme
    state: present

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

# User privileges to assign (optional)
postgresql_user_privileges:
  - name: baz                   # user name
    db: foobar                  # database
    priv: "ALL"                 # privilege level
    role_attr_flags: "CREATEDB" # role attributes

For more options, check the defaults/main.yml file.

Testing

This project includes a Vagrantfile, which is a simple way to test role changes. Start it with vagrant up.

For more information about Vagrant, refer to the Vagrant docs.

Once your virtual machine is running, you can use vagrant provision or ansible-playbook tests/playbook.yml -i vagrant-inventory to apply changes.

To modify the test play, see tests/playbook.yml and update variables in tests/vars.yml.

If you're contributing to this project, please test your changes in the Vagrant environment (using the correct distribution) and try to cover your changes in the tests found in .travis.yml.