Version 3.12.1

3.3.3. PostgreSQL logical backups

A PostgreSQL logical backup is a database dump. Backups are created in SQL format.

Requirements:

To make backups of your databases, you have to ensure that you have installed pg_dump utility to an equal or newer version than the target server.
There should be enough space in the directory where the temporary backup is created.
The database user, who connects to the database requires at least the following privilege:
- SELECT privilege for dumped tables and sequences
- CONNECT privilege for dumped database

Here is an example of configs for creating a backup of all databases on the PostgreSQL server with the exclusion of “postgres” and “demo” databases, “tmp” scheme in the ”staging” database and ”metrics” table of “sys” scheme in ”staging” database. This job config defines rules to make database backups on the PostgreSQL server running on localhost:

job_name: postgresql
type: postgresql
tmp_dir: /var/backup/dump_tmp
safety_backup: false
deferred_copying: false
sources:
  - name: psql
  	connect:
    	db_host: localhost
    	db_port: "5432"
    	psql_ssl_mode: require
    	db_user: backup@postgres
    	db_password: secureP@5s
  	target_dbs:
    	- all
  	excludes:
    	- postgres
    	- demo
    	- staging.service.metrics
    	- staging.common.tmp*
  	gzip: true
storages_options:
  - storage_name: local
  	backup_path: /var/backup/dump
  	retention:
    	days: 7
    	weeks: 5
    	months: 5

If you need to configure additional storage or have questions about options, please visit the Job storage options page for more details.

Tips

Users without a database

If used user that does not have a corresponding database, e.g. "backup" or "repmgr", you can specify the database to which the user has access by specifying the name of the database after the username with the @ symbol, e.g. "backup@postgres".

Excludes

When configuring a backup job, you can exclude some databases and tables.

To exclude databases, list them in the excludes field:

sources:
  - name: psql15
  	excludes:
    	- postgres
      - demo

To exclude tables, add a list in the excludes field in the format <db_name>.<schema>.<table_name> (supports psql patterns):

sources:
  - name: psql15
  	excludes:
    	- prod.service.metrics
    	- staging.service.metrics
    	- staging.common.tmp*

Reference of `postgresql` type

# Job unique name
job_name: string # Required

# Type of the backup job. `postgresql`
type: string # Required 

# Path to the directory used to temporarily store the backup during the creation process
tmp_dir: string # Required

# The option defines the order of rotation (deletion of old) backups, before creating a new copy or afterwards
safety_backup: bool # Default: false

# The option allows you to postpone copying until all backup parts of the job are complete before its delivery
deferred_copying: bool # Default: false

# The option enables compression of the backup archive
gzip: bool # Default: false
 
# Contains resource constraints for a specific job that override all others.
limits: map
  # Limit the write speed to the local disc per second in `bytes`, `kb`, `mb`, etc. Example: "20mb".
  # If "0" is used, there is no limitation.
  disk_rate: string
  # Limit the net speed to remote storages per second in `bytes`, `kb`, `mb`, etc. Example: "12mb".
  # If "0" is used, there is no limitation.
  net_rate: string

# Contains a list of individual subtasks for creating backups
sources: list 
    # Unique subtask name, used to separate storage paths
  - name: string # Required
    # Defines a set of parameters for connecting to the database. You may use either db_host,db_port or socket options. Options priority follows: db_host → socket
    connect: map 
      # DB host address
      db_host: string # Optional
      # DB port
      db_port: string # Optional
      # DB user
      db_user: string # Optional
      # DB password
      db_password: string # Optional
      # Path to DB socket
      socket: string # Optional
      # PostgreSQL SSL mode option
      psql_ssl_mode: string # Default: require
      # Path to file containing SSL certificate authority (CA) certificate(s) for PostgreSQL
      psql_ssl_root_cert: string # Optional
      # Path to file containing SSL server certificate revocation list (CRL) for PostgreSQL
      psql_ssl_crl: string # Optional
    # List of databases to be backed up. Use the keyword all for backup all databases.
    target_dbs: list 
        # Database name to be backed up. All databases will be backed up if used `all` as db name
      - string # Default: all
    # List of databases/schemes/tables to be excluded from backup
    excludes: list 
        # Entry to exclude from the backup.
      - string 
    # The option enables compression of the backup archive, overwrites job level
    gzip: bool # Default: false
    # Special parameters for collecting database backups.
    db_extra_keys: string # Optional

# List of storages to which backups should be delivered and their rotation rules
storages_options: list 
    # The name of the storage added to the main config. The name `local` is used to store a copy on host.
  - storage_name: string # Reuired
    # Path to directory where backups will be stored
    backup_path: string # Reuired
    # Enables backup rotation according to retention parameters.
    enable_rotate: bool # Default: true
    # Set of rules to store and rotate the backups.
    retention: map 
      # Use backup retention count instead of retention period.
      count_instead_of_period: bool # Default: false
      # Count of days/daily copies to store backups. Multiple copies can be created in one day.
      days: numeric # Default: 7
      # Count of weeks/weekly copies to store backups created on sunday.
      weeks: numeric # Default: 5
      # Count of months/montly copies to store backups created on the first day of month.
      months: numeric # Default: 12

3.3.3. PostgreSQL logical backups

Tips

Users without a database

Excludes

Reference of postgresql type

Reference of `postgresql` type