puppet-pgprobackup

Automates PostgreSQL backups using pgprobackup.

Description

Module allows configuration of a PostgreSQL instance (role for backup, SSH keys, hba rules) and a (remote) backup catalog (user account, backup directory, host keys, SSH keys).

Setup

What pgprobackup affects

Module touches many resources, including PostgreSQL configuration that might require server restart (e.g. when archive_mode is modified). Make sure to understand the implications before using it. Each feature could be turned off in case you're using some other mechanism.

database configuration
database roles
pgprobackup::manage_pgpass role password (default: true)
pgprobackup::manage_host_keys SSH host keys (default: true)
pgprobackup::manage_ssh_keys SSH authorized keys (public SSH keys)
pgprobackup::manage_cron CRON jobs (default: true)
user accounts
pgprobackup catalog

Setup Requirements

Puppet >= 5
PostgreSQL instance >= 9.5
puppetlabs/postgresql is expected to manage the PostgreSQL instance

Usage

Backup server(s) (where backup data will be stored) requires packages for all different PostgreSQL version that are running the same host_group, e.g. pg_probackup-11, pg_probackup-12.

include pgprobackup::catalog

each backup server should define:

pgprobackup::catalog::host_group: common
# pg_probackup has dedicated binaries for each major PostgreSQL versions
pgprobackup::catalog::versions:
  - '13'
  - '14'

NOTE: Package version catalog and instance needs to be exactly the same! (e.g. 2.3.3-1.6a736c2db6402d77).

pgprobackup::package_ensure allows pinpointing to a specific version:

pgprobackup::package_ensure: "2.4.2-1.8db55b42aeece064.%{facts.os.distro.codename}"

All Puppet variables are documented in REFERENCE.md.

Instance

DB server:

include pgprobackup::instance

Configure pgprobackup to run full backup every Sunday (via CRON job):

pgprobackup::manage_cron: true
pgprobackup::instance::backups:
  common:
    FULL:
      hour: 3
      minute: 15
      weekday: [0] # same as `7` or `Sunday`

Incremental (DELTA) backups every day except Sunday:

pgprobackup::instance::backups:
  common:
    FULL:
      weekday: 0
    DELTA:
      weekday: [1-6]

Incremental (DELTA) backups every day except Friday, full backup on Friday:

pgprobackup::instance::backups:
  common:
    FULL:
      weekday: 5
    DELTA:
      weekday: [0-4,6]

Target backup catalog servers (one database instance can be backed to multiple locations - first catalog is has host_group configured as common the other off-site):

pgprobackup::instance::backups:
  common:
    FULL:
      weekday: 0
    DELTA:
      weekday: [1-6]
  # run full backup to `off-site` location on first of each month
  off-site:
    FULL:
      hour: 5
      monthday: 1
      retention_redundancy: 2
      retention_window: 7
      delete_expired: true
      merge_expired: true
      threads: 3

There are many shared parameters between instance and catalog. Such parameters are defined in pgprobackup:: namespace, such as pgprobackup::package_name (base package name to be installed on both instance and catalog).

retention_window Defines the earliest point in time for which pg_probackup can complete the recovery.
retention_redundancy The number of full backup copies to keep in the backup catalog.
delete_expired Delete expired backups when retention_redundancy or retention_window is set.
merge_expired Merge expired backups when retention_redundancy or retention_window is set.

Instance parameters

threads Number of parallel threads
temp_slot Whether to use temporary replication slot, which should guarantee that WAL won't be removed from primary server. In case of backup failure the slot will be removed (default false).
slot Specifies the replication slot for WAL streaming. Can't be used together with archive_wal=true.
validate Validate backup consistency after backup completition (default: true).
compress_algorithm Currently supported algorithms zlib or pglz (default: 'none')
compress_level 0-9 (defalt: 1)
archive_timeout Timeout in seconds for copying all remaining WAL files (default 300).

Logging

Parameters can be specified on instance level:

pgprobackup::instance::log_dir: /var/log/pg_probackup
pgprobackup::instance::log_level_file: info

or for specific backup:

pgprobackup::instance::backups:
  common:
    FULL:
      log_level_file: verbose

log_dir - by default stored to backup dir/log
log_file - custom filename
redirect_console redirect stdout & stderr to file (default: false)
log_console - custom filename for console log (requires redirect_console: true)
log_level_file - allowed values off, error, warning, info, log, verbose
log_level_console - allowed values off, error, warning, info, log, verbose
log_rotation_size - rotate logfile if its size exceeds this value; 0 disables; (default: 0) available units: 'kB', 'MB', 'GB', 'TB' (default: kB)
log_rotation_age - rotate logfile if its size exceeds this value; 0 disables; (default: 0) available units: 'ms', 's', 'min', 'h', 'd' (default: min)

Limitations

Error message on catalog server:

Could not find resource 'Package[pg-probackup-11]' in parameter 'require'

means, that the server requires packages for all different Postgresql versions that are being backed up.

pgprobackup::catalog::versions:
  - '11'
  - '12'

deric/puppet-pgprobackup