Skip to content

Latest commit

 

History

History
210 lines (178 loc) · 7.74 KB

index.mdx

File metadata and controls

210 lines (178 loc) · 7.74 KB
meta content tags dates categories
title description
Backing up PostgreSQL databases with pgBackRest to Object Storage
How to install pgBackRest on Scaleway Instance for backing up PostgreSQL databases to Object Storage
h1 paragraph
Backing up PostgreSQL databases with pgBackRest to Object Storage
This page shows how to backup PostgreSQL databases with pgBackRest to Object Storage
postgresql pgbackrest backup configuration setup s3
validation
2025-01-22
object-storage
postgresql-and-mysql

pgBackRest aims to be a reliable, easy-to-use backup and restore solution that can seamlessly scale up to the largest databases and workloads by utilizing algorithms that are optimized for database-specific requirements.

pgBackRest can back up in three ways:

  • Full backup: This is a backup of every file under the database directory ($PGDATA). A full backup of a PostgreSQL server is required first. It is the only backup that pgBackRest can load by itself.
  • Differential backup: This only retrieves files that have changed since the last full backup. Both the full backup and the differential backup must be intact.
  • Incremental backup: This retrieves files that have changed since the last backup. The last backup can be a full backup or a differential backup.

Installing pgBackRest

The PostgreSQL Global Development Group (PGDG) provides an apt and yum repository. After importing the repository, you can install the PgBouncer package.

For Ubuntu/Debian:

  1. Create the file repository configuration: 
    sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
  2. Import the repository signing key: 
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
  3. Update the package lists: 
    apt update
  4. Install the pgbackrest package 
    apt install -y pgbackrest

For RHEL/Rocky Linux:

  1. Install the repository RPM: 
    dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  2. Disable the built-in PostgreSQL module: 
    dnf -qy module disable postgresql
  3. Install the pgbackrest package 
    dnf install -y pgbackrest

Setting up pgBackRest

  1. Edit the /etc/pgbackrest.conf with the following changes (repository configurations for example purposes only):

    [global]
repo1-type=s3
repo1-s3-endpoint=<BUCKET-ENDPOINT>
repo1-s3-bucket=<BUCKET-NAME>
repo1-s3-key=<ACCESS-KEY>
repo1-s3-key-secret=<SECRET-KEY>
repo1-s3-region=<REGION>
repo1-path=/pgbackrest

repo1-retention-full=1
start-fast=y
archive-async=y

[db-primary]
pg1-path=/var/lib/pgsql/14/data
    PostgreSQL data directory is `/var/lib/postgresql/14/main` in Ubuntu/Debian by default

  2. Create a stanza for defining the cluster:

    sudo -u postgres pgbackrest --stanza=db-primary stanza-create

Setting up PostgreSQL

  1. Edit the PostgreSQL configuration file /etc/postgresql/14/main/postgresql.conf as follows:

    archive_mode = on
archive_command = 'pgbackrest --stanza=db-primary archive-push %p'
    Changing the `archive_mode` parameter requires a restart, but a reload is sufficient if you are only changing the `archive_command` parameter. If you want WAL archiving to be done only during backup, you can add the following command: ``` test -f /tmp/pgbackrest/db-primary-backup.lock || exit 0 && pgbackrest --stanza=db-primary archive-push %p ``` It is not a method supported by pgBackRest. This should be monitored carefully.

  2. Restart PostgreSQL to apply the changes:

    For RHEL/Rocky Linux:

    Restart the postgresql-14.service to apply the changes.

    systemctl restart postgresql-14.service

    For Ubuntu/Debian:

    Restart the PostgreSQL 14 main cluster to apply the changes.

    pg_ctlcluster restart 14 main

  3. Use the following command to check the configuration:

    sudo -u postgres pgbackrest --stanza=db-primary check

Performing backups

  • To start a full backup for db-primary, use this command: 
    sudo -u postgres pgbackrest --stanza=db-primary --type=full backup
  • To start a differential backup for db-primary, use this command: 
    sudo -u postgres pgbackrest --stanza=db-primary --type=diff backup
  • To start an incremental backup for db-primary, use this command: 
    sudo -u postgres pgbackrest --stanza=db-primary --type=incr backup
  • To view a list of all backups available of db-primary, use this command: 
    sudo -u postgres pgbackrest --stanza=db-primary info
If there are no existing backups for a stanza, the backup type will be set to full.

Restoring from backups

  • To restore from backup to the same location on the DB server, you can start the restore process with the following command: 
    sudo -u postgres pgbackrest --stanza=db-primary restore
  • To restore from backup to the same location on the DB server and at a specified time, you can start the restore process with the following command: 
    sudo -u postgres pgbackrest --stanza=db-primary --type=time --target="2022-06-02 17:05:23" restore
  • To restore from backup to a desired location on the DB server, you can start the restore process with the following command: 
    sudo -u postgres pgbackrest --stanza=db-primary --reset-pg1-host --pg1-path=/var/lib/pgsql/14/restored restore
A full restore can take a long time on large databases. If the connection is lost, the restoration process may be interrupted. For this reason, running the restore command in `screen` or `tmux` will increase reliability. If only missing files need to be added, you can use the `--delta` parameter. This parameter restores only missing files.

Automatizing backups

If you want to back up at certain time intervals, you can add this to the cron:

  1. Edit the crontab of the postgres user: 
    crontab -u postgres -e
  2. Add the following line to the crontab and save it. This particular example runs a full backup at 00:00 on every Wednesday and an incremental backup at 00:00 on every Saturday: 
    0 0 * * 3 /usr/bin/pgbackrest --stanza=db-primary --type=full backup
0 0 * * 6 /usr/bin/pgbackrest --stanza=db-primary --type=incr backup

Deleting old backups manually:

  • To keep only the last full backup: 
    sudo -u postgres pgbackrest --stanza=db-primary --repo1-retention-full=1 expire
  • To keep only the last differential backup: 
    sudo -u postgres pgbackrest --stanza=db-primary --repo1-retention-diff=1 expire