Backup your PostgreSQL dbs in Azure

Even though most of us will be using PaaS services in Azure, some tasks are still required - like keeping a copy of your PostgreSQL dbs in an azure storage account.
This is in case you want to quickly have access to a raw dump instead of relying of the recovery mechanism of the PaaS service.

Setup

Here we will be using a simple ansible playbook, scheduled to run daily.
It can be a regular cronjob or a scheduled job in AWX.

Playbook

- hosts: localhost
  gather_facts: true
  vars_files:
    - 'your_ansible_vault.yml'
    - 'your_vars.yml'

  vars:
    postgresql_dump_directory: "/data/backups/postgresql"

  tasks:

    - name: Perform postgresql backup
      block:

        - name: Make sure {{ postgresql_dump_directory }} directory exists 
          file:
            path: "{{ postgresql_dump_directory }}"
            state: directory
            owner: "{{ ansible_user }}"
            group: "{{ ansible_user }}"
            mode: "0755"
          become: true 
      
        - name: Dump databases
          postgresql_db:
            login_host: "{{ postgresql_host }}"
            login_user: "{{ postgresql_admin_user }}"
            login_password: "{{ postgresql_admin_pass }}"
            name: "{{ item }}"
            state: dump
            target: "{{ postgresql_dump_directory }}/{{ item }}_{{ ansible_date_time.date }}.sql.gz"
            target_opts: "-n public"
          loop:
             - database_1
             - database_2
             - database_3

        - name: Upload backups to Azure Account Storage
          command: /home/{{ ansible_user }}/bin/azcopy sync "{{ postgresql_dump_directory }}" "{{ sa_db_backups }}/{{ sa_container_postgresql }}{{ sas_postgresql_backups }}" --recursive=false --log-level=INFO 

      rescue:

        - name: send alert on telegram
          telegram:
              token: "{{ telegram_token }}"
              chat_id: "{{ telegram_chat_id }}"
              msg_format: markdown
              msg: "\U0001F4E4 | *PostgreSQL backup failed*\n*Date: *{{ ansible_date_time.date }}"


    - name: Get list of files older than 5 days in {{ postgresql_dump_directory }}
      find:
        paths: "{{ postgresql_dump_directory }}"
        age: 5d
      register: files_to_delete

    - name: Cleanup files older than 5 days in {{ postgresql_dump_directory }}
      file:
        path: "{{ item.path }}"
        state: absent
      with_items: "{{ files_to_delete.files }}"

Playbook steps

• Create a local directory where we dump our backups.
• Dump databases using the postgresql_db module.
• Upload files to Azure (azcopy sync).
• Send a telegram message in case of any failure.
• Keep only 5 days of data locally.

vars_files will contain either sensitive information or regular data.
For example sas_postgresql_backups define the SAS key use to access the storage account, this key should a be stored in an Ansible vault file.