Postgres patron backup on Minio with prometheus monitoring

README.md

Скрипт не претендует на истину первой инстанции

Да, я знаю что есть способ ${the_best_pg_backup_method}

Это просто скрипт которым я поделился - хочешь пользуйся, не хочешь - иди мимо

config.json

{
  "version": "10",
  "aliases": {
    "minio": {
      "url": "{{ minio_url }}",
      "accessKey": "{{ postgres_ha_minio_access_key }}",
      "secretKey": "{{ postgres_ha_minio_secret_key }}",
      "api": "s3v4",
      "path": "auto"
    }
  }
}

main.yaml

- name: Collect only facts returned by facter
  setup: filter=ansible_distribution*

- name: setup minio backup
  when: postgres_ha_enable_minio_backup | bool
  block:
  - name: set usr bin path
    set_fact:
      usr_bin_path: "{% if 'Flatcar' in ansible_distribution %}/opt/bin{% else %}/usr/local/bin{% endif %}"

  - name: ensure mc binary
    get_url:
      url: https://dl.min.io/client/mc/release/linux-amd64/mc
      dest: "{{ usr_bin_path }}"
      mode: 0755

  - name: ensure mc dir
    file:
      path: /root/.mc
      state: directory

  - name: ensure minio config
    template:
      src: config.json
      dest: /root/.mc/config.json
      mode: 0400
      owner: root
      group: root

  - name: ensure minio backup script
    template:
      src: postgres-backup.sh
      dest: "{{ usr_bin_path }}"
      mode: 0755

  - name: ensure postgres-backup service
    copy:
      dest: /etc/systemd/system/postgres-backup.service
      content: |
        [Unit]
        Description=postgres backup on minio

        [Service]
        Type=oneshot
        ExecStart=/bin/sh -c '{{ usr_bin_path }}/postgres-backup.sh'

  - name: ensure postgres-backup timer
    copy:
      dest: /etc/systemd/system/postgres-backup.timer
      content: |
        [Unit]
        Description=backup postgresql databases every night at 3am

        [Timer]
        OnCalendar=*-*-* 3:00:00

  - name: enable and start systemd timer
    systemd:
      name: postgres-backup.timer
      state: started
      enabled: yes

postgres-backup.sh

#!/bin/sh
databases="{{ postgres_ha_minio_databases_to_backup }}"
prom_file="postgres_backup_exporter.prom"
TEXTFILE_COLLECTOR_DIR="/var/prometheus/textfile_exporter"

if [ "$(docker exec patroni /usr/bin/python3 /var/lib/postgresql/.local/bin/patronictl -c /etc/patroni/patroni.yml list | awk '$0 ~ /.*Leader.*running.*/ {print $2}' | head -1)" == "$(hostname)" ]; then
  # doing backup
  for db in ${databases}; do
    echo "running backup of ${db}";
    docker exec patroni pg_dump -Z 4 -Fc ${db} | {{ usr_bin_path }}/mc --insecure pipe --attr "bug=fix" minio/{{ postgres_ha_minio_bucket }}/${db}-$(date +%Y-%b-%d-%X).sql;
  done

  # inform monitoring about backups state
  rm ${TEXTFILE_COLLECTOR_DIR}/${prom_file}.* 2>/dev/null || true
  echo "# HELP postgres_backup_time_created timestamp when backup was created" >> "${TEXTFILE_COLLECTOR_DIR}/${prom_file}.$$"
  echo "# TYPE postgres_backup_time_created gauge" >> "${TEXTFILE_COLLECTOR_DIR}/${prom_file}.$$"
  for db in ${databases}; do
    raw_backup_time=$({{ usr_bin_path }}/mc --insecure ls minio/{{ postgres_ha_minio_bucket }}/${db}- | tail -n 1 | grep -oP '(?<=\[).*(?=\])' )
    backup_time=$(date -d "${raw_backup_time}" +%s)
    if [ "x${backup_time}" == "x" ]; then backup_time="0"; fi
    echo "postgres_backup_time_created{db=\"${db}\"} ${backup_time}" >> "${TEXTFILE_COLLECTOR_DIR}/${prom_file}.$$"
  done
  echo "# HELP postgres_backup_size size of backup in bytes" >> "${TEXTFILE_COLLECTOR_DIR}/${prom_file}.$$"
  echo "# TYPE postgres_backup_size gauge" >> "${TEXTFILE_COLLECTOR_DIR}/${prom_file}.$$"
  for db in ${databases}; do
    size=$({{ usr_bin_path }}/mc --insecure ls minio/{{ postgres_ha_minio_bucket }}/${db}- --json | tail -n 1 | grep -oP '(?<=size":).*(?=,"key)')
    echo "postgres_backup_size{db=\"${db}\"} ${size}" >> "${TEXTFILE_COLLECTOR_DIR}/${prom_file}.$$"
  done
  mv "${TEXTFILE_COLLECTOR_DIR}/${prom_file}.$$" "${TEXTFILE_COLLECTOR_DIR}/${prom_file}"

  # doing cleanup of old backups
  for db in ${databases}; do
  {{ usr_bin_path }}/mc --insecure ls minio/{{ postgres_ha_minio_bucket }}/${db}- | tac | tail -n +11 | awk '{print "minio/{{ postgres_ha_minio_bucket }}/"$5}' | {{ usr_bin_path }}/mc rm --insecure --stdin --force
  done
fi

postgres.rules

  - alert: PostgresBackupSeemsTooSmall
    expr: postgres_backup_size < 100
    for: 5m
    labels:
      severity: WARNING
    annotations:
      summary: Postgres backup size < 100bytes
      instance: "{{ $labels.instance }}"
      value: "{{ $value }} bytes"
  - alert: PostgresBackupOldThan28h
    expr: time() - postgres_backup_time_created > 100500
    for: 5m
    labels:
      severity: WARNING
    annotations:
      summary: Postgres backup was made later than 28h
      instance: "{{ $labels.instance }}"
      database: "{{ $labels.db }}"
      value: "{{ $value }} seconds"