pgBackRest

pgBackRest is a tool for backing up PostgreSQL with S3 support for Linux OS.

Set up pgBackRest

1. Configure access to S3.
2. Configure PostgreSQL for WAL archiving.
3. Install pgBackRest.
4. Create a configuration and configure backups.

1. Configure access to S3

Access can be configured by the Account Owner or a user with the iam.admin role.

1. Create a service user with a role with access to S3. If you use a service user with the s3.user, object_storage_user or s3.bucket.user role, the bucket must have a configured access policy and its rules must allow access to this user.
2. Issue an S3 key to the user.

2. Configure PostgreSQL for WAL archiving

1. Open the CLI.

2. Open the postgresql.conf file:

   sudo nano /etc/postgresql/16/main/postgresql.conf

3. Set the parameter values:

   wal_level = replica
   archive_mode = on
   archive_command = 'pgbackrest --stanza=main archive-push %p'
   max_wal_senders = 3

4. Remove the # before each of the listed parameters and comments after them.

5. To exit the nano text editor while saving changes, press Ctrl+X → Y+Enter.

6. Apply the changes:

   sudo systemctl restart postgresql

3. Install pgBackRest

Install pgBackRest:

sudo apt install -y pgbackrest

4. Create the configuration and set up backups

1. Create the configuration directory:

   sudo mkdir -p /etc/pgbackrest

2. Open the /etc/pgbackrest.conf configuration file:

   nano /etc/pgbackrest.conf

3. Populate the /etc/pgbackrest.conf file:

   [main]
   pg1-path=/var/lib/postgresql/<postgresql_version>/main   
   
   [global]
   repo1-type=s3
   repo1-s3-endpoint=<s3_domain>
   repo1-s3-bucket=<bucket_name>
   repo1-s3-region=<pool>
   repo1-s3-key=<access_key>
   repo1-s3-key-secret=<secret_key>
   repo1-path=<path_to_repository>
   repo1-retention-full=<number_of_backups>
   log-level-console=info

   Specify:

   • <postgresql_version> – PostgreSQL data directory;
   • <s3_domain> – S3 API domain. The domain depends on the pool where S3 is located;
   • <bucket_name> – S3 bucket name. The bucket must use vHosted-style addressing;
   • <pool> – pool where S3 is located;
   • <access_key> – value of the Access key field from the S3 key;
   • <secret_key> – value of the Secret key field from the S3 key;
   • <path_to_repository> – root of the pgBackRest repository;
   • <number_of_backups> – number of simultaneously stored backups.

4. To exit the nano text editor while saving changes, press Ctrl+X → Y+Enter.

5. Make the postgres user the owner of the configuration:

   sudo chown -R postgres:postgres /var/lib/pgbackrest
   sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

6. Initialize stanza – a logical unit for pgBackRest that combines the cluster and repository:

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

   Where:

   • stanza-create — checks the data directory and repository, creates metadata for the backup;
   • check — confirms the configuration is correct.

Working with pgBackRest

Create a full backup

1. Open the CLI.

2. Create a full backup:

   sudo -u postgres pgbackrest --stanza=main --type=full backup

Create an incremental backup

To perform an incremental backup, at least one full backup must be created.

1. Open the CLI.

2. Create an incremental backup:

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

View information about stored backups

1. Open the CLI.

2. View information about stored backups:

   sudo -u postgres pgbackrest --stanza=main info

Restore a cluster from a backup

1. Open the CLI.

2. Before restoring a cluster, stop PostgreSQL:

   sudo systemctl stop postgresql

3. Restore the backup — the last created backup is selected automatically:

   sudo -u postgres pgbackrest --stanza=main restore

4. Start PostgreSQL:

   sudo systemctl start postgresql

5. Check the list of clusters:

   sudo pg_lsclusters

6. Check the list of databases:

   sudo -u postgres psql -c "\l"