samdoran / ansible-role-pgsql-replication

Configure PostgreSQL streaming replication between two nodes

PostgreSQL Streaming Replication

Configure PostgreSQL streaming replication between two or more nodes. This role was developed and tested for use on PostgreSQL for setting up a redundant database backend for Ansible Tower. This will not configure advanced clustering but will configure two PostgreSQL nodes in a master/replica configuration.

Each host defined in pgsqlrep_group_name will be added to the pg_hba.conf on the master node.

This role is meant to be installed alongside the roles included with the Ansible Tower installer.

Requirements

Ansible Tower installer roles in your roles_path as well as a properly configured Ansible Tower inventory file. You can install this role in the roles/ directory included with the Ansible Tower installer.

Add the replica database node to the Ansible Tower inventory file and define pgsqlrep_role for each database host.

[tower]
tower1 ansible_connection=local
tower2
tower3

[database]
db-master pgsqlrep_role=master

[database_replica]
db-replica pgsqlrep_role=replica

...

Role Variables

Name	Default Value	Description
pg_port	5432	PostgreSQL port
pgsqlrep_role	skip	master or replica, which determines which tasks run on the host.
pgsqlrep_user	replicator	User account that will be created and used for replication.
pgsqlrep_password	[undefined]	Password for replication account
pgsqlrep_wal_level	hot_standby	WAL level
pgsqlrep_max_wal_senders	`groups[pgsqlrep_group_name]	length * 2`
pgsqlrep_wal_keep_segments	100	Max number of WAL segments.
pgsqlrep_synchronous_commit	local	Set to on, local, or off. Setting to on will cause the master to stop accepting writes in the replica goes down. See documentation
pgsqlrep_application_name	awx	Application name used for synchronization.
pgsqlrep_group_name	database_replica	Name of the group that contains the replica database nodes.
pgsqlrep_group_name_master	database	Name of the gorup that contains the master database node.
pgsqlrep_master_address	[default IPv4 of the master]	If you need something other than the default IPv4 address, for exaample, FQDN, define it here.
pgsqlrep_replica_address	[default IPv4 of the replica(s)]	If you need something other than the default IPv4 address, for exaample, FQDN, define it here.
pgsqlrep_postgres_conf_lines	[see defaults/main.yml]	Lines in postgres.conf that are set in order to enable streaming replication.

Dependencies

The following roles from the Ansible Tower installer are required:

• repos_el
• postgresql

Example Playbook

The following playbooks were tested on CentOS 7 and may need adjustment to work with CentOS 8.

Note: This example playbook overrides the IP address for the master and replica nodes by getting the last IP from the list of all IPs on the system. This is just an example of how to override this value if the default IP address does not provide the desired IP.

Note: If you want to allow all IP addresses to connect to the master node, use:

pgsqlrep_replica_address: "{{ groups[pgsqlrep_group_name] | map('extract', hostvars, 'ansible_all_ipv4_addresses') | flatten }}"

Note: This playbook is not regularly tested and is meant as a guideline only. Use at your own risk.

- name: Configure PostgreSQL streaming replication
  hosts: database_replica

  roles:
    - role: postgres
      postgres_exec_vars_only: true

  tasks:
    - name: Find recovery.conf
      find:
        paths:
          - /var/lib/pgsql
          - /opt/rh/rh-postgresql10
        recurse: yes
        patterns: recovery.conf
      register: recovery_conf_path

    - name: Remove recovery.conf
      file:
        path: "{{ item.path }}"
        state: absent
      loop: "{{ recovery_conf_path.files }}"

    - name: Add replica to database group
      add_host:
        name: "{{ inventory_hostname }}"
        groups: database
      tags:
        - always

    - import_role:
        name: repos_el

    - import_role:
        name: packages_el
      vars:
        packages_el_install_tower: no
        packages_el_install_postgres: yes

    - import_role:
        name: postgres
      vars:
        postgres_allowed_ipv4: "0.0.0.0/0"
        postgres_allowed_ipv6: "::/0"
        postgres_username: "{{ pg_username }}"
        postgres_password: "{{ pg_password }}"
        postgres_database: "{{ pg_database }}"
        max_postgres_connections: 1024
        postgres_shared_memory_size: "{{ (ansible_memtotal_mb*0.3)|int }}"
        postgres_work_mem: "{{ (ansible_memtotal_mb*0.03)|int }}"
        postgres_maintenance_work_mem: "{{ (ansible_memtotal_mb*0.04)|int }}"
      tags:
        - postgresql_database


- name: Configure PSQL master server
  hosts: database[0]

  vars:
    pgsqlrep_master_address: "{{ hostvars[groups[pgsqlrep_group_name_master][0]]['ansible_facts']['all_ipv4_addresses'][-1] }}"
    pgsqlrep_replica_address: "[ '{{ hostvars[groups[pgsqlrep_group_name][0]]['ansible_facts']['all_ipv4_addresses'][-1] }}' ]"

  tasks:
    - import_role:
        name: samdoran.pgsql_replication


- name: Configure PSQL replica(s)
  hosts: database_replica

  vars:
    pgsqlrep_master_address: "{{ hostvars[groups[pgsqlrep_group_name_master][0]]['ansible_facts']['all_ipv4_addresses'][-1] }}"
    pgsqlrep_replica_address: "{{ hostvars[groups[pgsqlrep_group_name][0]]['ansible_facts']['all_ipv4_addresses'][-1] }}"

  tasks:
    - import_role:
        name: samdoran.pgsql_replication

This playbook can be run multiple times. Each time, it erases all the data on the replica node and creates a fresh copy of the database from the master.

If the primary database node goes down, here is a playbook that can be used to fail over to the secondary node.

- name: Gather facts
  hosts: all
  become: yes


- name: Failover PostgreSQL
  hosts: database_replica
  become: yes

  vars:
    '9':
      env:
        PATH: /usr/pgsql-{{ pgsql_version }}/bin:{{ ansible_env.PATH }}
        PGDATA: /var/lib/pgsql/{{ pgsql_version }}/data
    '10':
      env:
        PATH: /opt/rh/rh-postgresql10/root/usr/bin:{{ ansible_env.PATH }}
        PGDATA: /var/opt/rh/rh-postgresql10/lib/pgsql/data
        LIBRARY_PATH: /opt/rh/rh-postgresql10/root/usr/lib64
        JAVACONFDIRS: '/etc/opt/rh/rh-postgresql10/java:/etc/java'
        LD_LIBRARY_PATH: /opt/rh/rh-postgresql10/root/usr/lib64
        CPATH: /opt/rh/rh-postgresql10/root/usr/include
        PKG_CONFIG_PATH: /opt/rh/rh-postgresql10/root/usr/lib64/pkgconfig

  tasks:
    - name: Get the current PostgreSQL Version
      import_role:
        name: samdoran.pgsql_replication
        tasks_from: pgsql_version.yml

    - name: Promote secondary PostgreSQL server to primary
      command: pg_ctl promote
      become_user: postgres
      environment: "{{ pgsql_version.split('.')[0]['env']] }}"
      ignore_errors: yes


- name: Update Ansible Tower database configuration
  hosts: tower
  become: yes

  tasks:
    - name: Update Tower postgres.py
      lineinfile:
        dest: /etc/tower/conf.d/postgres.py
        regexp: "^(.*'HOST':)"
        line: "\\1 '{{ hostvars[groups['database_replica'][0]]['ansible_facts']['default_ipv4']['address'] }}',"
        backrefs: yes
      notify: restart tower

  handlers:
    - name: restart tower
      command: ansible-tower-service restart

License

Apache 2.0

ansible-role-pgsql-replication's Issues

pg_basebackup task fails when data directory delete in previous task

• in tasks/replica.yml data directory will be cleaned (delete entire directory) and recreate again.
• but the next step is Create base backup which will fail saying no pg_hba.conf found (no postgresql.conf as well)

TASK [samdoran.pgsql_replication : Clear out data directory] ************************************************
changed: [node05-db.lab.local]

TASK [samdoran.pgsql_replication : Create empty data directory] *********************************************
changed: [node05-db.lab.local]

TASK [samdoran.pgsql_replication : Create base backup] ******************************************************
fatal: [node05-db.lab.local]: FAILED! => {"changed": false, "cmd": "pg_basebackup -X stream -D /var/opt/rh/rh-postgresql10/lib/pgsql/data -h 10.6.1.204 -U replicator", "msg": "[Errno 2] No such file or directory", "rc": 2}

nginx role fails when calling from ansible-role-pgsql-replication playbook

Thank you for sharing this role.

Info:
ansible 2.9.3
ansible tower 3.6.1
3x Ansible nodes
2x db node (1x master and 1x replica)
Tower is running fine.

Issue:
using ansible-role-pgsql-replication to enable streaming replication on replica node. (using sample playbook as used in repo) But playbook fails at nginx role.

TASK [nginx : place self-signed SSL certificates] ***********************************************************
fatal: [node05-db.lab.local]: FAILED! => {"msg": "The task includes an option with an undefined variable. The error was: 'ansible.vars.hostvars.HostVarsVars object' has no attribute 'tower_cert'\n\nThe error appears to be in '/home/ansible/ansible-tower-setup-3.6.1-1/roles/nginx/tasks/tasks.yml': line 84, column 11, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n\n        - name: place self-signed SSL certificates\n          ^ here\n"}

Observation

• we are using hosts: database_replica in playbook (Hence the playbook will not consider any other hosts, that's fine).
• But this will skip - name: slurp self-signed SSL key task because the node is not master node (when: inventory_hostname == groups['tower'][0])
• and the tower_cert variable will be undefined because of that; hence next block will fail with undefined variable (- name: Copy the cert we just created to additional hosts)

Any workaround ?

Do we really need nginx on database_replica node ?

existing_pg_dir undefined, typo and loop issues

First of all, thanks for fixing those old issues and all worked well.
I was installing another one yesterday and used the latest from Galaxy.

Noticed 3 erros and I have added all those in a page with temp fix what I have used.
Could you please have a look and fix if possible ?

All fixes here : Issues and Fixes for Ansible PostgreSQL Repliction Role

(I hesitated to raise a PR as I am not sure those real fixes or any other workarounds avaialable)

Thanks

how can i enable and configure from the asnible Tower database which can be replicated to high availability instance instead of using external database

The Tower setup process does not configure streaming replica configuration/hot standby configurations, How to enable Streaming replication enabled and configured from the asnible Tower database can be replicated to high availability instance instead of using external database

Error in task/masters.yml

TASK [samdoran.pgsql_replication : Add trust in pg_hba.conf] ************************************************************
fatal: [support2]: FAILED! => {"msg": "Invalid data passed to 'loop', it requires a list, got this instead: 192.168.1.148. Hint: If you passed a list/dict of just one element, try adding wantlist=True to your lookup invocation or use q/query instead of lookup."}.

I could fix the error by changing the code to

- name: Add trust in pg_hba.conf
  lineinfile:
    state: present
    dest: "{{ pgsqlrep_data_path }}/pg_hba.conf"
    regexp: 'host.*replication.*{{ item }}/32.*trust'
    line: 'host    replication    {{ pgsqlrep_user }}  {{ item }}/32 trust'
  loop:
    - "{{ pgsqlrep_replica_address | list | join }}"
  notify: restart postgresql

Securize removing data folder

ansible-role-pgsql-replication/tasks/replica.yml

Line 7 in ae7bc9b

shell: rm -rf {{ pgsqlrep_data_path }}/*

This line is very dangerous: depending on variable pgsqlrep_data_path, it could delete the whole filesystem!
You should test whether pgsqlrep_data_path is defined and not empty, for instance with:

- name: Clear out data directory
  shell: rm -rf {{ pgsqlrep_data_path }}/*
  when: pgsqlrep_data_path is defined and pgsqlrep_data_path|length > 0

Handling pgsql version 10+

In your pgsql_versions.yml task file, your parser doesn't handle PgSQL when version is 4 chars:

ansible-role-pgsql-replication/tasks/pgsql_version.yml

Line 11 in 80f5366

pgsql_version: "{{ pgsql_version_check['stdout'].split()[-1][:3] }}"

I suggest to find a more reliable way to parse PgSQL version

Documentation on using bundle install

We should probably include a little information on how to use this with the bundled install. Would your preference be to add the bundle_install: True parameter on the command line, or to add it as a playbook var? I can update the doc.

Failover playbook has a typo in role name.

In the README example of the failover playbook the role name uses a - not an _

- name: Get the current PostgreSQL Version
     import_role:
       name: samdoran.pgsql-replication

Solution:
re-write 3rd line as:

name: samdoran.pgsql_replication

(Nice work BTW, worked flawlessly on a Tower 3.4.1 cluster)

Configuration folder is often not the same as data folder

ansible-role-pgsql-replication/tasks/master.yml

Line 5 in ae7bc9b

dest: "{{ pgsqlrep_data_path }}/postgresql.conf"

I think pgsqlrep_data_path should not be used for this.

I tried to understand where this configuration folder comes from. I'm not familiar with molecule, but it seems there is a problem with your repository. There is a weird folder containing (old ?) config: ./molecule/default/postgres/vars/Ubuntu.yml for instance.

pg_conf_dir seems to be declared but not used, is it referring to an old version (which shouldn't have been committed?)

Thanks

Allow the use of replication slots when configuring replication

The use of replication slots makes streaming replication more tolerant to connectivity issues between the master and replica. Without replication slots if a replica is unable to connect to the master for a long enough time then the replica will become out of sync and need to be manuall re-synced. Replication slots allow the master to keep track of what updates the replica has missed and will allow the replica to re-sync automatically when connectivity is restored.

breaking changes in dependency - nginx role in Tower 3.6.1

The nginx role was refactored and there are some issues with missing variables (web_server_ssl_cert/web_server_ssl_key, aw_group), directories (/etc/tower does not exist), etc.

seems to be that the nginx_exec_vars_only var is now meaningless (unused in the new nginx role).