Running pgBackRest Locally on the Database Host’

Description

  • pgBackRest runs locally on database servers and stores repository on remote storage (NFS, S3, Azure or GCS compatible object stores).

  • Cron task active on the node where to take backups.

  • Manually reconfigure cron task to take backup from another server.

! Use case diagram

Configuration

Global Section

The [global] section will contain the:

  • repository location;

  • recommended settings;

  • retention policy.

This section’s settings should be the same for all the nodes (primary and standbys).

Mount Points

Set repo1-type to posix or cifs and specify the repo1-path location to store backups and archives on a shared space:

repo1-type=posix
repo1-path=/backup_space

S3-compatible Object Stores

repo1-type=s3
repo1-path=/repo1
repo1-s3-bucket=bucket-name
repo1-s3-endpoint=...
repo1-s3-key=accessKey1
repo1-s3-key-secret=***
repo1-s3-region=...

The backups and archives will be stored in bucket-name/repo1 .

It is possible to automatically retrieve temporary credentials when the AWS instance is associated with an IAM role. Credentials are automatically updated when they are <= 5 minutes from expiring.

To do this, instead of setting repo1-s3-key and repo1-s3-key-secret , use:

repo1-s3-key-type=auto
repo1-s3-role=authrole

To automatically retrieve web identity credentials, set the repo1-s3-key-type=web-id option and define the AWS_ROLE_ARN and AWS_WEB_IDENTITY_TOKEN_FILE environment variables.

Set the repo1-s3-kms-key-id option to enable server-side encryption using the specified AWS key management service key.

Azure-compatible Object Stores

repo1-type=azure
repo1-azure-key-type=shared
repo1-path=/repo1
repo1-azure-container=container-name
repo1-azure-account=pgbackrest
repo1-azure-key=***

To use the shared access signatures, set the repo1-azure-key-type option to sas and the repo1-azure-key option to the shared access signature token.

GCS-compatible Object Stores

repo1-type=gcs
repo1-gcs-key-type=service
repo1-path=/repo1
repo1-gcs-bucket=bucket-name
repo1-gcs-key=/etc/pgbackrest/gcs-key.json

repo1-gcs-key is a token or service key file depending on the repo1-gcs-key-type option.

Retention Policy

To be able to delete old backups and archives, you have to define a retention policy. Use the following setting to only keep the latest full backup:

repo1-retention-full=1

See the Retention Policy page for more options and details.

Stanza Section

The [demo] stanza section will be different on each database server and should contain:

  • the location of the PostgreSQL configuration files

pg1-path=/var/lib/pgsql/13/data

or for EDBPostgresAdvancedServer

pg1-path=/var/lib/edb/as13/data
pg1-user=enterprisedb
pg1-port=5444

  • for every other PostgreSQL node

pg2-host=other_node_hostname_or_ip
pg2-path=/var/lib/pgsql/13/data

or for EDBPostgresAdvancedServer

pg2-host=other_node_hostname_or_ip
pg2-host-user=enterprisedb
pg2-path=/var/lib/edb/as13/data

  • specific recovery option to set the replication primary_conninfo after the restore command is executed:

recovery-option=primary_conninfo=host=primary_vip user=replication_user ...

Setup Archiving

Once pgBackRest is configured, set up the database archiver process on each node:

#  postgresql.conf

archive_mode = on
archive_command = pgbackrest --stanza=demo archive-push %p

As changing the archive_mode parameter requires a service restart, and changing the archive_command only requires a configuration reload, we recommend enabling archive_mode with an empty archive_command (or pointing to /bin/true) when initiating a new database cluster.

See the Quick Start page to set up a simple demo stanza configuration.

Assume the identity of the system user (postgres or enterprisedb ) and initiate the pgBackRest repository from the primary server:

$ pgbackrest --stanza=demo stanza-create

Check the configuration and the archiving process:

$ pgbackrest --stanza=demo check

Backup and Restore

See the Quickstart Backups and Restore sections for more details about those two commands.

Glossary

pgBackRest

PostgreSQL