Backing Up PostgreSQL with pgBackRest and Amazon S3

For high-availability databases, consistent backups are essential to ensure data integrity and quick disaster recovery. In this guide, we’ll cover how to configure pgBackRest to back up a standalone PostgreSQL instance and securely store the backups in an Amazon S3 bucket.

1. Setting Up PostgreSQL

Before configuring pgBackRest, make sure you have a working PostgreSQL installation. For this guide, we’re assuming PostgreSQL is already installed and initialized on your system. Make sure to enable archiving in your PostgreSQL configuration, as pgBackRest relies on this setting.

# Edit PostgreSQL configuration file
nano /var/lib/pgsql/16/data/postgresql.conf

# Enable archiving and configure the archive command for pgBackRest
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

Once configured, restart PostgreSQL to apply the changes.

sudo systemctl restart postgresql

2. Installing and Configuring pgBackRest

Install pgBackRest on your system. For most Linux distributions, it can be installed via package managers:

sudo yum install pgbackrest -y  # For RedHat-based systems
# or
sudo apt install pgbackrest -y  # For Debian-based systems

Next, configure pgBackRest to point to the PostgreSQL data directory and specify the backup repository on S3.

Create the pgbackrest configuration file:

sudo mkdir -p /etc/pgbackrest
sudo nano /etc/pgbackrest/pgbackrest.conf

Add the following content to the configuration file:

[global]
repo1-type=s3
repo1-s3-endpoint=<BUCKET-ENDPOINT>         # S3 bucket endpointi (örn. s3.amazonaws.com)
repo1-s3-bucket=<BUCKET-NAME>                # S3 bucket isminiz
repo1-s3-key=<ACCESS-KEY>                    # AWS erişim anahtarınız
repo1-s3-key-secret=<SECRET-KEY>             # AWS gizli erişim anahtarınız
repo1-s3-region=<REGION>                     # S3 bölgeniz (örn. us-east-1)
repo1-path=/pgbackrest                       # Yedeklerin kaydedileceği s3 dizini
repo1-retention-full=2                       # Sadece iki tam yedeği sakla
start-fast=y                                 # Hızlı yedekleme başlangıcı
archive-async=y                              # Asenkron arşivleme modu
log-level-file=info                          # Log seviyesi (info, detay veya debug olarak değiştirilebilir)

[main]
pg1-path=/var/lib/pgsql/16/data              # PostgreSQL ana dizini

Make sure that pgBackRest has access to the S3 bucket. You can use AWS CLI to configure credentials or set up an IAM role with the necessary permissions if this is running on an EC2 instance.

3. Setting Up Permissions and Directories

Create the necessary directories and adjust ownership and permissions for pgBackRest:

sudo mkdir -p /var/lib/pgbackrest
sudo chown -R postgres:postgres /var/lib/pgbackrest
sudo chmod -R 700 /var/lib/pgbackrest

4. Initializing the Stanza

In pgBackRest, a stanza is a configuration for each PostgreSQL instance. You need to create a stanza for your PostgreSQL cluster.

postgres pgbackrest --stanza=main --log-level-console=info stanza-create
pgbackrest --stanza=main --log-level-console=info check
pgbackrest --stanza=main --log-level-console=info backup

The stanza-create command configures pgBackRest to work with the specified PostgreSQL instance, while the check command verifies that everything is set up correctly.

5. Running the First Backup

Once the configuration is verified, take your first backup by running the following command:

sudo -u postgres pgbackrest --stanza=main --log-level-console=info backup

This command initiates a full backup of the PostgreSQL data to your S3 bucket.

6. Automating Backups

To automate backups, you can set up a cron job. For example, the following cron job takes a full backup every 15 minutes and logs the output:

sudo vi /var/lib/pgsql/scripts/pgbackrest.sh

Add the following commands to pgbackrest.sh:

#!/bin/bash
pgbackrest --stanza=main --log-level-console=info backup

Make it executable:

chmod +x /var/lib/pgsql/scripts/pgbackrest.sh

Then, set up a cron job to run this script:

crontab -e
*/15 * * * * /var/lib/pgsql/scripts/pgbackrest.sh >> /var/log/pgbackrest_backup.log 2>&1

Restoring from S3

In the event of a failure, you can restore your backup from the S3 bucket. Run the following pgBackRest command to initiate a restore:

sudo -u postgres pgbackrest --stanza=main --delta --log-level-console=info restore