Skip to content

Instantly share code, notes, and snippets.

@bykvaadm
Last active June 3, 2022 03:45
Show Gist options
  • Save bykvaadm/434a4eef5392528c9c0e4788937301bc to your computer and use it in GitHub Desktop.
Save bykvaadm/434a4eef5392528c9c0e4788937301bc to your computer and use it in GitHub Desktop.
Postgres patron backup on Minio with prometheus monitoring

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

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

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

{
"version": "10",
"aliases": {
"minio": {
"url": "{{ minio_url }}",
"accessKey": "{{ postgres_ha_minio_access_key }}",
"secretKey": "{{ postgres_ha_minio_secret_key }}",
"api": "s3v4",
"path": "auto"
}
}
}
- 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
#!/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
- 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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment