enix.postgresql_replication

This is a tool for setting up and managing a PostgreSQL replication cluster on Unix systems using Ansible.

Requirements

Supported Operating Systems:

• Debian 8 "Jessie"
• Debian 9 "Stretch"

Role Variables

This tool comes with many default settings, which you can change in your host or group variables, inventory, or playbook. To help with configuration, refer to the default settings listed in defaults/main.yml. All variable names start with postgresql_replication__.

This tool is designed to allow different cluster hosts to work independently by using Ansible groups. There are three groups you need to define in your inventory:

• postgresql_replication__group: The main group for this cluster. Default is postgresql.
• postgresql_replication__group_master: The group for the master node, which should only contain one host. Default is postgresql_master.
• postgresql_replication__group_replicas: The group for replica nodes. Default is postgresql_replicas.

Your inventory file should look like this (example in YAML format):

all:
  children:
    postgresql:
      children:
        postgresql_master:
          hosts:
            db-01:
              ansible_host: 185.145.251.12
        postgresql_replicas:
          hosts:
            db-02:
              ansible_host: 185.145.251.243

Important Variables:

• postgresql_replication__user: The username for replication authentication. Default is replicate.
• postgresql_replication__password: The password for the replication user. Change the default for production systems! Default is replicate.
• postgresql_replication__waldir: Directory for storing WAL files sent from the master to replicas.

To configure the sending servers, refer to PostgreSQL documentation:

• postgresql_replication__walsenders: Number of WAL sender processes to enable. Default is 3.
• postgresql_replication__walsegments: Number of WAL segments to keep (16MB each). Default is 64.
• postgresql_replication__trigger_file: File to trigger the master operation on a replica. Default is /tmp/MasterNow.
• postgresql_replication__bootstrap: Important. If set to "yesiwant", it will delete all data on hosts in postgresql_replication__group_replicas and take a fresh pg_basebackup from the master. Be careful, as this can lead to data loss if hosts are not properly set up.

Dependencies

Make sure to include the following tool:

• enix.postgresql

Usage

To use with Ansible Galaxy, add this to your requirements.yml:

- src: enix.postgresql_replication

Then, include it in your playbook roles:

- hosts: postgresql
  roles:
    - role: enix.postgresql_replication
      postgresql__version: 11
      postgresql__global_config_options:
        - option: listen_addresses
          value: '*'
        - option: log_min_duration_statement
          value: 1000
      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: "0.0.0.0/0", auth_method: md5}
        - {type: hostssl, database: replication, user: "{{ postgresql_replication__user }}", address: "0.0.0.0/0", auth_method: md5}
      postgresql__users:
        - {name: "{{ postgresql_replication__user }}", password: "{{ postgresql_replication__password }}", role_attr_flags: "REPLICATION"}

You can also run the role as a standalone playbook and specify which hosts to set up with the --extra-vars option:

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

Future Plans

• Implement CI with GitLab/Travis
• Add tests using Molecule

Changelog

1.1.0

• Updated group names to postgresql_master and postgresql_replicas.
• Complied with Ansible 2.8+ group naming requirements.

1.0.0

• Initial release.

License

GPLv2

Author Information

Laurent CORBES laurent.corbes@enix.fr - Enix