fidanf.postgresql_ha

PostgreSQL High Availability (HA)

This guide is about installing and setting up a PostgreSQL high-availability cluster using repmgr. It can also work for standalone setups. You can add dependencies, extensions, databases, and users with this setup.

Tested on:

• Debian 10.x :heavy_check_mark:
• Debian 11.x :heavy_check_mark:
• Ubuntu 18.04.x :heavy_check_mark:
• Ubuntu 20.04.x :heavy_check_mark:

• PostgreSQL HA
  • Requirements
  • Installation
  • Dependencies
  • Example Inventory
  • Role variables
  • Example Playbook
• Useful commands to run after your first installation
  • Verifying cluster functionality
  • Show cluster status
  • List nodes and their attributes
  • Register (clone) an additional standby node
• Register former primary as a standby node after automatic failover
• License

Requirements

• Python version 3.8 or higher
• Ansible-core version 2.12 or higher

Check the requirements.txt file for detailed dependencies used in this setup.

Recommended for each PostgreSQL host:

• Python3 should be in your PATH
• Pip3 should be in your PATH
• Install psycopg2-binary (requires libpq-dev package)

You can review prepare.yml to see an example Python 3 setup.

Installation

To install, use the following commands:

Shell

ansible-galaxy collection install community.crypto
ansible-galaxy install fidanf.postgresql_ha

Requirements file

---
roles:
- src: https://github.com/fidanf/ansible-role-postgresql-ha
  name: fidanf.postgresql_ha
  version: master

collections:
  - community.crypto

Dependencies

• cryptography (needed on the Ansible host)

Example Inventory

Refer to inventory.yml for detailed variables about groups and hosts.

Role Variables

The default variables are divided into two files:

• 001-postgresql.yml
• 002-repmgr.yml

To understand the purpose and valid values for each variable, check the Jinja templates in the templates directory. Original default configuration files are also included (.orig).

Example Playbook

---
- hosts: pgcluster
  gather_facts: yes
  become: yes
  roles:
    - name: fidanf.postgresql_ha
      vars:
        # Required configuration items
        repmgr_target_group: pgcluster
        repmgr_master: pgsql01
        repmgr_failover: automatic
        repmgr_promote_command: /usr/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file
        repmgr_follow_command: /usr/bin/repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n
        repmgr_monitoring_history: "yes"
        repmgr_connection_check_type: query
        repmgr_log_level: DEBUG
        repmgr_reconnect_attempts: 2
        repmgr_reconnect_interval: 10
        # Basic settings
        postgresql_version: 13
        postgresql_cluster_name: main
        postgresql_cluster_reset: false # TODO: Needs to be tested for repmgr
        postgresql_listen_addresses: "*"
        postgresql_port: 5432
        postgresql_wal_level: "replica"
        postgresql_max_wal_senders: 10
        postgresql_max_replication_slots: 10
        postgresql_wal_keep_segments: 100
        postgresql_hot_standby: on
        postgresql_archive_mode: on
        postgresql_archive_command: "test ! -f /tmp/%f && cp %p /tmp/%f"
        postgresql_ext_install_repmgr: yes
        postgresql_shared_preload_libraries:
          - repmgr
        # PostgreSQL logging
        postgresql_log_checkpoints: on
        postgresql_log_connections: on
        postgresql_log_disconnections: on
        postgresql_log_temp_files: 0
        # pg_hba.conf rules
        postgresql_pg_hba_custom:
          - { type: "host", database: "all", user: "all", address: "192.168.56.0/24", method: "md5" }
          - { type: "host", database: "replication", user: "{{ repmgr_user }}", address: "192.168.56.0/24", method: "trust" }  
          - { type: "host", database: "replication", user: "{{ repmgr_user }}", address: "127.0.0.1/32", method: "trust" }  
          - { type: "host", database: "{{ repmgr_database }}", user: "{{ repmgr_user }}", address: "127.0.0.1/32", method: "trust" }  
          - { type: "host", database: "{{ repmgr_database }}", user: "{{ repmgr_user }}", address: "192.168.56.0/32", method: "trust" }  
        # Databases
        postgresql_databases:
          - name: "{{ repmgr_database }}"
            owner: "{{ repmgr_user }}"
            encoding: "UTF-8"
          - name: testdb
            owner: admin
            encoding: "UTF-8"
        # Users
        postgresql_users:
          - name: "{{ repmgr_user }}"
            pass: "{{ repmgr_password }}"
          - name: admin
            pass: secret # PostgreSQL >=10 does not accept unencrypted passwords
            encrypted: yes
        # Roles
        postgresql_user_privileges:
          - name: "{{ repmgr_user }}"
            db: "{{ repmgr_database }}"
            priv: "ALL"
            role_attr_flags: "SUPERUSER,REPLICATION"
          - name: admin
            db: testdb
            role_attr_flags: "SUPERUSER"

Useful commands to run after your first installation

Verifying cluster functionality

ansible pgcluster -b --become-user postgres -m shell -a "repmgr cluster crosscheck"

Show cluster status

ansible pgcluster -b --become-user postgres -m shell -a "repmgr cluster show"

List nodes and their attributes

ansible pgcluster -b --become-user postgres -m shell -a "repmgr node status"

Register (clone) an additional standby node

# Assuming current primary hostname is pgsql01
ansible-playbook myplaybook.yml -l 'pgsql04' -e 'repmgr_primary_hostname=pgsql01' -vv 

Register former primary as a standby node after automatic failover

postgres@pgsql01:~$ pg_ctlcluster 13 main stop
postgres@pgsql01:~$ repmgr standby clone --force -h pgsql02 -U repmgr -d repmgr
postgres@pgsql01:~$ pg_ctlcluster 13 main start
postgres@pgsql01:~$ repmgr standby register --force

Alternatively, you can use the repmgr node rejoin with pg_rewind:

repmgr node rejoin -d repmgr -U repmgr -h pgsql02 --verbose --force-rewind=/usr/lib/postgresql/13/bin/pg_rewind

License

MIT / BSD