freehck.script_psql_backup

Panoramica Versioni Approfondimenti

freehck.script_psql_backup

=========

This role creates a script to perform backups of PostgreSQL databases.

It saves the database and can compress (gzip) and encrypt the backup using aes256.

It can send alerts to Slack.

You can also save your backup on S3 or send it to another host using SCP.

Role Variables

Base variables

psql_backup_host: PostgreSQL host
psql_backup_port: PostgreSQL port (default is 3306)
psql_backup_user: PostgreSQL user
psql_backup_pass: PostgreSQL password
psql_backup_db: PostgreSQL database to back up (leave empty for all databases)
psql_backup_backend_use_s3: set to true to store backup on S3
psql_backup_backend_use_scp: set to true to send backup to another host using SCP

S3 backend variables

psql_backup_s3cfg_template: template for S3 configuration (default provided)

psql_backup_s3: S3 config options in this format:

psql_backup_s3:
  username: "s3user"
  access_key: "s3user-akey"
  secret_key: "s3user-skey"
  bucket: "bucket-name"

SCP backend variables

psql_backup_scp_host: host for storing your backup
psql_backup_scp_user: user for logging into storage host
psql_backup_scp_dst: destination path on storage host for the backup
psql_backup_scp_identity_src: identity file (private key) for login

Naming

psql_backup_archive_prefix: name for the backup
psql_backup_archive_stamp: timestamp format (default is %F-%Hh%Mm%Ss, e.g., 2019-09-23-12h22m07s)
psql_backup_script_name: rename the base script if desired
psql_backup_custom_script_name: name for the actual backup script (default is psql-backup-<database_name>.sh)

Notifications

psql_backup_warn_size: size in GiB to compare against your backup
psql_backup_hostname: hostname for Slack messages
psql_backup_slack_webhook: Slack webhook for notifications

Compression and Encryption variables

psql_backup_gzip: enables gzip compression
psql_backup_encrypt_aes: enables encryption (applies after gzip)
psql_backup_encrypt_aes_key_src: AES256 key for encryption

Directories

psql_backup_script_dir: directory for the base script
psql_backup_custom_script_dir: directory for specific backup job scripts
psql_backup_conf_dir: directory for backup script configuration files
psql_backup_encrypt_aes_key_dir: directory for AES256 key
psql_backup_tmpdir: temporary results directory (default is /tmp)

Pass data outside the role

psql_backup_save_facts_about_custom_script: if true, saves the path of the generated job script for adding as a cron task.

Example Playbook

# Create PostgreSQL backup job script
- role: freehck.script_psql_backup
  psql_backup_host: "{{ db_host }}"
  psql_backup_user: "{{ db_user }}"
  psql_backup_pass: "{{ db_pass }}"
  psql_backup_db: "{{ db_name }}"
  psql_backup_backend_use_s3: no
  psql_backup_backend_use_scp: yes
  psql_backup_scp_host: "{{ hostvars['storage'].ansible_host }}"
  psql_backup_scp_identity_src: "{{ playbook_dir }}/files/id_rsa.bkp.db01"
  psql_backup_scp_user: 'file'
  psql_backup_scp_dst: '/var/www/file/public/psql-db-prod-backup'
  psql_backup_gzip: yes
  psql_backup_encrypt_aes: yes
  psql_backup_encrypt_aes_key_src: "{{ playbook_dir }}/files/aes256.bkp.key"
  psql_backup_save_facts_about_custom_script: yes
  psql_backup_logfile: "/var/log/psql-backup.log"
  tags: [ backup, psql ]

# Create cron job for the backup script
- role: freehck.crontask
  crontask_file: "backups"
  crontask_name: "backup database"
  crontask_hour: "12"
  crontask_minute: "0"
  crontask_job: "{{ psql_backup_last_generated_custom_script }}"
  crontask_user: "root"
  crontask_commented_out: false
  tags: [ backup, psql ]

Important info

After deploying the psql-backup script, run the job script from /opt/scripts without parameters. If it runs without errors and you see the backup file, everything is good. If there are issues, you may have forgotten to add the storage host to known_hosts or made an error in your s3cfg template. Double-check everything, as backups are very important.

Once you've confirmed the backup is created and stored correctly, check it thoroughly. You must ensure you can restore from it.