ansible-postgres-backup

ansible-postgres-backup is an Ansible/AWX project for PostgreSQL backups with pg_dump.

The project supports two run paths:

• Manual Ansible run with local inventory and vars files.
• AWX run with a job template and database credential.
• Real secrets are stored in AWX or ignored local vault files, not in this repo.
• Optional Slack notifications send successful backup reports through a webhook.

Requirements

• The backup VM is in the backup_servers inventory group.
• pg_dump and pg_restore are installed on the backup VM.
• The backup VM can connect to the PostgreSQL server.
• The backup VM can write to pg_backup_root, for example /var/backups/postgres.

For AWX, the controller must connect to the backup VM with a machine credential. For manual Ansible runs, the local Ansible process must connect to the backup VM.

Ansible Inventory

The inventory must contain the backup VM in the backup_servers group:

backup_servers:
  hosts:
    backup-vm:
      ansible_host: 192.0.2.10

For a local template, see inventory/hosts.example.yml.

Ansible Run

For a direct Ansible run without AWX, copy the example files to ignored local files and edit them for the target database:

cp inventory/hosts.example.yml inventory/hosts.yml
cp vars/pg_backup.example.yml vars/pg_backup.yml
cp vars/pg_backup.vault.example.yml vars/pg_backup.vault.yml

Put the real database password in vars/pg_backup.vault.yml, then encrypt it:

ansible-vault encrypt vars/pg_backup.vault.yml

Run the playbook:

ansible-playbook playbooks/pg_backup.yml \
  -i inventory/hosts.yml \
  -e @vars/pg_backup.yml \
  -e @vars/pg_backup.vault.yml \
  --ask-vault-pass

AWX Run

In AWX, the same playbook runs from a job template. Each AWX database credential represents a PostgreSQL backup target. The credential injects connection values into the job, and the job template extra vars define backup behavior.

AWX Database Credential

Create one custom credential type for PostgreSQL backup targets.

Input configuration:

fields:
  - id: pg_backup_host
    type: string
    label: PostgreSQL host
  - id: pg_backup_port
    type: string
    label: PostgreSQL port
  - id: pg_backup_database
    type: string
    label: PostgreSQL database
  - id: pg_backup_user
    type: string
    label: PostgreSQL user
  - id: pg_backup_password
    type: string
    label: PostgreSQL password
    secret: true
required:
  - pg_backup_host
  - pg_backup_port
  - pg_backup_database
  - pg_backup_user
  - pg_backup_password

Injector configuration:

extra_vars:
  pg_backup_host: "{{ pg_backup_host }}"
  pg_backup_port: "{{ pg_backup_port }}"
  pg_backup_database: "{{ pg_backup_database }}"
  pg_backup_user: "{{ pg_backup_user }}"
  pg_backup_password: "{{ pg_backup_password }}"

Create one credential from this type for each database you want to back up.

AWX Job Template

Create a job template with:

• Project: this repository.
• Inventory: the inventory that contains the backup VM.
• Playbook: playbooks/pg_backup.yml.
• Credentials:
  • machine credential for the backup VM.
  • PostgreSQL database credential.
• Schedule: the required backup schedule.

Job template extra vars:

pg_backup_name: example-main
pg_backup_root: /var/backups/postgres
pg_backup_format: custom
pg_backup_retention_days: 30
pg_backup_slack_enabled: false

pg_backup_name is used in the backup directory and file name. It may contain only letters, numbers, _, and -.

Slack Notification

Slack notification is optional. AWX can handle failed jobs, while this playbook can send the successful backup report to Slack after validation and retention cleanup.

Enable it in Ansible vars or AWX job template extra vars:

pg_backup_slack_enabled: true

Store the webhook URL as a secret in AWX or an ignored vault file:

slack_webhook_url: https://example.invalid/slack-webhook-url

The Slack message uses formatted Slack blocks and mirrors the same backup report data printed by the playbook.

Backup Output

Backups are written under:

{{ pg_backup_root }}/{{ pg_backup_name }}

Example:

/var/backups/postgres/example-main/example-main-20260623T120000Z.dump

Formats:

• custom creates .dump files.
• plain creates .sql files.

Backups are validated before retention cleanup. Custom-format backups are validated with pg_restore --list. Plain SQL backups are validated by checking that the file is not empty.

Old .dump and .sql files are deleted after a successful backup when they are older than pg_backup_retention_days.

Current Status

Implemented:

• PostgreSQL backup playbook.
• Example inventory.
• Manual Ansible run instructions.
• AWX database credential injection example.
• Backup behavior through job template extra vars.
• Single database backup output directory.
• Backup file validation after successful backups.
• Retention cleanup after successful backups.
• Backup report in play output.
• Optional Slack notification for successful backup reports.
• AWX job fails when the backup fails.

Not implemented yet:

• Cloud backup uploads.