postgres.yaml

---
#
# No Fuss Computing
# https://gitlab.com/nofusscomputing/projects/ansible/ansible_playbooks
#
# Playbook for backing up and restoring a PostgreSQL Database.
# tags must be sepcified for the playbook to function.
#
# image: nofusscomputing/ansible-ee >= 0.3.0
#
# Tags:
#
#   - backup
#      Dump the specified database to a file
#
#   - restore
#     Restore a database from file
#

- name: PostgresDB
  hosts: localhost
  gather_facts: false


  pre_tasks:


      # Play and task set time
    - name: Set Start Time
      ansible.builtin.set_fact:
        nfc_task_starttime: "{{ ('%Y-%m-%dT%H:%M:%S %z' | strftime) | string }}"
      no_log: "{{ nfc_pb_no_log_setup_facts | default(false) | bool }}"
      tags:
        - always


    # Setup dictionary 'nfc_automation'
    - name: Set Automation Facts
      ansible.builtin.set_fact:
        nfc_automation: {
          "time": {
            "start": "{{ nfc_task_starttime | string }}",
            "end": 0
          }
        }
      no_log: "{{ nfc_pb_no_log_setup_facts | default(false) | bool }}"
      when: nfc_automation is not defined
      tags:
        - always


    # Setup dictionary 'nfc_task'
    - name: Set Automation Facts
      ansible.builtin.set_fact:
        nfc_task: {
          "name": "{{ ansible_play_name | default('') | lower | string }}",
          "error": 0,
          "roles": "{{ ansible_play_role_names | default('') | string }}",
          "skip_tags": "{{ ansible_skip_tags | default([])| list }}",
          "start": "{{ nfc_task_starttime | string }}",
          "tags": "{{ ansible_run_tags | default([]) | list }}"
        }
      no_log: "{{ nfc_pb_no_log_setup_facts | default(false) | bool }}"
      tags:
        - always


    - name: Block - pre_tasks
      block:


        - name: Check for login credentials
          ansible.builtin.assert:
            that:
              - postgresdb_host is defined
              - postgresdb_user is defined
              - postgresdb_password is defined
            msg: You must specify login credentials
          tags:
            - always


        - name: Variable Setup - project_dir
          ansible.builtin.include_tasks:
            file: tasks/var_project_dir.yaml
            apply:
              tags:
                - always
          when: var_project_dir is not defined
          tags:
            - always

      rescue:

          # there was an error, set error object
        - name: Set error fact
          ansible.builtin.set_fact:
            nfc_task: "{{ nfc_task | combine({
                'error': 1
              }) }}"
          no_log: "{{ nfc_pb_no_log_setup_facts | default(false) | bool }}"
          tags:
            - always


      always:


          # Check if error occured and fail task
        - name: Error Check
          ansible.builtin.assert:
            that:
              - nfc_task.error | int == 0
            msg: Error occured, Fail the play run
          tags:
            - always


    # Don't use the 'roles' section.
  roles: []
    # if the included role(s) do not contain a rescue block, the playbook may stop
    # executing in this section (roles) with the post_tasks not running. This will
    # cause the artifacts to be incomplete. It's recommended to include your roles
    # in section(s) 'pre_tasks', 'tasks' or 'post_tasks' and from within a block with
   # rescue so that the playbook can complete and ensure that all artifacts are set.


  tasks:


    - name: Block - tasks
      block:

          # Check for error and fail play on error
        - name: Error Check
          ansible.builtin.assert:
            that:
              - nfc_task.error | int == 0
            msg: Error eccured, follow error path to fail play
          tags:
            - always


        - name: Backup PostgresDB
          ansible.builtin.include_tasks:
            file: tasks/backup-postgres.yaml
            apply:
              tags:
                - restore
          when: backup.postgresdb.databases | default([]) | length | int > 0
          tags:
            - never
            - backup

        - name: Check Variables
          ansible.builtin.assert:
            that:
              - postgresdb_directory_backup | default('') != ''
              - postgresdb_host | default('') != ''
              - postgresdb_user | default('') != ''
              - postgresdb_password | default('') != ''
              - postgresdb_database_name | default('') != ''
            fail_msg: "You must specify variables 'postgresdb_directory_backup/postgresdb_host/postgresdb_user/postgresdb_password/postgresdb_database_name'"
          tags:
            - never
            - restore


        - name: Check Restore Variables
          ansible.builtin.assert:
            that:
              - postgresdb_filename_restore | default('') != ''
            fail_msg: "You must specify variable 'postgresdb_filename_restore'"
          tags:
            - never
            - restore


        - name: Dependency Versions
          ansible.builtin.command:
            cmd: "{{ item.cmd }}"
          changed_when: false
          failed_when: false
          loop: "{{ check_versions }}"
          tags:
            - always
          vars:
            check_versions:
              - cmd: "pip show psycopg2"
              - cmd: "pg_dump --version"


        - name: Set Backup Facts
          ansible.builtin.set_fact:
            postgresdb_filename_backup: recovery.sql
          tags:
            - never
            - restore


        - name: Backup database
          tags:
            - never
            - restore-backup
          block:


            - name: Dump an existing database to a file
              community.postgresql.postgresql_db:
                name: "{{ postgresdb_database_name }}"
                state: dump
                target: "{{ postgresdb_directory_backup }}/{{ postgresdb_filename_backup }}"
                login_host: "{{ postgresdb_host }}"
                login_user: "{{ postgresdb_user }}"
                login_password: "{{ postgresdb_password }}"


            - name: Backup File Details
              ansible.builtin.stat:
                name: "{{ postgresdb_directory_backup }}/{{ postgresdb_filename_backup }}"

          rescue:

            - name: Remove Failed Backup
              ansible.builtin.file:
                name: "{{ postgresdb_directory_backup }}/{{ postgresdb_filename_backup }}"
                state: absent

            - name: Fail Play
              ansible.builtin.assert:
                that:
                  - false
                fail_msg: Backup of Postgres database '{{ postgresdb_database_name }}' Failed!


        - name: Restore database
          tags:
            - never
            - restore
          block:

            - name: Drop DB
              community.postgresql.postgresql_db:
                name: "{{ postgresdb_database_name }}"
                state: absent
                force: true
                login_host: "{{ postgresdb_host }}"
                login_user: "{{ postgresdb_user }}"
                login_password: "{{ postgresdb_password }}"


            - name: Create DB
              community.postgresql.postgresql_db:
                name: "{{ postgresdb_database_name }}"
                state: present
                login_host: "{{ postgresdb_host }}"
                login_user: "{{ postgresdb_user }}"
                login_password: "{{ postgresdb_password }}"

            - name: Restore an existing database to a file
              community.postgresql.postgresql_db:
                name: "{{ postgresdb_database_name }}"
                state: restore
                target: "{{ postgresdb_directory_backup }}/{{ postgresdb_filename_restore }}"
                login_host: "{{ postgresdb_host }}"
                login_user: "{{ postgresdb_user }}"
                login_password: "{{ postgresdb_password }}"

          always:

            - name: Remove the restore dump
              ansible.builtin.file:
                name: "{{ postgresdb_directory_backup }}/restore.sql"
                state: absent


        - name: Create Postgres User
          ansible.builtin.include_tasks:
            file: tasks/postgres/create_user.yaml
            apply:
              tags:
                - always
          tags:
            - never
            - create_user


        - name: Create Postgres Database
          ansible.builtin.include_tasks:
            file: tasks/postgres/create_database.yaml
            apply:
              tags:
                - always
          tags:
            - never
            - create_database


      rescue:


          # there was an error, set error object
        - name: Set error fact
          ansible.builtin.set_fact:
            nfc_task: "{{ nfc_task | combine({
                'error': 1
              }) }}"
          no_log: "{{ nfc_pb_no_log_setup_facts | default(false) | bool }}"
          tags:
            - always


      always:


          # Check if error occured and fail task
        - name: Error Check
          ansible.builtin.assert:
            that:
              - nfc_task.error | int == 0
            msg: Error occured, Fail the play run
          tags:
            - always


  post_tasks:

    - name: Tasks post_task
      block:


          # Check for error and fail play on error
        - name: Error Check
          ansible.builtin.assert:
            that:
              - nfc_task.error | int == 0
            msg: Error occured, follow error path to fail play
          tags:
            - always


        ########################################################################
        #
        # Your tasks here
        #
        ########################################################################


      rescue:


          # there was an error, set error object
        - name: Set error fact
          ansible.builtin.set_fact:
            nfc_task: "{{ nfc_task | combine({
                'error': 1
              }) }}"
          no_log: "{{ nfc_pb_no_log_setup_facts | default(false) | bool }}"
          tags:
            - always


      always:


          # Task and automation end time.
        - name: Fetch End time
          ansible.builtin.set_fact:
            nfc_task_endtime: "{{ '%Y-%m-%dT%H:%M:%S %z' | strftime }}"
          no_log: "{{ nfc_pb_no_log_setup_facts | default(false) | bool }}"
          tags:
            - always


          # Set task end time
        - name: Set task Facts
          ansible.builtin.set_fact:
            nfc_tasks: "{{ nfc_tasks | default([]) + [ nfc_task | combine({
                'end': nfc_task_endtime | string
              }) ] }}"
          no_log: "{{ nfc_pb_no_log_setup_facts | default(false) | bool }}"
          tags:
            - always


          # Set Automation end time.
          # every playbook must set this variable so that the end time
          # is equal to the fail time or the end of a group of playbooks.
        - name: Set automation end time
          ansible.builtin.set_fact:
            nfc_automation: "{{ nfc_automation | combine({
                'time': nfc_automation.time | combine({
                  'end': nfc_task_endtime | string
                })
              }) }}"
            nfc_task_endtime: null
          no_log: "{{ nfc_pb_no_log_setup_facts | default(false) | bool }}"
          tags:
            - always


          # Set the artifacts as a fact for subsequent playbook useage
          # Note: variable 'per_host' must be 'false' so that the artifacts
          # work within AWX / Ansible Automation Platform.
        - name: Create Automation Artifact
          ansible.builtin.set_stats:
            data:
              nfc_automation: "{{ nfc_automation }}"
              nfc_tasks: "{{ nfc_tasks }}"
            per_host: false
          tags:
            - always


          # Final error check to fail the entire play run on error 
        - name: Error Check
          ansible.builtin.assert:
            that:
              - nfc_task.error | int == 0
            msg: Error occured, Fail the play run
          tags:
            - always


  vars:
    ansible_connection: local          # String, Default 'local' DONT change.
    postgresdb_directory_backup: ""    # String, Required
    postgresdb_host: ""                # String, Required
    postgresdb_user: ""                # String, Required
    postgresdb_password: ""            # String, Required
    postgresdb_database_name: ""       # String, Required
    postgresdb_filename_restore: ""    # String, Required for Restore ONLY

    nfc_pb_awx_tower_template:


      - name: "PostgresDB"
        state: absent


      - name: "postgres/user/create"
        new_name: Database/PostgreSQL/User/Create
        ask_tags_on_launch: false
        description: |
          Playbook to create PostgreSQL User
        execution_environment: "No Fuss Computing EE"
        job_type: "run"
        job_tags: create_user
        labels:
          - create
          - database
          - postgres
          - user
        use_fact_cache: true
        survey_enabled: true
        survey_spec: |
          [
            {
                "max": 1024,
                "min": 3,
                "type": "text",
                "choices": "",
                "default": "",
                "required": true,
                "variable": "postgresql_user_name",
                "new_question": true,
                "question_name": "User Name",
                "question_description": "User to create"
            },
            {
                "max": 1024,
                "min": 3,
                "type": "password",
                "choices": "",
                "default": "",
                "required": true,
                "variable": "postgresql_user_password",
                "new_question": false,
                "question_name": "User Password",
                "question_description": "Password for user to create"
            },
            {
                "max": 1024,
                "min": 2,
                "type": "text",
                "choices": "",
                "default": "",
                "required": false,
                "variable": "postgresql_user_priv",
                "new_question": true,
                "question_name": "User Privileges",
                "question_description": "Privileges to grant user"
            }
          ]



      - name: "postgres/database/create"
        state: absent