--- # ============================================================================ # PostgreSQL Backup Playbook # ============================================================================ # # Purpose: Create full database backups of all PostgreSQL nodes # # Use Cases: # 1. Before cleanup/redeployment (called by postgresql-deploy.yml) # 2. Manual backup operations (run independently) # 3. Scheduled backups (via cron/systemd timer) # # Requirements: # - 'assethost' defined in inventory [all] group with ansible_host # - PostgreSQL cluster must be running # - Sufficient disk space on assethost and PostgreSQL nodes # # Usage: # ansible-playbook -i inventory.yml postgresql-backup.yml # ansible-playbook -i inventory.yml postgresql-backup.yml -e backup_destination=/custom/path # ansible-playbook -i inventory.yml postgresql-backup.yml -e skip_assethost_check=true # # Variables: # backup_destination: Base path on assethost (default: /var/backups/postgresql) # skip_assethost_check: Skip assethost validation (default: false) # # ============================================================================ - name: Backup PostgreSQL Cluster to Assethost hosts: "{{ target_nodes | default('postgresql_rw,postgresql_ro') }}" become: yes vars: backup_destination: "/var/backups/postgresql" skip_assethost_check: false tasks: # ===== PREREQUISITES ===== - name: Check if PostgreSQL is installed and running stat: path: "/usr/bin/psql" register: postgresql_installed - name: Validate PostgreSQL is running ansible.builtin.shell: | sudo -u postgres psql -t -A -c "SELECT 1;" postgres 2>/dev/null || echo "not_running" register: pg_status changed_when: false failed_when: false when: postgresql_installed.stat.exists - name: Skip backup for nodes without PostgreSQL debug: msg: "⚠️ PostgreSQL not installed or not running on {{ inventory_hostname }} - skipping backup" when: > not postgresql_installed.stat.exists or (pg_status.stdout | default('not_running') | trim == 'not_running') - name: Validate assethost exists in inventory set_fact: assethost_available: "{{ 'assethost' in groups['all'] }}" run_once: true delegate_to: localhost - name: Display assethost validation result debug: msg: | {% if not assethost_available %} ⚠️ BACKUP SKIPPED - ASSETHOST NOT DEFINED ⚠️ Backup requires 'assethost' host in inventory file. To enable backup, define assethost in your inventory: [all] assethost ansible_host=IP_ADDRESS {% else %} ✅ Assethost found: {{ hostvars['assethost']['ansible_host'] | default('assethost') }} {% endif %} run_once: true when: not skip_assethost_check - name: Fail if assethost not available (unless check skipped) fail: msg: "Assethost not defined in inventory. Add 'assethost' to [all] group or use -e skip_assethost_check=true" when: - not assethost_available - not skip_assethost_check run_once: true # ===== BACKUP EXECUTION ===== - name: Execute backup process block: - name: Generate backup timestamp set_fact: backup_timestamp: "{{ ansible_date_time.iso8601_basic_short }}" run_once: true delegate_to: localhost - name: Create backup directory on assethost file: path: "{{ backup_destination }}/{{ backup_timestamp }}" state: directory mode: "0755" delegate_to: assethost run_once: true - name: Check node replication status ansible.builtin.shell: | sudo -u postgres psql -t -A -c "SELECT pg_is_in_recovery();" postgres 2>/dev/null || echo "unknown" register: pg_recovery_status changed_when: false failed_when: false - name: Determine node role set_fact: node_role: "{{ 'primary' if pg_recovery_status.stdout | trim == 'f' else ('replica' if pg_recovery_status.stdout | trim == 't' else 'unknown') }}" - name: Display backup plan debug: msg: | Backup Plan for {{ inventory_hostname }}: - Node role: {{ node_role | upper }} - Status: {{ 'Will create backup' if node_role != 'unknown' else 'SKIPPED - PostgreSQL not responding' }} - Destination: assethost:{{ backup_destination }}/{{ backup_timestamp }}/ - name: Create local backup directory file: path: "/tmp/pg_backup_{{ backup_timestamp }}" state: directory owner: postgres group: postgres mode: "0700" when: node_role != 'unknown' - name: Create full database backup (pg_dumpall) ansible.builtin.shell: | BACKUP_DIR="/tmp/pg_backup_{{ backup_timestamp }}" BACKUP_FILE="$BACKUP_DIR/{{ inventory_hostname }}_{{ node_role }}_full.sql.gz" echo "Creating backup from {{ node_role | upper }} node..." sudo -u postgres pg_dumpall | gzip > "$BACKUP_FILE" if [ $? -eq 0 ]; then BACKUP_SIZE=$(du -h "$BACKUP_FILE" | cut -f1) echo "SUCCESS|$BACKUP_FILE|$BACKUP_SIZE" else echo "FAILED|$BACKUP_FILE|0" exit 1 fi register: backup_result when: node_role != 'unknown' changed_when: true - name: Parse backup result set_fact: backup_status: "{{ (backup_result.stdout_lines[-1] | default('')).split('|')[0] if backup_result.stdout_lines is defined and backup_result.stdout_lines | length > 0 else 'SKIPPED' }}" backup_file: "{{ (backup_result.stdout_lines[-1] | default('')).split('|')[1] if backup_result.stdout_lines is defined and backup_result.stdout_lines | length > 0 and '|' in backup_result.stdout_lines[-1] else '' }}" backup_size: "{{ (backup_result.stdout_lines[-1] | default('')).split('|')[2] if backup_result.stdout_lines is defined and backup_result.stdout_lines | length > 2 and (backup_result.stdout_lines[-1] | default('')).split('|') | length > 2 else 'N/A' }}" when: node_role != 'unknown' - name: Fetch backup from PostgreSQL node to control machine ansible.builtin.fetch: src: "{{ backup_file }}" dest: "/tmp/pg_transfer_{{ backup_timestamp }}/" flat: yes when: - node_role != 'unknown' - backup_status | default('SKIPPED') == 'SUCCESS' - name: Transfer backup to assethost ansible.builtin.copy: src: "/tmp/pg_transfer_{{ backup_timestamp }}/{{ inventory_hostname }}_{{ node_role }}_full.sql.gz" dest: "{{ backup_destination }}/{{ backup_timestamp }}/{{ inventory_hostname }}_{{ node_role }}_full.sql.gz" mode: "0644" delegate_to: assethost when: - node_role != 'unknown' - backup_status | default('SKIPPED') == 'SUCCESS' register: transfer_result - name: Clean up control machine transfer directory ansible.builtin.file: path: "/tmp/pg_transfer_{{ backup_timestamp }}/{{ inventory_hostname }}_{{ node_role }}_full.sql.gz" state: absent delegate_to: localhost when: - node_role != 'unknown' - backup_status | default('SKIPPED') == 'SUCCESS' - transfer_result is succeeded - name: Clean up local backup file file: path: "{{ backup_file }}" state: absent when: - node_role != 'unknown' - backup_status | default('SKIPPED') == 'SUCCESS' - transfer_result is succeeded - name: Clean up local backup directory file: path: "/tmp/pg_backup_{{ backup_timestamp }}" state: absent when: node_role != 'unknown' - name: Display backup status debug: msg: | Backup Status for {{ inventory_hostname }}: - Role: {{ node_role | upper }} - Status: {{ backup_status | default('SKIPPED') }} - Size: {{ backup_size | default('N/A') }} - Location: assethost:{{ backup_destination }}/{{ backup_timestamp }}/{{ inventory_hostname }}_{{ node_role }}_full.sql.gz when: node_role != 'unknown' and backup_status | default('SKIPPED') == 'SUCCESS' when: - postgresql_installed.stat.exists - pg_status.stdout | default('not_running') | trim != 'not_running' - assethost_available or skip_assethost_check # ===== BACKUP SUMMARY ===== - name: Display backup summary debug: msg: | ✅ ALL DATABASE BACKUPS COMPLETED ✅ Backup location: assethost:{{ backup_destination }}/{{ backup_timestamp }}/ To list all backups: ssh assethost "ls -lh {{ backup_destination }}/{{ backup_timestamp }}/" To restore a backup (example): scp assethost:{{ backup_destination }}/{{ backup_timestamp }}/nodename_primary_full.sql.gz /tmp/ gunzip -c /tmp/nodename_primary_full.sql.gz | sudo -u postgres psql postgres run_once: true when: - backup_timestamp is defined - assethost_available or skip_assethost_check