Set up pgBackRest for AlloyDB Omni

Select a documentation version: 15.7.0

• Current (16.8.0)
• 16.8.0
• 16.3.0
• 15.12.0
• 15.7.1
• 15.7.0
• 15.5.5
• 15.5.4
• 15.5.2

This page shows you how to protect your data by configuring AlloyDB Omni to work with pgBackRest, an open-source database backup server.

The information on this page applies only to AlloyDB Omni for containers. It does not apply to AlloyDB Omni for Kubernetes.

For Kubernetes-based information, see Back up and restore in Kubernetes.

For more information about pgBackRest, see its user guide.

Before you begin

Before configuring AlloyDB Omni to work with pgBackRest, complete the following prerequisites:

• Install and run AlloyDB Omni on a server that you control.
• Set up persistent data storage for your AlloyDB Omni instance.

Basic configuration with local backups

The steps in this section guide you through a basic setup of pgBackRest. Basic configurations don't enable backups on a schedule. For more information, see Set up scheduled backups.

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.

Mount a backup volume to your container

For organization purposes, we strongly recommend that you create and mount a separate volume for pgBackRest backups. To set up a new volume, complete the following steps:

1. If your container is running, stop and remove it.

   Docker

   docker stop CONTAINER_NAME
   docker rm CONTAINER_NAME

   Docker

   docker stop CONTAINER_NAME
   docker rm CONTAINER_NAME

   Podman

   podman stop CONTAINER_NAME
   podman rm CONTAINER_NAME

   Podman

   podman stop CONTAINER_NAME
   podman rm CONTAINER_NAME

   Replace the following variable:

   • CONTAINER_NAME: Name you used for your container.

2. Create a backup directory in your container's file system.

   Docker

   mkdir -p BACKUP_DIR
   

   Docker

   mkdir -p BACKUP_DIR
   

   Podman

   mkdir -p BACKUP_DIR
   

   Podman

   mkdir -p BACKUP_DIR
   

   Replace the following variable:

   • BACKUP_DIR: Host directory that your backups will be stored in.

3. Recreate your container with the new backups volume.

   Docker

   docker run -d --name CONTAINER_NAME \
   -e POSTGRES_PASSWORD=NEW_PASSWORD \
   -v DATA_DIR:/var/lib/postgresql/data \
   -v BACKUP_DIR:/var/lib/postgresql/backups \
   -p HOST_PORT:5432 \
   --restart=always \
   google/alloydbomni:latest
   

   Docker

   docker run -d --name CONTAINER_NAME \
   -e POSTGRES_PASSWORD=NEW_PASSWORD \
   -v DATA_DIR:/var/lib/postgresql/data \
   -v BACKUP_DIR:/var/lib/postgresql/backups \
   -p HOST_PORT:5432 \
   --restart=always \
   google/alloydbomni:latest
   

   Podman

   podman run -d --name CONTAINER_NAME \
   -e POSTGRES_PASSWORD=NEW_PASSWORD \
   -v DATA_DIR:/var/lib/postgresql/data \
   -v BACKUP_DIR:/var/lib/postgresql/backups \
   -p HOST_PORT:5432 \
   --restart=always \
   docker.io/google/alloydbomni:latest
   

   Podman

   podman run -d --name CONTAINER_NAME \
   -e POSTGRES_PASSWORD=NEW_PASSWORD \
   -v DATA_DIR:/var/lib/postgresql/data \
   -v BACKUP_DIR:/var/lib/postgresql/backups \
   -p HOST_PORT:5432 \
   --restart=always \
   docker.io/google/alloydbomni:latest
   

   Replace the following variables:

   • CONTAINER_NAME: Name you used for your container.
   • NEW_PASSWORD: Password assigned to the new container's postgres user after its creation. Note that NEW_PASSWORD will only set a new password when DATA_DIR is a new location.
   • DATA_DIR: Host directory path that your data is stored in.
   • DATA_DIR: Host directory path that your data is stored in.
   • BACKUP_DIR: Host directory that your backups will be stored in.
   • HOST_PORT: TCP port on the host machine that the container should publish its own port 5432 to. To use the PostgreSQL default port on the host machine as well, specify 5432.

4. Make the backups directory, in your container, readable by postgres.

   Docker

   docker exec CONTAINER_NAME chown -R postgres:postgres /var/lib/postgresql/backups
   

   Docker

   docker exec CONTAINER_NAME chown -R postgres:postgres /var/lib/postgresql/backups
   

   Podman

   podman exec CONTAINER_NAME chown -R postgres:postgres /var/lib/postgresql/backups
   

   Podman

   podman exec CONTAINER_NAME chown -R postgres:postgres /var/lib/postgresql/backups
   

   Replace the following variable:

   • CONTAINER_NAME: Name you used for your container.

Configure local backups

pgBackRest creates subdirectories for each backup taken and includes a plain-text manifest file.

pgBackRest uses the term stanza to refer to the configuration for a PostgreSQL database cluster. The stanza name is arbitrary and doesn't need to match the host server, PostgreSQL cluster, or database name. The pgBackRest documentation suggests naming the stanza after the cluster's function.

The repository is where backups are written. pgBackRest supports writing to more than one repository in a given stanza. Most configuration parameters related to repositories are indexed with a numeric value. For example, repo1-. Parameters related to PostgreSQL clusters are also, independently, indexed. For example, pg1-.

pgBackRest leverages a configuration file, called pgbackrest.conf, to hold global and stanza-specific parameters.

To build and initialize a configuration file for backing up your AlloyDB Omni cluster, create the pgbackrest.conf file in the host-side backups directory you created in Mount a backup volume to your container.

[global]
# Paths (all mandatory):
repo1-path=/var/lib/postgresql/backups
spool-path=/var/lib/postgresql/backups/spool
lock-path=/var/lib/postgresql/backups

# 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=/var/lib/postgresql/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=/tmp
pg1-path=/var/lib/postgresql/data

Some parameters are mandatory, but others can be adjusted to meet your specific requirements if needed, such as the following:

• repo1-path: Directory location where the backups are written to. We recommend a location visible to both the host server and the container.

• log-path: Directory location where the log files are written to. If you want to write the log files to a separate location, not mixed with the backups themselves, adjust this parameter.

• repo1-retention-full: Number of full backups to retain.

• repo1-retention-full-type: Whether the retention is measured by count or time period (days).

• repo1-retention-diff: Number of differential backups to retain.

Other non-critical, but recommended, parameter settings that are compatible with AlloyDB Omni in the configuration file include the following:

• log-level-console: Level of logging written to the screen (STDOUT) when running pgBackup commands. You can adjust this to meet your needs in the configuration file, or override this value with the --log-level-console command line argument. The default is warn.

• start-fast: Forces a checkpoint to start the backups quickly. The default is n.

• archive-async: Push WAL segment files asynchronously for performance. The default is n.

• process-max: Maximum number of processes to use for compression and transfer. This is typically set to max_cpu/4 on a primary or max_cpu/2 on a standby cluster. The default is 1.

• compress-type: Compression algorithm to use. The default is gz.

Many other pgBackRest configuration parameters exist and can be adjusted. This documentation covers only the parameters mandatory for the default AlloyDB Omni configuration and some recommended parameter settings. For the full list of configuration parameters, refer to the pgBackRest Configuration Reference online documentation.

After configuring pgBackRest, the target repositories where backups are written to must be initialized by creating the stanza, which uses the parameters as set in the configuration file.

To configure your database for backups, complete the following steps:

1. Create the stanza using the stanza-create command.

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni stanza-create
   

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni stanza-create
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni stanza-create
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni stanza-create
   

   Replace the following variables:

   • CONTAINER_NAME: Name you used for your container.

2. Configure your database for backups.

   Docker

   docker exec -u postgres CONTAINER_NAME psql \
   -c "ALTER SYSTEM SET archive_command='pgbackrest --config-path=/var/lib/postgresql/backups --stanza=omni archive-push %p';" \
   -c "ALTER SYSTEM SET archive_mode=on;" \
   -c "ALTER SYSTEM SET max_wal_senders=10;" \
   -c "ALTER SYSTEM SET wal_level=replica;"
   

   Docker

   docker exec -u postgres CONTAINER_NAME psql \
   -c "ALTER SYSTEM SET archive_command='pgbackrest --config-path=/var/lib/postgresql/backups --stanza=omni archive-push %p';" \
   -c "ALTER SYSTEM SET archive_mode=on;" \
   -c "ALTER SYSTEM SET max_wal_senders=10;" \
   -c "ALTER SYSTEM SET wal_level=replica;"
   

   Podman

   podman exec -u postgres CONTAINER_NAME psql \
   -c "ALTER SYSTEM SET archive_command='pgbackrest --config-path=/var/lib/postgresql/backups --stanza=omni archive-push %p';" \
   -c "ALTER SYSTEM SET archive_mode=on;" \
   -c "ALTER SYSTEM SET max_wal_senders=10;" \
   -c "ALTER SYSTEM SET wal_level=replica;"
   

   Podman

   podman exec -u postgres CONTAINER_NAME psql \
   -c "ALTER SYSTEM SET archive_command='pgbackrest --config-path=/var/lib/postgresql/backups --stanza=omni archive-push %p';" \
   -c "ALTER SYSTEM SET archive_mode=on;" \
   -c "ALTER SYSTEM SET max_wal_senders=10;" \
   -c "ALTER SYSTEM SET wal_level=replica;"
   

   Replace the following variables:

   • CONTAINER_NAME: Name you used for your container.

3. Restart your container.

   Docker

   docker restart CONTAINER_NAME
   

   Docker

   docker restart CONTAINER_NAME
   

   Podman

   podman restart CONTAINER_NAME
   

   Podman

   podman restart CONTAINER_NAME
   

   Replace the following variables:

   • CONTAINER_NAME: Name you used for your container.

4. Validate the backup configuration.

   Docker

   docker exec -u postgres CONTAINER_NAME psql \
   -c "SELECT name, setting
       FROM pg_catalog.pg_settings
       WHERE name IN ('archive_command',
                      'archive_mode',
                      'max_wal_senders',
                      'wal_level')
       ORDER BY name"
   

   Docker

   docker exec -u postgres CONTAINER_NAME psql \
   -c "SELECT name, setting
       FROM pg_catalog.pg_settings
       WHERE name IN ('archive_command',
                      'archive_mode',
                      'max_wal_senders',
                      'wal_level')
       ORDER BY name"
   

   Podman

   podman exec -u postgres CONTAINER_NAME psql \
   -c "SELECT name, setting
       FROM pg_catalog.pg_settings
       WHERE name IN ('archive_command',
                      'archive_mode',
                      'max_wal_senders',
                      'wal_level')
       ORDER BY name"
   

   Podman

   podman exec -u postgres CONTAINER_NAME psql \
   -c "SELECT name, setting
       FROM pg_catalog.pg_settings
       WHERE name IN ('archive_command',
                      'archive_mode',
                      'max_wal_senders',
                      'wal_level')
       ORDER BY name"
   

   Replace the following variables:

   • CONTAINER_NAME: Name you used for your container.

5. Perform a pgBackRest check.

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   check
   

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   check
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   check
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   check
   

   Replace the following variables:

   • CONTAINER_NAME: Name you used for your container.

Perform a backup

1. Perform a full backup.

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   --type=full \
   backup
   

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   --type=full \
   backup
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   --type=full \
   backup
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   --type=full \
   backup
   

   Replace the following variables:

   • CONTAINER_NAME: Name you used for your container.

2. Perform a differential backup.

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   --type=diff \
   backup
   

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   --type=diff \
   backup
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   --type=diff \
   backup
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   --type=diff \
   backup
   

   Replace the following variables:

   • CONTAINER_NAME: Name you used for your container.

3. Report backups.

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   info
   

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   info
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   info
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   info
   

   Replace the following variables:

   • CONTAINER_NAME: Name you used for your container.

Set up scheduled backups

To set up scheduled backups, create a cron job 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 the [global] section of 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=PATH_IN_BUCKET
   
   # Cloud Storage backup retention parameters:
   repo2-retention-full=8
   repo2-retention-full-type=count
   

   Replace the following variable:

   • BUCKET_NAME: Name of the Cloud Storage bucket that you want pgBackRest to store backups to.
   • PATH_IN_BUCKET: Directory path in the Cloud Storage bucket that you want pgBackRest to store backups in.

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

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   stanza-create
   

   Docker

   docker exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   stanza-create
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   stanza-create
   

   Podman

   podman exec -u postgres CONTAINER_NAME pgbackrest \
   --config-path=/var/lib/postgresql/backups \
   --stanza=omni \
   stanza-create
   

   Replace the following variable:

   • CONTAINER_NAME: Name you used for your container.

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

• Location in the local file system, defined elsewhere in the configuration file as repo1-path. This is the default location when --repo is not specified in the pgbackrest command.

• Cloud Storage bucket, defined using the repo2-gcs-bucket configuration directives set up by this example. To use the Cloud Storage bucket, specify --repo=2 in the pgbackrest command.

What's next

• Restore an AlloyDB Omni cluster using pgBackRest