Set up pgBackRest for AlloyDB Omni

This page shows you how to protect your data by configuring AlloyDB Omni to work with pgBackRest, an open-source database backup server. For an overview of available backup solutions, see Back up and restore AlloyDB Omni.

pgBackRest is a flexible backup and restore utility for PostgreSQL. Because AlloyDB Omni is PostgreSQL compatible, you can use pgBackRest to protect your AlloyDB Omni data using manual, scheduled, and continuous backups.

pgBackRest writes its backup data to local, remote, or cloud-based repositories. After you have established at least one repository, you can use pgBackRest to restore your AlloyDB Omni data through various methods, including point-in-time recovery (PITR).

AlloyDB Omni includes pgBackRest in its Docker container. This means that you can use pgBackRest to back up and restore your AlloyDB Omni data without the need to install any additional software.

For more information about pgBackRest, see its user guide.

Before you begin

Before configuring AlloyDB Omni to work with pgBackrest, you need to have AlloyDB Omni installed and running on a server that you control.

A note about file system paths

The pgBackRest software included with AlloyDB Omni runs in the same Docker container as AlloyDB Omni. Because of this, all of the file system paths that you provide pgBackRest through its configuration file or as command-line arguments are locations on the container's file system, and not your host machine's file system.

Many of the commands and examples on this page refer to your data directory as /mnt/disks/pgsql, regardless of the location of your data directory on your host system. This is because AlloyDB Omni mounts your data directory to /mnt/disks/pgsql on its containerized file system. As a result, you can use the data directory as a location to store pgBackRest configuration and repositories without further setup.

If you want to configure the containerized pgBackRest to read from or write to directories on your host machine's file system outside of your AlloyDB Omni your data directory, then you need to make these directories available to the container.

Basic configuration with local backups

The steps in this section guide you through a basic setup of pgBackRest, including a short configuration file that directs pgBackRest to write continuous backup data into a subdirectory of your AlloyDB Omni data directory.

Because pgBackRest is a flexible third-party product compatible with AlloyDB Omni, you can modify any of these steps as appropriate for your own needs and preferences. If you do change any file system paths, remember that they must be visible to the container; see A note about file system paths.

Set up trusted Unix-socket authentication

Allow your AlloyDB Omni host machine to authenticate the database server's postgres user without requiring a password. This simplifies subsequent steps.

  1. Add the following line to your /var/alloydb/config/pg_hba.conf file:

    local       all      postgres       trust

  2. Restart AlloyDB Omni:

    sudo alloydb database-server stop
sudo alloydb database-server start

Create and configure a backup repository

The configuration file created in this section is an example, enabling a minimal setup for locally stored, continuous backup. You can modify this file in any way that suits your needs. For more information, see Configure cluster stanza.

  1. Create a backups subdirectory in your AlloyDB Omni data directory:

    mkdir DATA_DIR/backups

    Replace DATA_DIR with the file system path to your data directory—for example, /home/$USER/alloydb-data.

  2. Create a file named pgbackrest.conf inside your data directory, and copy the following content into it:

    # Paths (all mandatory):
repo1-path=/mnt/disks/pgsql/backups
spool-path=/mnt/disks/pgsql
lock-path=/mnt/disks/pgsql

# Retention details:
repo1-retention-full=3
repo1-retention-full-type=count
repo1-retention-diff=16

# Force a checkpoint to start backup immediately:
start-fast=y

# Logging parameters:
log-path=/mnt/disks/pgsql/backups
log-level-console=info
log-level-file=info

# Recommended ZSTD compression:
compress-type=zst

# Other performance parameters:
archive-async=y
archive-push-queue-max=1024MB
archive-get-queue-max=256MB
archive-missing-retry=y

[global:archive-push]
process-max=2

[global:archive-get]
process-max=2

[omni]
pg1-user=postgres
pg1-socket-path=/mnt/disks/pgsql
pg1-path=/mnt/disks/pgsql/data

  3. Initialize the backup location using the pgbackrest stanza-create command:

    docker exec pg-service pgbackrest --config-path=/mnt/disks/pgsql --stanza=omni stanza-create

Configure the database for continuous backups

  1. To enable continuous backups, run the following command to set several PostgreSQL parameters:

    docker exec pg-service psql -h localhost -U postgres \
-c "ALTER SYSTEM SET archive_command='pgbackrest --config-path=/mnt/disks/pgsql --stanza=omni archive-push %p';" \
-c "ALTER SYSTEM SET archive_mode=on;" \
-c "ALTER SYSTEM SET max_wal_senders=on;" \
-c "ALTER SYSTEM SET wal_level=replica;"

  2. Restart AlloyDB Omni:

    sudo alloydb database-server stop
sudo alloydb database-server start

Run pgBackRest commands

Completing the steps in the previous section configures pgBackRest to work with your AlloyDB Omni server. To check the backup status, create manual backups, and perform other tasks, see Command reference.

As a best practice, run pgBackRest commands directly from the host machine that the AlloyDB Omni container is installed on, using the docker exec command. For example, to create a manual backup, use the pgbackrest backup command. Modify it so that it runs within a docker exec command, and refers to the configuration file that you created earlier:

docker exec pg-service pgbackrest --config-path=/mnt/disks/pgsql --stanza=omni --type=full backup

The value of the --config-path flag is /mnt/disks/pgsql because that is always the location of your data directory on the AlloyDB Omni container's file system. If you followed the steps in the previous section, then your data directory contains the pgbackrest.conf file that pgBackRest commands need to run properly. For more information, see A note about file system paths.

You can also use environment variables to set the location of your pgBackRest configuration file. For more information, see Config Path Option.

Set up scheduled backups

To set up scheduled backups, create a cron task that runs the pgbackrest backup command as often as needed. For more information, see Schedule a backup.

Custom configuration and remote backups

After you have a basic configuration working, you can tune your configuration file to suit your needs and preferences using the options documented in the pgBackRest configuration reference.

This includes specifying additional backup repositories located on remote machines, or in the cloud. If you define multiple repositories, then pgBackRest simultaneously writes to them all as its default backup action.

For example, pgBackRest supports using a Cloud Storage bucket as a backup repository, with a number of related configuration options. The following section demonstrates one way to use these options.

An example configuration using Cloud Storage

The steps in this section build on the configuration file introduced in Basic configuration with local backups. These modifications to that file define a second backup repository in a Cloud Storage bucket, accessed through Identity and Access Management (IAM).

The automatic authentication style in this example requires an AlloyDB Omni cluster to run on a Compute Engine VM instance. If you don't run AlloyDB Omni on a Compute Engine VM instance, then you can still back up to a Cloud Storage bucket by using another authentication method, such a Google Cloud service account key saved to the local file system.

To extend the earlier configuration file to define a Cloud Storage-based pgBackRest repository, follow these steps:

  1. Configure the bucket permissions to allow the service account attached to your VM instance to write to the bucket. This requires the Storage Object User IAM role set on that service account.

  2. Add these lines to your pgbackrest.conf file:

    # Cloud Storage access details:
repo2-type=gcs
repo2-gcs-key-type=auto
repo2-storage-verify-tls=n

# Cloud Storage bucket and path details:
repo2-gcs-bucket=BUCKET_NAME
repo2-path=/pgbackrest

# Cloud Storage backup retention parameters:
repo2-retention-full=8
repo2-retention-full-type=count

    Replace BUCKET_NAME with the name of the Cloud Storage bucket that you want pgBackRest to store backups to.

  3. Initialize the cloud-based backup location using the pgbackrest stanza-create command:

    docker exec pg-service pgbackrest --config-path=/mnt/disks/pgsql --stanza=omni stanza-create

After you initialize the backup repository in your Cloud Storage bucket using the pgbackrest stanza-create command, pgBackRest backs up to two locations:

  • The location in the local file system, defined elsewhere in the configuration file as repo1-path.

  • The Cloud Storage bucket, defined using the repo2- configuration directives set up by this example.

What's next