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'

Reference

Table of Contents

Classes

Public Classes

• pgprobackup: Manages PostgreSQL backups using pg_probackup
• pgprobackup::catalog: Manages host where backups are being stored
• pgprobackup::instance: Configure a DB instance

Private Classes

• pgprobackup::grants::psql10
• pgprobackup::install
• pgprobackup::repo
• pgprobackup::repo::apt
• pgprobackup::repo::yum

Defined types

Private Defined types

• pgprobackup::cron_backup: A cron job is exported from a database instance, but could be executed elsewhere. Typically on a catalog (backup) server.

Data types

• Pgprobackup::Backup_type: Supported backup types
• Pgprobackup::Config: Backup schedule
• Pgprobackup::Hour
• Pgprobackup::LogLevel: Supported log levels
• Pgprobackup::Minute
• Pgprobackup::Month
• Pgprobackup::Monthday
• Pgprobackup::Weekday

Classes

pgprobackup

Manages PostgreSQL backups using pg_probackup

Examples

include pgprobackup

Parameters

The following parameters are available in the pgprobackup class:

• package_ensure
• package_name
• host_group
• db_name
• db_user
• debug_symbols
• debug_suffix
• backup_dir
• manage_ssh_keys
• manage_host_keys
• manage_pgpass
• manage_hba
• backup_user
• manage_cron
• log_dir
• host_key_type

package_ensure

Data type: String

Ensure package installation

package_name

Data type: String

Base package name, e.g. pg_probackup, pg_probackup-std, pg_probackup-ent

host_group

Data type: String

Allows grouping DB servers, each host_group should have just one backup catalog.

db_name

Data type: String

Database created on DB instance

db_user

Data type: String

PostgreSQL role used for connecting to DB instance/replication.

debug_symbols

Data type: Boolean

Whether to install package with debugging symbols, default: true

debug_suffix

Data type: Optional[String]

Suffix for debug package

Default value: undef

backup_dir

Data type: Stdlib::AbsolutePath

Path to backup catalog (physical backups storage)

manage_ssh_keys

Data type: Boolean

When enabled public SSH key from backup catalog user will be added as authorized key on DB instance

manage_host_keys

Data type: Boolean

Adds host's ssh fingerprint to known hosts (required to negotiate ssh connection)

manage_pgpass

Data type: Boolean

When true, configures password for database authentication (for backup role).

manage_hba

Data type: Boolean

When enabled, create rule for connection from backup catalog server to DB instance.

backup_user

Data type: String

manage_cron

Data type: Boolean

log_dir

Data type: Optional[Stdlib::AbsolutePath]

Default value: undef

host_key_type

Data type: String

pgprobackup::catalog

Configures server for storing backups.

Examples

include pgprobackup::catalog

Parameters

The following parameters are available in the pgprobackup::catalog class:

• backup_dir
• user
• group
• dir_mode
• manage_ssh_keys
• host_group
• purge_cron
• log_dir
• logrotate_template
• exported_ipaddress
• user_ensure
• user_shell
• manage_host_keys
• manage_pgpass
• manage_hba
• manage_cron
• uid
• hba_entry_order
• ssh_key_fact
• package_name
• package_ensure
• versions

backup_dir

Data type: Stdlib::AbsolutePath

Directory for storing backups, also home directory for backup user

Default value: $pgprobackup::backup_dir

user

Data type: String

Local user account used for running and storing backups in its home dir.

Default value: $pgprobackup::backup_user

group

Data type: String

Primary group of backup user

Default value: $pgprobackup::backup_user

dir_mode

Data type: String

Permission mode for backup storage

Default value: '0750'

manage_ssh_keys

Data type: Boolean

Whether ssh directory should be managed

Default value: $pgprobackup::manage_ssh_keys

host_group

Data type: String

Allows to import only certain servers

Default value: $pgprobackup::host_group

purge_cron

Data type: Boolean

Whether remove unmanaged entries from crontab

Default value: true

log_dir

Data type: Optional[Stdlib::AbsolutePath]

Default value: $pgprobackup::log_dir

logrotate_template

Data type: String

Default value: 'pgprobackup/logrotate.conf.erb'

exported_ipaddress

Data type: String

Default value: "${facts['networking']['ip']}/32"

user_ensure

Data type: Enum['present', 'absent']

Default value: 'present'

user_shell

Data type: String

Default value: '/bin/bash'

manage_host_keys

Data type: Boolean

Default value: $pgprobackup::manage_host_keys

manage_pgpass

Data type: Boolean

Default value: $pgprobackup::manage_pgpass

manage_hba

Data type: Boolean

Default value: $pgprobackup::manage_hba

manage_cron

Data type: Boolean

Default value: $pgprobackup::manage_cron

uid

Data type: Optional[Integer]

Default value: undef

hba_entry_order

Data type: Integer

Default value: 50

ssh_key_fact

Data type: String

Default value: $facts['pgprobackup_catalog_key']

package_name

Data type: String

Default value: $pgprobackup::package_name

package_ensure

Data type: String

Default value: $pgprobackup::package_ensure

versions

Data type: Array[String]

Default value: ['12']

pgprobackup::instance

Prepares PostgreSQL host for running backups.

Examples

pgprobackup::instance::backups:
  common:
    FULL:
      hour: 3
      minute: 15
      weekday: 0
    DELTA:
      hour: 0
      minute: 45

include pgprobackup::instance

Parameters

The following parameters are available in the pgprobackup::instance class:

• id
• cluster
• server_address
• server_port
• db_name
• db_user
• db_cluster
• db_dir
• manage_dbuser
• version
• backups
• retention_redundancy
• retention_window
• delete_expired
• merge_expired
• threads
• temp_slot
• slot
• validate
• compress_algorithm
• compress_level
• archive_timeout
• remote_user
• remote_port
• binary
• redirect_console
• log_console
• log_rotation_size
• log_rotation_age
• db_password
• seed
• manage_ssh_keys
• manage_host_keys
• manage_pgpass
• manage_hba
• manage_cron
• manage_grants
• archive_wal
• backup_dir
• backup_user
• ssh_key_fact
• log_dir
• log_file
• log_level_file
• log_level_console
• package_name
• package_ensure

id

Data type: String

Unique identifier within host_group

Default value: $facts['networking']['hostname']

cluster

Data type: Optional[String]

Could be used to group primary with standby servers

Default value: undef

server_address

Data type: String

Address used for connecting to the DB server

Default value: $facts['networking']['fqdn']

server_port

Data type: Integer

DB port

Default value: 5432

db_name

Data type: String

Database used for backups

Default value: $pgprobackup::db_name

db_user

Data type: String

User connecting to database

Default value: $pgprobackup::db_user

db_cluster

Data type: String

Postgresql cluster e.g. main

Default value: 'main'

db_dir

Data type: String

PostgreSQL home directory

Default value: '/var/lib/postgresql'

manage_dbuser

Data type: Boolean

Whether role for running backups should be managed.

Default value: true

version

Data type: String

Major PostgreSQL release version for installing pg_probackup package

Default value: lookup('postgresql::globals::version')

backups

Data type: Optional[Pgprobackup::Config]

Hash with backups schedule

Default value: undef

retention_redundancy

Data type: Optional[Integer]

The number of full backup copies to keep in the backup catalog.

Default value: undef

retention_window

Data type: Optional[Integer]

Defines the earliest point in time for which pg_probackup can complete the recovery.

Default value: undef

delete_expired

Data type: Boolean

Delete expired backups when retention_redundancy or retention_window is set.

Default value: true

merge_expired

Data type: Boolean

Merge expired backups when retention_redundancy or retention_window is set.

Default value: false

threads

Data type: Optional[Integer]

Number of parallel threads

Default value: undef

temp_slot

Data type: Boolean

Use temporary replication slot

Default value: false

slot

Data type: Optional[String]

Replication slot name

Default value: undef

validate

Data type: Boolean

Whether backups should be validated after taking backup

Default value: true

compress_algorithm

Data type: Optional[String]

Either zlib, pglz or none (default: none)

Default value: undef

compress_level

Data type: Integer

Integer between 0 and 9 (default: 1)

Default value: 1

archive_timeout

Data type: Optional[Integer]

Timeout in seconds for copying all remaining WAL files.

Default value: undef

remote_user

Data type: String

user used for ssh connection to the DB instance

Default value: 'postgres'

remote_port

Data type: Integer

ssh port used for connection to the DB instance from catalog server

Default value: 22

binary

Data type: Optional[String]

custom script to be executed as backup command

Default value: undef

redirect_console

Data type: Boolean

Redirect console output to a log file (make sense especially with custom backup command)

Default value: false

log_console

Data type: Optional[String]

File for storing console logs

Default value: undef

log_rotation_size

Data type: Optional[String]

rotate logfile if its size exceeds this value; 0 disables; (default: 0) available units: 'kB', 'MB', 'GB', 'TB' (default: kB)

Default value: undef

log_rotation_age

Data type: Optional[String]

rotate logfile if its age exceeds this value; 0 disables; (default: 0) available units: 'ms', 's', 'min', 'h', 'd' (default: min)

Default value: undef

db_password

Data type: Optional[Variant[String,Sensitive[String]]]

Default value: undef

seed

Data type: Optional[String]

Default value: undef

manage_ssh_keys

Data type: Boolean

Default value: $pgprobackup::manage_ssh_keys

manage_host_keys

Data type: Boolean

Default value: $pgprobackup::manage_host_keys

manage_pgpass

Data type: Boolean

Default value: $pgprobackup::manage_pgpass

manage_hba

Data type: Boolean

Default value: $pgprobackup::manage_hba

manage_cron

Data type: Boolean

Default value: $pgprobackup::manage_cron

manage_grants

Data type: Boolean

Whether grants needed for backups are managed, default true

Default value: true

archive_wal

Data type: Boolean

Default value: false

backup_dir

Data type: Stdlib::AbsolutePath

Default value: $pgprobackup::backup_dir

backup_user

Data type: String

Default value: $pgprobackup::backup_user

ssh_key_fact

Data type: String

Default value: $facts['pgprobackup_instance_key']

log_dir

Data type: Optional[Stdlib::AbsolutePath]

Default value: $pgprobackup::log_dir

log_file

Data type: Optional[String]

Default value: undef

log_level_file

Data type: Optional[Pgprobackup::LogLevel]

Default value: undef

log_level_console

Data type: Optional[Pgprobackup::LogLevel]

Default value: undef

package_name

Data type: String

Default value: $pgprobackup::package_name

package_ensure

Data type: String

Default value: $pgprobackup::package_ensure

Data types

Pgprobackup::Backup_type

Supported backup types

Alias of Enum['FULL', 'DELTA']

Pgprobackup::Config

Backup schedule

Alias of

Hash[String, Hash[
                                              Pgprobackup::Backup_type,Struct[{
                                                  Optional[hour]   => Pgprobackup::Hour,
                                                  Optional[minute] => Pgprobackup::Minute,
                                                  Optional[month]  => Pgprobackup::Month,
                                                  Optional[weekday] => Pgprobackup::Weekday,
                                                  Optional[monthday] => Pgprobackup::Monthday,
                                                  Optional[threads] => Integer,
                                                  Optional[retention_redundancy] => Integer,
                                                  Optional[retention_window] => Integer,
                                                  Optional[delete_expired] => Boolean,
                                                  Optional[merge_expired] => Boolean,
                                                  Optional[temp_slot] => Boolean,
                                                  Optional[slot] => String,
                                                  Optional[validate] => Boolean,
                                                  Optional[compress_algorithm] => String,
                                                  Optional[compress_level] => Integer,
                                                  Optional[archive_wal] => Boolean,
                                                  Optional[log_dir] => Stdlib::AbsolutePath,
                                                  Optional[log_file] => String,
                                                  Optional[log_console] => String,
                                                  Optional[log_rotation_size] => String,
                                                  Optional[log_rotation_age] => String,
                                                  Optional[redirect_console] => Boolean,
                                                  Optional[log_level_file] => Pgprobackup::LogLevel,
                                                  Optional[log_level_console] => Pgprobackup::LogLevel,
                                                }]
                                              ]]

Pgprobackup::Hour

The Pgprobackup::Hour data type.

Alias of Variant[Integer[0,23], String, Tuple[Variant[String, Integer[0,23]], 1, default]]

Pgprobackup::LogLevel

Supported log levels

Alias of Enum['off', 'error', 'warning', 'info', 'log', 'verbose']

Pgprobackup::Minute

The Pgprobackup::Minute data type.

Alias of Variant[Integer[0,59], String, Tuple[Variant[String, Integer[0,59]], 1, default]]

Pgprobackup::Month

The Pgprobackup::Month data type.

Alias of Variant[Integer[1,12], String, Tuple[Variant[String, Integer[1,12]], 1, default]]

Pgprobackup::Monthday

The Pgprobackup::Monthday data type.

Alias of Variant[Integer[1,31], String, Tuple[Variant[String, Integer[1,31]], 1, default]]

Pgprobackup::Weekday

The Pgprobackup::Weekday data type.

Alias of Variant[Integer[0,7], String, Tuple[Variant[String, Integer[0,7]], 1, default]]

Change log

All notable changes to this project will be documented in this file. The format is based on Keep a Changelog and this project adheres to Semantic Versioning.

v2.0.2 (2024-06-22)

• require stdlib > 9.1.0 in order to support passing undef to fqdn_rand_string as 2nd arg

Full Changelog

v2.0.1 (2024-06-21)

• Use stdlib::fqdn_rand_string

Full Changelog

v2.0.0 (2024-06-21)

• Use prefixed Puppet 4.x functions
• Puppet 8 compatible
• stdlib >= 9 required

Full Changelog

v1.3.1 (2024-04-04)

• Fix postgresql 15 and 16 support

Full Changelog

v1.3.0 (2024-04-04)

• Optionally mangage grants
• Support all Postgresql version >= 10

Full Changelog

v1.2.0 (2024-02-01)

• Refactor GPG key usage for apt
• Use namespaced function postgresql::postgresql_password

v1.1.0 (2023-12-20)

• Fixed invalid common.yaml
• Support puppetlabs-postgresql 10.x
• Fix upper bounds for dependencies

Full Changelog

v1.0.0 (2023-09-15)

• Puppet 8 compatibility
• Removed legacy facts

Full Changelog

v0.5.0 (2022-05-05)

Full Changelog

Added

• Better logging #8 (deric)

v0.4.1 (2022-05-04)

Full Changelog

Added

• Support custom backup binary #7 (deric)

v0.4.0 (2022-05-04)

Full Changelog

Added

• [BC] Support grouping backups by cluster name #5 (deric)

Fixed

• Fix importing ssh host keys #6 (deric)

v0.3.1 (2022-04-29)

Full Changelog

Added

• Support sensitive type #4 (deric)

v0.3.0 (2022-04-28)

Full Changelog

Added

• Support backups to multiple targets #3 (deric)

v0.2.2 (2022-03-25)

Full Changelog

v0.2.1 (2022-03-25)

Full Changelog

v0.2.0 (2022-03-25)

Full Changelog

v0.1.0 (2021-08-30)

Full Changelog

* This Changelog was automatically generated by github_changelog_generator