dbi Blog

Introduction

One of the nice things with cloud providers is the low costs of their storage. Whenever you want to optimize your long-term backups and push them on Azure, you might face a wall of questions on how to do it with PostgreSQL securely and whether it would align with disaster recovery plans.
The following setup is an example of how you could manage having local PITR backups and offload your long-term full backups on Azure Blob Storage securely to reduce storage costs and extend your backup infrastructure to the cloud.

Architecture Overview

The architecture consists of the following components:

PostgreSQL Standalone Server (PG1): An Azure VM hosting PostgreSQL 17.2
Local PGBackRest Repository: Stores backups locally on the PG1 server for quick access.
Azure Blob Storage Account: Acts as a secondary, offsite repository for backups.
Managed Identity: Enables the VM to authenticate with Azure services without storing credentials on the VM.
SAS Token: Provides secure, time-limited access to the Blob Storage container.
Azure Key Vault: Securely stores sensitive information like the SAS token (optional but recommended).

+--------------------+       +------------------------+
|  Azure VM (PG1)    |       |  Azure Blob Storage    |
|  PostgreSQL 17     |<------>  Repo 2 (Remote Backup)|
|                    |       |   (Secondary Storage)  |
|  +--------------+  |       +------------------------+
|  | PGBackRest   |  |
|  |  Repo 1      |  |                     ^
|  | (Local       |  |                     |
|  |  Storage)    |  |                     |
|  +--------------+  |                     |
|                    |                     |
|                    |                     |
|  +---------------+ | Managed Identity    |
|  | Azure CLI     |<----------------------+
|  +---------------+ |
|  | SAS Token     | |
|  +---------------+ |
+--------------------+

Prerequisites

Azure Subscription: Active subscription to deploy resources.
Azure VM (PG1): Running compatible Linux distribution with PostgreSQL installed.
PGBackRest Installed: On the server.
Azure CLI Installed: Version 2.0 or higher on the server.
Managed Identity Enabled: On the Azure VM.
Azure Blob Storage Account and Container: Created for storing backups.

Step 1: Assign a Managed Identity to the PG1 VM

Navigate to Your VM in Azure Portal:
- Go to Azure Portal > Virtual Machines > Your VM (PG1).
Enable System-Assigned Managed Identity:
- Click on Identity in the left-hand menu.
- Under the System assigned tab, set the status to On.
- Click Save.

Step 2: Set Up Azure Blob Storage

Create a Storage Account:
- Go to Azure Portal > Storage Accounts > Create.
- Provide a unique Storage Account Name
- Choose the appropriate Subscription, Resource Group, and Location.
- Select Standard performance and BlobStorage account kind.
- Click Review + create and then Create.
Create a Blob Container:
- Navigate to your storage account.
- Under Data storage, select Containers.
- Click + Container.
- Name the container
- Set the Public access level to Private.
- Click Create.

Step 3: Grant Permissions to the Managed Identity

Assign Role to Managed Identity:
- Go to your Storage Account > Access Control (IAM).
- Click + Add > Add role assignment.
- In the Role dropdown, select Storage Blob Data Contributor.
- In the Assign access to dropdown, select Managed identity.
- Click + Select members, search for your VM’s name, and select it.
- Click Select and then Review + assign.

Step 4: Adapt the Backup Script

According your infrastructure, change the required parameters.
This works well for standalone servers, it can also serve as a base for a Patroni setup, in that case, you would need to modify the local pgBackRest configuration and push it to the other nodes as well with a “scp” command.

Script

#!/bin/bash

## Renew Azure Blob Storage SAS Token for PGBackRest

# Authenticate Azure CLI
az login --identity

# Variables
STORAGE_ACCOUNT_NAME="storage-account"
CONTAINER_NAME="container-name"
PG_BACKREST_CONF="/etc/pgbackrest.conf"
EXPIRY_DATE=$(date -u -d "+6 hours" +"%Y-%m-%dT%H:%M:%SZ")

# Retrieve the Storage Account Key
ACCOUNT_KEY=$(az storage account keys list \
    --resource-group "cloud-shell-storage-westeurope" \
    --account-name "$STORAGE_ACCOUNT_NAME" \
    --query "[0].value" \
    --output tsv)

if [ -z "$ACCOUNT_KEY" ]; then
    echo "Failed to retrieve storage account key."
    exit 1
fi

# Generate SAS Token
SAS_TOKEN=$(az storage container generate-sas \
    --account-name "$STORAGE_ACCOUNT_NAME" \
    --name "$CONTAINER_NAME" \
    --permissions acdlrw \
    --expiry "$EXPIRY_DATE" \
    --https-only \
    --account-key "$ACCOUNT_KEY" \
    --output tsv)

if [ -z "$SAS_TOKEN" ]; then
    echo "Failed to generate SAS token."
    exit 1
fi

echo "SAS Token generated successfully. It will expire on $EXPIRY_DATE."

# Update PGBackRest configuration with the SAS token
echo "Updating PGBackRest configuration on backup server..."

sudo awk -v sas_token="$SAS_TOKEN" '/^repo2-azure-key=/ {$0="repo2-azure-key=" sas_token} {print}' "$PG_BACKREST_CONF" > /tmp/pgbackrest.conf.tmp && sudo mv /tmp/pgbackrest.conf.tmp "$PG_BACKREST_CONF"

if [ $? -eq 0 ]; then
    echo "PGBackRest configuration updated successfully."
else
    echo "Failed to update PGBackRest configuration."
    exit 1
fi

echo "SAS token update completed."

Step 5: Configure PGBackRest for Azure Blob Storage

Update your /etc/pgbackrest.conf file to include the Azure Blob Storage as a secondary repository.

[global]
repo1-path=/u99/pgbackrest
repo1-retention-full=2

repo2-type=azure
repo2-path=/
repo2-azure-container=backups
repo2-azure-account=pgbackreststorage
repo2-azure-key=se=2024-12-02T05%3A36%3A34Z&sp=racwdl&spr=https&sv=2022-11-02&sr=c&sig=d16aYx67bpUT/MVNerRQG9cnIQYJ46%2Bggt3jeEC8fnQ%3D
repo2-azure-key-type=sas
repo2-retention-full=2

[pg17]
pg1-path=/u02/pgdata/17/PG17 
pg1-port=5435 
db-socket-path=/tmp

Note: The <Your_SAS_Token> value will be dynamically updated by your script.

Step 6: Test the Backup Process

Create your stanza and run a Manual Backup:

postgres@PG1:/home/postgres/renewSAS/ [PG17] pgbackrest --config=/etc/pgbackrest.conf --stanza=pg17 --log-level-console=info stanza-create
2024-12-01 17:36:56.834 P00   INFO: stanza-create command begin 2.54.0: --config=/etc/pgbackrest.conf --exec-id=10839-70ab76ac --log-level-console=info --pg1-path=/u02/pgdata/17/PG17 --pg1-port=5435 --pg1-socket-path=/tmp --repo2-azure-account=<redacted> --repo2-azure-container=backups --repo2-azure-key=<redacted> --repo2-azure-key-type=sas --repo1-path=/u99/pgbackrest --repo2-path=/ --repo2-type=azure --stanza=pg17
2024-12-01 17:36:56.844 P00   INFO: stanza-create for stanza 'pg17' on repo1
2024-12-01 17:36:56.844 P00   INFO: stanza 'pg17' already exists on repo1 and is valid
2024-12-01 17:36:56.844 P00   INFO: stanza-create for stanza 'pg17' on repo2
2024-12-01 17:36:57.473 P00   INFO: stanza-create command end: completed successfully (642ms)

postgres@PG1:/home/postgres/renewSAS/ [PG17] pgbackrest --config=/etc/pgbackrest.conf --stanza=pg17 --log-level-console=info --repo=2 --archive-copy backup
2024-12-01 17:37:22.303 P00   INFO: backup command begin 2.54.0: --archive-copy --config=/etc/pgbackrest.conf --exec-id=10844-31614f9c --log-level-console=info --pg1-path=/u02/pgdata/17/PG17 --pg1-port=5435 --pg1-socket-path=/tmp --repo=2 --repo2-azure-account=<redacted> --repo2-azure-container=backups --repo2-azure-key=<redacted> --repo2-azure-key-type=sas --repo1-path=/u99/pgbackrest --repo2-path=/ --repo1-retention-full=2 --repo2-retention-full=2 --repo2-type=azure --stanza=pg17
2024-12-01 17:37:22.550 P00   WARN: no prior backup exists, incr backup has been changed to full
2024-12-01 17:37:22.550 P00   INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2024-12-01 17:37:22.793 P00   INFO: backup start archive = 00000001000000010000008F, lsn = 1/8F000028
2024-12-01 17:37:22.793 P00   INFO: check archive for prior segment 00000001000000010000008E
2024-12-01 17:39:10.512 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-12-01 17:39:10.685 P00   INFO: backup stop archive = 00000001000000010000008F, lsn = 1/8F000158
2024-12-01 17:39:10.727 P00   INFO: check archive for segment(s) 00000001000000010000008F:00000001000000010000008F
2024-12-01 17:39:11.750 P00   INFO: new backup label = 20241201-173722F
2024-12-01 17:39:12.556 P00   INFO: full backup size = 1.5GB, file total = 1592
2024-12-01 17:39:12.556 P00   INFO: backup command end: completed successfully (110257ms)
2024-12-01 17:39:12.556 P00   INFO: expire command begin 2.54.0: --config=/etc/pgbackrest.conf --exec-id=10844-31614f9c --log-level-console=info --repo=2 --repo2-azure-account=<redacted> --repo2-azure-container=backups --repo2-azure-key=<redacted> --repo2-azure-key-type=sas --repo1-path=/u99/pgbackrest --repo2-path=/ --repo1-retention-full=2 --repo2-retention-full=2 --repo2-type=azure --stanza=pg17
2024-12-01 17:39:12.708 P00   INFO: expire command end: completed successfully (152ms)

postgres@PG1:/home/postgres/renewSAS/ [PG17] pgbackrest --config=/etc/pgbackrest.conf --stanza=pg17 --log-level-console=info --repo=1 backup
2024-12-01 17:45:32.275 P00   INFO: backup command begin 2.54.0: --config=/etc/pgbackrest.conf --exec-id=10957-81c3e6e7 --log-level-console=info --pg1-path=/u02/pgdata/17/PG17 --pg1-port=5435 --pg1-socket-path=/tmp --repo=1 --repo2-azure-account=<redacted> --repo2-azure-container=backups --repo2-azure-key=<redacted> --repo2-azure-key-type=sas --repo1-path=/u99/pgbackrest --repo2-path=/ --repo1-retention-full=2 --repo2-retention-full=2 --repo2-type=azure --stanza=pg17
2024-12-01 17:45:32.300 P00   INFO: last backup label = 20241201-164054F_20241201-164358I, version = 2.54.0
2024-12-01 17:45:32.300 P00   INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2024-12-01 17:45:32.564 P00   INFO: backup start archive = 000000010000000100000091, lsn = 1/91000028
2024-12-01 17:45:32.564 P00   INFO: check archive for prior segment 000000010000000100000090
2024-12-01 17:45:34.211 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-12-01 17:45:34.392 P00   INFO: backup stop archive = 000000010000000100000091, lsn = 1/91000120
2024-12-01 17:45:34.422 P00   INFO: check archive for segment(s) 000000010000000100000091:000000010000000100000091
2024-12-01 17:45:34.790 P00   INFO: new backup label = 20241201-164054F_20241201-174532I
2024-12-01 17:45:35.022 P00   INFO: incr backup size = 605.7KB, file total = 1591
2024-12-01 17:45:35.022 P00   INFO: backup command end: completed successfully (2750ms)
2024-12-01 17:45:35.022 P00   INFO: expire command begin 2.54.0: --config=/etc/pgbackrest.conf --exec-id=10957-81c3e6e7 --log-level-console=info --repo=1 --repo2-azure-account=<redacted> --repo2-azure-container=backups --repo2-azure-key=<redacted> --repo2-azure-key-type=sas --repo1-path=/u99/pgbackrest --repo2-path=/ --repo1-retention-full=2 --repo2-retention-full=2 --repo2-type=azure --stanza=pg17
2024-12-01 17:45:35.023 P00   INFO: expire command end: completed successfully (1ms)

Note : I use the “–archive-copy” command here to backup the required WAL with the FULL backup for repo 2. This avoids having parts of the WAL chain on both repositories. The idea here is that you would do your local (repo 1) backup for PITR purposes and your long-term backups on repo 2 (weekly/monthly/yearly).

Verify Backups:

You can note here that each backup set has a repo line specifying where the files are stored.

postgres@PG1:/home/postgres/renewSAS/ [PG17] pgbackrest --config=/etc/pgbackrest.conf --stanza=pg17 --log-level-console=info info
stanza: pg17
status: ok
cipher: none

db (current)
    wal archive min/max (17): 000000010000000100000087/000000010000000100000091

    full backup: 20241201-164054F
        timestamp start/stop: 2024-12-01 16:40:54+00 / 2024-12-01 16:41:49+00
        wal start/stop: 000000010000000100000089 / 000000010000000100000089
        database size: 1.5GB, database backup size: 1.5GB
        repo1: backup set size: 91MB, backup size: 91MB

    incr backup: 20241201-164054F_20241201-164358I
        timestamp start/stop: 2024-12-01 16:43:58+00 / 2024-12-01 16:44:00+00
        wal start/stop: 00000001000000010000008D / 00000001000000010000008D
        database size: 1.5GB, database backup size: 604.5KB
        repo1: backup set size: 91MB, backup size: 27.6KB
        backup reference total: 1 full

    full backup: 20241201-173722F
        timestamp start/stop: 2024-12-01 17:37:22+00 / 2024-12-01 17:39:10+00
        wal start/stop: 00000001000000010000008F / 00000001000000010000008F
        database size: 1.5GB, database backup size: 1.5GB
        repo2: backup set size: 91MB, backup size: 91MB

    incr backup: 20241201-164054F_20241201-174532I
        timestamp start/stop: 2024-12-01 17:45:32+00 / 2024-12-01 17:45:34+00
        wal start/stop: 000000010000000100000091 / 000000010000000100000091
        database size: 1.5GB, database backup size: 605.7KB
        repo1: backup set size: 91MB, backup size: 27.7KB
        backup reference total: 1 full, 1 incr

Verify Remote files Backups:

Use Azure Portal or Azure CLI to check the Blob Storage container for uploaded backup files.

az storage blob list --account-name pgbackreststorage --container-name backups --output table

Step 7: Automate the Script Execution

Schedule the script to run periodically to renew the SAS token before it expires.

Edit Crontab:

  sudo crontab -e

Add Cron Job:


    0 */11 * * * /path/to/renew_sas_token.sh >> /var/log/pgbackrest_sas_renew.log 2>&1

Runs the script every 11 hours.
Redirects output to a log file for troubleshooting.
You can also trigger the script from any automation platform you’d like.

Why Use Managed Identity with Short-Lived SAS Tokens?

While there are alternative methods, using Managed Identity to generate short-lived SAS tokens provides a balance between security and manageability.
pgBackRest does not support managed Identity yet, it might not be a priority for the project (link) but this solution is valid and secure from a compliance point of view.
This solution offers seamless integration with pgBackRest no code changes are required it is pretty flexible since you can adjust your token validity to when you push towards Azure Blob Storage and permissions are customizable. Automation is easy with a crontab or AAP…etc.
The management is simplified since the token generation is automated, the access control is centralized (with Azure AD RBAC policies) and the administrative overhead is reduced by avoiding key management.
Regarding security standards, using short-lived tokens and Managed Identities aligns with security best practices and standards like ISO 27001, SOC 2, GDPR, and revDSG/nLPD. Additionally, actions performed using Managed Identity are logged in Azure AD providing the audit logs for compliance and security reviews and access to Blob Storage Account can be monitored using Azure Storage analytics to detect unauthorized access attempts.
Obviously, some enhancement could be done, you could configure SSL for pgBackRest and backup encryption even though there is a limited added value here (the connection is local and Azure Blob Storage is already encrypted). But, overall I feel this offers a good foundation to help you setup and offload on Azure your backup repository.

References

Appendix: Full Script and config

17:45:39 postgres@PG1:/home/postgres/renewSAS/ [PG17] cat renewSASToken.bash
#!/bin/bash

## Renew Azure Blob Storage SAS Token for PGBackRest

# Authenticate Azure CLI
az login --identity

# Variables
STORAGE_ACCOUNT_NAME="pgbackreststorage"
CONTAINER_NAME="backups"
PG_BACKREST_CONF="/etc/pgbackrest.conf"
EXPIRY_DATE=$(date -u -d "+12 hours" +"%Y-%m-%dT%H:%M:%SZ")

# Retrieve the Storage Account Key
ACCOUNT_KEY=$(az storage account keys list \
    --resource-group "cloud-shell-storage-westeurope" \
    --account-name "$STORAGE_ACCOUNT_NAME" \
    --query "[0].value" \
    --output tsv)

if [ -z "$ACCOUNT_KEY" ]; then
    echo "Failed to retrieve storage account key."
    exit 1
fi

# Generate SAS Token
SAS_TOKEN=$(az storage container generate-sas \
    --account-name "$STORAGE_ACCOUNT_NAME" \
    --name "$CONTAINER_NAME" \
    --permissions acdlrw \
    --expiry "$EXPIRY_DATE" \
    --https-only \
    --account-key "$ACCOUNT_KEY" \
    --output tsv)

if [ -z "$SAS_TOKEN" ]; then
    echo "Failed to generate SAS token."
    exit 1
fi

echo "SAS Token generated successfully. It will expire on $EXPIRY_DATE."

# Update PGBackRest configuration with the SAS token
echo "Updating PGBackRest configuration on backup server..."

sudo awk -v sas_token="$SAS_TOKEN" '/^repo2-azure-key=/ {$0="repo2-azure-key=" sas_token} {print}' "$PG_BACKREST_CONF" > /tmp/pgbackrest.conf.tmp && sudo mv /tmp/pgbackrest.conf.tmp "$PG_BACKREST_CONF"

if [ $? -eq 0 ]; then
    echo "PGBackRest configuration updated successfully."
else
    echo "Failed to update PGBackRest configuration."
    exit 1
fi

echo "SAS token update completed."

18:16:54 postgres@PG1:/home/postgres/renewSAS/ [PG17] cat /etc/pgbackrest.conf
[global]
repo1-path=/u99/pgbackrest
repo1-retention-full=2

repo2-type=azure
repo2-path=/
repo2-azure-container=backups
repo2-azure-account=pgbackreststorage
repo2-azure-key=se=2024-12-02T05%3A36%3A34Z&sp=racwdl&spr=https&sv=2022-11-02&sr=c&sig=d16aYx67bpUT/MVNerRQG9cnIQYJ46%2Bggt3jeEC8fnQ%3D
repo2-azure-key-type=sas
repo2-retention-full=2

[pg17]
pg1-path=/u02/pgdata/17/PG17
pg1-port=5435
db-socket-path=/tmp
18:17:19 postgres@PG1:/home/postgres/renewSAS/ [PG17] sqh
psql (17.2 dbi services build)
Type "help" for help.

postgres=# show archive_mode;
 archive_mode
--------------
 on
(1 row)

postgres=# show archive_command;
                            archive_command
------------------------------------------------------------------------
 pgbackrest --config=/etc/pgbackrest.conf --stanza=pg17 archive-push %p
(1 row)

Post Views: 446

Offloading PostgreSQL Backups to Azure Blob Storage Using PGBackRest, Managed Identity, and SAS Tokens

Introduction

Architecture Overview

Prerequisites

Step 1: Assign a Managed Identity to the PG1 VM

Step 2: Set Up Azure Blob Storage

Step 3: Grant Permissions to the Managed Identity

Step 4: Adapt the Backup Script

Script

Step 5: Configure PGBackRest for Azure Blob Storage

Step 6: Test the Backup Process

Step 7: Automate the Script Execution

Why Use Managed Identity with Short-Lived SAS Tokens?

References

Adrien Obernesser

Leave a Reply:

Related blog articles

Introduction

Architecture Overview

Prerequisites

Step 1: Assign a Managed Identity to the PG1 VM

Step 2: Set Up Azure Blob Storage

Step 3: Grant Permissions to the Managed Identity

Step 4: Adapt the Backup Script

Script

Step 5: Configure PGBackRest for Azure Blob Storage

Step 6: Test the Backup Process

Step 7: Automate the Script Execution

Why Use Managed Identity with Short-Lived SAS Tokens?

References

Adrien Obernesser

Leave a Reply:

Related blog articles

Creating your private cloud using OpenStack – (6) – The Networking service

Creating your private cloud using OpenStack – (5) – The Compute service

Creating your private cloud using OpenStack – (4) – The Image and Placement services

Creating your private cloud using OpenStack – (3) – Keystone, the Identity Service