---
- name: ensure a postgresql server is running and correctly configured
  hosts: dbservers
  gather_facts: no
  vars:
    pg_admin_pass: verysecret
  tasks:
    - name: check whether package is installed
      command: rpm -q postgresql-server
      ignore_errors: yes
      changed_when: false
      register: pg_is_there
      tags:
        - install

    - name: check whether PG_VERSION exists in datadir
      become: yes
      stat:
        path: /var/lib/pgsql/data/PG_VERSION
      register: pg_version_is_there
      tags:
        - install

    - name: ensure the server is at latest version
      become: true
      yum:
        name:
          - postgresql-server
          - python3-psycopg2
        state: latest
      register: install_status
      tags:
        - install

    - name: stop the server if this was an upgrade
      become: true
      service:
        name: postgresql
        state: stopped
      when:
        - not pg_is_there.failed
        - pg_version_is_there.stat.exists
        - install_status.changed
      tags:
        - install

    - name: decide what option to use for postgresql-setup
      set_fact:
        pgsetup: --initdb
      when: >
        (install_status.changed and pg_is_there.failed)
        or
        not pg_version_is_there.stat.exists
      tags:
        - install

    - name: decide what option to use for postgresql-setup
      set_fact:
        pgsetup: --upgrade
      when:
        - install_status.changed
        - not pg_is_there.failed
      tags:
        - install

    - name: initialise or upgrade the database
      become: yes
      command: postgresql-setup {{ pgsetup }}
      when: pgsetup is defined
      tags:
        - install

    - name: ensure the service is started
      become: yes
      service:
        name: postgresql
        state: started
      tags:
        - config

    - name: open ports if necessary
      become: yes
      firewalld:
        service: postgresql
        state: enabled
        immediate: yes
        permanent: yes
      tags:
        - config

    - name: ensure md5 authentication is configured for remote users (ipv4)
      become: yes
      become_user: postgres
      lineinfile:
        path: /var/lib/pgsql/data/pg_hba.conf
        regex: '^host\s+all\s+all\s+0\.0\.0\.0/0\s+'
        line: "host  all  all 0.0.0.0/0  md5"
      notify: reload postgres
      tags:
        - config

    - name: ensure md5 authentication is configured for remote users (ipv6)
      become: yes
      become_user: postgres
      lineinfile:
        path: /var/lib/pgsql/data/pg_hba.conf
        regex: '^host\s+all\s+all\s+::/0\s+'
        line: "host  all  all ::/0  md5"
      notify: reload postgres
      tags:
        - config

    - name: ensure the server is listening on all interfaces
      become: yes
      become_user: postgres
      lineinfile:
        path: /var/lib/pgsql/data/postgresql.conf
        regex: '^#?listen_addresses\s.*'
        line: "listen_addresses '*'"
      notify: restart postgres
      tags:
        - config

    - name: flush handlers
      meta: flush_handlers

    - name: ensure an admin user exists
      become: yes
      become_user: postgres
      postgresql_user:
        name: admin
        password: "{{ pg_admin_pass }}"
        state: present
        login_unix_socket: "/var/run/postgresql"
        role_attr_flags: SUPERUSER
      tags:
        - config

    - name: ensure the driver is installed on client machine
      become: true
      delegate_to: workstation.lab.example.com
      yum:
        name:
          - python3-psycopg2
        state: latest
      tags:
        - test
        - never

    - name: test the connection works
      delegate_to: workstation.lab.example.com
      postgresql_ping:
        login_host: "{{ inventory_hostname }}"
        login_user: admin
        login_password: "{{ pg_admin_pass }}"
        db: postgres
      register: ping_result
      tags:
        - test
        - never

    - assert:
        that: ping_result.is_available
      tags:
        - test
        - never

  handlers:
    - name: reload postgres
      become: yes
      service:
        name: postgresql
        state: reloaded

    - name: restart postgres
      become: yes
      service:
        name: postgresql
        state: restarted

...