enix.postgresql

This is a role for deploying and setting up PostgreSQL on UNIX systems using Ansible.

Requirements

Supported Systems:

• Debian 9 "Stretch"
• Debian 10 "Buster"
• Debian 11 "Bullseye"
• Ubuntu 20.04 "Focal"
• Ubuntu 22.04 "Jammy"

Role Variables

This role comes with many default settings that you can change in your host/group variables, your inventory, or your playbook. For help with configuration, check the default settings in defaults/main.yml.

• postgresql__version: The version of PostgreSQL to install, defaults to 14. Other available versions: 10, 11, 12, 13, 14.

• postgresql__extensions: The PostgreSQL extensions to install.

• postgresql__global_config_options: Global configuration options for postgresql.conf. Common options include:

  postgresql__global_config_options:
    - option: listen_addresses
      value: '*'
    - option: log_min_duration_statement
      value: 1000
    - option: bonjour
      value: off
      state: absent
  

• postgresql__includeconf: A list of configuration files to be created and added to conf.d/.

• postgresql__hba_entries: Host-Based Authentication entries to configure, which will replace PostgreSQL default settings. Mandatory fields are type, database, user, auth_method. Optional fields include address, ip_address, ip_mask, auth_options. Example configuration:

  postgresql_hba_entries:
    - {type: local, database: all, user: postgres, auth_method: peer}
    - {type: local, database: all, user: all, auth_method: peer}
    - {type: host, database: all, user: all, address: '127.0.0.1/32', auth_method: md5}
    - {type: host, database: all, user: all, address: '::1/128', auth_method: md5}
    - {type: local, database: replication, user: all, auth_method: peer}
    - {type: host, database: replication, user: all, address: '127.0.0.1/32', auth_method: md5}
    - {type: host, database: replication, user: all, address: '::1/128', auth_method: md5}
  

• postgresql__users: Users to create on the PostgreSQL server and their credentials:

  postgresql__users:
    - {name: "foobar",
      password: "supersecure",
      encrypted: yes,
      expires: 'infinity',
      state: 'present',
      conn_limit: none,
      priv: none,
      role_attr_flags: none,
      db: none}
  

• postgresql__databases: The databases to be created on the server. The only required field is name:

  postgresql__databases:
    - {name: test,
      lc_collate: 'en_US.UTF-8',
      lc_ctype: 'en_US.UTF-8',
      encoding: 'UTF-8',
      template: 'template0',
      owner: postgres,
      extension: [],
      state: 'present'}
  

• postgresql__install_pgbackrest: Install pgbackrest software https://pgbackrest.org.

• postgresql__install_pg_auto_failover: Install pg_auto_failover software https://github.com/citusdata/pg_auto_failover.

• postgresql__disable_initdb: Disable the creation of the default main cluster. Useful for pg_auto_failover cluster creation or just installing the package.

Dependencies

• None

Usage

Use Ansible galaxy in your requirements.yml file:

- src: enix.postgresql

Then add it to your playbook:

- hosts: all
  roles:
    - role: enix.postgresql
      postgresql__var: true

You can also run the role as a playbook. You will be asked which hosts to set up and can further customize the play using --extra-vars.

ansible-playbook -i inventory --extra-vars='{...}' main.yml

Still to Do

• Check if deploying on a replica; if yes, avoid creating databases/users.
• Add CI tests using Molecule.

Changelog

2.1.0

• Added installation options for pgbackrest and pg_auto_failover.
• Added disable_initdb option to prevent setting up default PostgreSQL cluster.

2.0.0

• Added support for PostgreSQL versions 13 and 14.
• Added support for Debian versions 10 and 11.
• Added support for Ubuntu versions 20.04 and 22.04.
• Removed support for Debian Jessie.
• Switched to Molecule tests.
• Updated to use new Ansible FQDN tasks.

1.2.0

• Added support for PostgreSQL version 12.

1.1.0

• Added support for an extension list in database definitions.

1.0.0

• Initial version.

License

GPLv2

Author Information

Laurent CORBES laurent.corbes@enix.fr - http://www.enix.io