Restore backups

Crunchy Bridge automatically stores 10 days' worth of physical backups for each cluster. The pgBackRest package is used to make backups. See the Backups page for more information about PostgreSQL backup types in general and Crunchy Bridge backups in particular.

Restoring a fork is the easiest way to restore a backup on Crunchy Bridge. You can also use pgBackRest to restore backups to another PostgreSQL server, or download* backup files to store them offsite.

Read on for details about and examples of working with Crunchy Bridge backups.

Listing backups

The dashboard Backups tab displays a list of all the backups Crunchy Bridge is storing for a cluster. You can view the list on the CLI by running cb backup list <cluster-name>, or by using the Cluster Backup API endpoint.

Restoring a backup as a fork

Creating a new fork of the cluster is the easiest way to restore a backup. Click "Fork from Backup" next to any backup in the Backups tab to restore a backup to a new cluster fork. You can restore to a fork from the CLI by running cb create --fork <cluster-id> --at <timestamp like '2023-03-15T23:40:00Z'>, or by using the Cluster Backup API endpoint.

A new cluster will be created according to the options you specify and it will be populated with the data from the backup. Your existing cluster will not be affected by this operation.

Advanced backup management

Downloading backups

Backups are stored in a blob store on the same provider where the cluster is provisioned. You can generate credentials to access backups directly if a cluster is provisioned on AWS or Azure.

Generating access credentials

You'll need to generate access credentials for the blob store in order to access the backup files. Click the "Backup Credentials" button in the Backups tab of the dashboard to generate and reveal a set of credentials:

Note: Credentials displayed in the dashboard are already formatted for use with pgBackRest.

You can generate backup credentials on the CLI by running cb backup token <cluster-name>. By default, the CLI output is unformatted, which makes it easier to use with the AWS CLI or Azure's AzCopy. For pgBackRest-formatted credentials, add --format=pgbackrest to the command.

You can also work with backups programmatically using the Cluster Backup API endpoint. Note that there are separate endpoints for generating AWS or Azure backup tokens: AWSBackupToken and AzureBackupToken respectively.

Backup file format

Because backups on Crunchy Bridge are taken using pgBackRest, it's important to note that the backup files will be organized using this file layout convention:

<Container ID>/<Repo Path>/archive/<Stanza>/
<Container ID>/<Repo Path>/backup/<Stanza>/
Copy

A "Stanza" is a pgBackRest configuration for a PostgreSQL cluster. The name of the Stanza will be included in the credentials.

Examples: Accessing backup files with the CLI

Let's try an example of accessing your cluster's backup files using your provider's CLI tool, AWS CLI or Azure's AzCopy. As previously mentioned, it's not currently possible to access backup files on GCP directly.

In the walkthroughs below, we'll download all the data pgBackRest would need to restore a cluster's backups.

Using AWS CLI

For the purposes of this example, we assume you already have the AWS CLI installed on your machine.

First, use cb backup token <cluster-id> to generate access credentials. Copy the output somewhere as you'll be using various bits for the remainder of this walkthrough.

Next, add the access credentials to your .aws/credentials file. Copy the template below to the bottom of your .aws/credentials file and fill in the S3 Key, S3 Key Secret, and S3 Token you received from the cb backup token command. Note: Do not leave spaces around the = as it will cause errors in some shells.

[crunchy]
aws_access_key_id=
aws_secret_access_key=
aws_session_token=
Copy

Once you've configured your credentials file, use the access credentials you generated earlier to build the URI and run the command to view your cluster backups (minding the trailing slash):

aws s3 --profile crunchy ls s3://<s3_bucket>/<repo_path>/
Copy

You will see two folders here: archive and backup. The archive folder contains WAL files that pgBackRest needs to bring a backup to a consistent state. The backup folder contains the files for each stored backup in its own date-labelled folder, for example 20230314-230242F.

Let's recursively copy the archive and all the backups to a crunchy-backups folder on your local machine.

Navigate to a spot on your local machine where you want to receive the files. Then build and run the following command using the access credentials you generated earlier (minding the trailing slash):

aws s3 --profile crunchy cp s3://<s3_bucket>/<repo_path>/ ./crunchy-backups --recursive
Copy

Congratulations! You've successfully accessed and downloaded the backup files from the S3 bucket.

For details on how you would use these files to restore with pgBackRest, see the pgBackRest documentation. Restoring Outside Crunchy Bridge may also be helpful to review.

Using AzCopy

For the purposes of this example, we assume you already have Azure's AzCopy installed on your machine.

First, use cb backup token <cluster-id> to generate access credentials. Copy the output somewhere as you'll be using various bits for the remainder of this walkthrough.

Instead of configuring local environment variables, you'll append the Azure SAS Key to the end of each request in the examples below in order to authenticate to Azure.

Use the access credentials you generated earlier to build the URI and run the command to list the backup files:

azcopy list 'https://<Azure Account>.blob.core.windows.net/<Azure Container>?<Azure SAS Key>'
Copy

AzCopy will recursively list all the files in the container.

Let's copy the archive and all the backups to a crunchy-backups folder on your local machine.

Navigate to a spot on your local machine where you want to receive the files. Then build and run the following command using the access credentials you generated earlier:

azcopy copy 'https://<Azure Account>.blob.core.windows.net/<Azure Container>/*?<Azure SAS Key>' ./crunchy-backups --recursive
Copy

Congratulations! You've successfully accessed and downloaded the backup files from the Azure Container.

For details on how you would use these files to restore with pgBackRest, see the pgBackRest documentation. Restoring Outside Crunchy Bridge may also be helpful to review.

Restoring outside Crunchy Bridge

Because Crunchy Bridge's automatic backups are taken using the pgBackRest package, you must use the same package when you want to restore a backup outside of Crunchy Bridge.

To restore a backup, you'll follow these general steps which we walk through below:

1. Install PostgreSQL on the target server. The PostgreSQL Major version must match that of the backup.
2. Generate pgBackRest config with access credentials by running cb backup token --format=pgbackrest <cluster name>
3. Use the pgBackRest backup stanza to verify access to the backup files
4. Restore the stanza to the desired location
5. Validate proper configuration of PostgreSQL and its extensions
6. Start the new instance

Install PostgreSQL

This example starts with a base Alma Linux 8.6 image.

First, install PostgreSQL. The major PostgreSQL version that is being restored to needs to match the major PostgreSQL version from which the backup was made. The backup in this case is using PostgreSQL 14. Appropriate installers and/or repositories can be found here on the PostgreSQL download page.

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the Alma default PostgreSQL modules:
sudo yum -y module disable postgresql

# Install PostgreSQL and pgBackRest:
sudo yum install -y postgresql14-server pgbackrest
Copy

Generate pgBackRest config

Generate access credentials in pgBackRest format using cb and store them in /etc/pgbackrest.conf:

$ cb backup token --format=pgbackrest wmb5md6hm5avjfc7zqs4hr63xq > /etc/pgbackrest.conf
Copy

Verify access to backup files

The pgBackRest backup stanza is the first line of the generated configuration. Retrieve that and use it to list backups to ensure connectivity:

$ export STANZA="$(head -n1 < /etc/pgbackrest.conf  | sed -E 's/\[(.*)\]/\1/g')"
$ pgbackrest info --stanza=$STANZA
stanza: untz7xp4xnhtlf774im4jzfzyi
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000001/000000010000000000000004

        full backup: 20220606-142020F
            timestamp start/stop: 2022-06-06 09:20:20 / 2022-06-06 09:21:32
            wal start/stop: 000000010000000000000002 / 000000010000000000000003
            database size: 25.3MB, database backup size: 25.3MB
            repo1: backup set size: 4.8MB, backup size: 4.8MB
Copy

Restore the pgBackRest stanza

Begin the restore of the backup stanza to the target directory:

$ export STANZA="$(head -n1 < /etc/pgbackrest.conf  | sed -E 's/\[(.*)\]/\1/g')"
$ pgbackrest restore --stanza=$STANZA --pg1-path=/var/lib/pgsql/14/data/ --archive-mode=off
Copy

Validate PostgreSQL configuration

Ensure that Postgres and its extensions are configured properly:

1. The restored conf.d folder has server or instance-specific configurations that may no longer apply to the restored instance.
2. Any extensions that are required by the database should also be installed. Some extensions are not available outside of Crunchy Bridge (ie, pgpodman) and need to be removed from shared_preload_libraries.

Start instance

Now we're ready to start the instance:

sudo systemctl start postgresql-14
Copy

The instance will become available as soon as recovery is complete.