Ansible Role: Postgres Backups

• Installs a script to back up PostgreSQL databases.
• Can back up multiple hosts.
• Manages scheduled tasks (cron) for regular backups.

What Gets Backed Up?

• Global objects (like roles and tablespaces) are compressed using gzip. This can be turned off. It's like running pg_dumpall --globals-only | gzip.
• All readable databases are backed up to separate files in a "custom" PostgreSQL format. It's like running pg_dump --format=custom database.

Role Variables (Default Values)

postgres_backup_list:
  - name: default
    pg_username: postgres
    pg_hostname: localhost
    pg_port: 5432
    do_backup_globals: true
    cron:
      minute: '0'
      hour: '*'
      day: '*'
      month: '*'
      weekday: '*'

This list shows which PostgreSQL instances to back up (connection details) and how often (optional).

These details are saved in config files, so keep in mind the security risks of storing passwords in plain text (if using pg_password, see examples below). By default, these files can only be read by the owner, usually root.

To remove an entry from the list, use the state parameter to delete the config file and its cron job (see examples below).

postgres_backup_config_dir: /etc/postgres_backup

Location to save the configuration files.

postgres_backup_default_output_dir: /srv/postgres_backup

Where to store the backups. Each entry in the list gets its own folder here. This can be changed for each entry.

postgres_backup_default_date_format: "%Y-%m-%d_%H-%M"

How to name the backup folders. This can also be changed for each entry in the list.

Examples

Hourly Backup of a Local PostgreSQL Server

postgres_backup_list:
  - name: default
    pg_username: postgres
    pg_hostname: localhost
    pg_port: 5432
    do_backup_globals: true
    cron:
      minute: '0'
      hour: '*'
      day: '*'
      month: '*'
      weekday: '*'

This configuration will create a structure like this:

/srv/
  postgres_backup/
    default/
      2019-01-01_00-00/
        globals.sql.gz
        database-a.custom
        database-b.custom
      2019-01-01_01-00/
        globals.sql.gz
        database-a.custom
        database-b.custom

No Automatic Backups, Just Install the Script and Configuration

postgres_backup_list:
  - name: default
    pg_username: postgres
    pg_hostname: localhost
    pg_port: 5432

Daily Backup of a Remote Server with a Password, to a Different Directory

postgres_backup_list:
  - name: production
    pg_username: backup
    pg_password: "hunter2"
    pg_hostname: db.example.com
    pg_port: 5432
    do_backup_globals: false
    output_dir: /opt/prod_db_bak
    cron:
      minute: '0'
      hour: '0'
      day: '*'
      month: '*'
      weekday: '*'

This configuration will create a structure like this:

/opt/
  prod_db_bak/
    2019-01-01_00-00/
      users.custom
      posts.custom
    2019-01-02_00-00/
      users.custom
      posts.custom

Remove a Previously Defined Configuration

postgres_backup_list:
  - name: production
    state: absent