PostgreSQL Backups With pgbackrest and Wasabi S3

Photo by Denny Müller on Unsplash

PostgreSQL is a powerful and widely used open-source relational database management system (RDBMS). As with any important data system, regular backups are essential to ensure that data can be restored in case of system failure or data loss. There are many backup solutions available for PostgreSQL, but in this article, we will focus on using pgBackRest with Wasabi S3 as a cloud storage provider.

What is pgBackRest?

pgBackRest is a backup and restore solution designed for PostgreSQL. It offers many advanced features such as parallel backup and restore, incremental backups, full and differential backups, compression, and encryption. It also allows for easy backup and restore configuration through its own configuration file.

What is Wasabi S3?

Wasabi S3 is a cloud storage provider that offers fast and cost-effective storage for data backups. Wasabi S3 is an Amazon S3-compatible storage service, which means that it can be used with many backup solutions that support Amazon S3.

Configuring pgBackRest with Wasabi S3

Before we can start using pgBackRest with Wasabi S3, we need to configure both solutions.

First, we need to create a Wasabi S3 account and obtain the access key and secret key. These keys will be used to authenticate pgBackRest to Wasabi S3.

Next, we need to configure pgBackRest. The configuration file for pgBackRest is usually located at /etc/pgbackrest/pgbackrest.conf. In this file, we need to add the following lines to the [global] section:

[global]
...
archive-push-command = 'env AWS_ACCESS_KEY_ID=<access_key> AWS_SECRET_ACCESS_KEY=<secret_key> aws --endpoint-url=https://s3.<region>.wasabisys.com s3 cp %p s3://<bucket>/<path>/pgbackrest/%f'
archive-async = y

Replace <access_key>, <secret_key>, <region>, <bucket>, and <path> with the appropriate values for your Wasabi S3 account. These lines configure pgBackRest to use the Wasabi S3 endpoint for archive pushes and enable asynchronous archiving.

After configuring pgBackRest, we can start using it to create backups.

Creating backups with pgBackRest

Creating backups with pgBackRest is straightforward. To create a full backup, run the following command:

pgbackrest backup

This command will create a full backup of the PostgreSQL database and store it in the Wasabi S3 bucket specified in the configuration file.

To create an incremental backup, run the same command with the --type=incr option:

pgbackrest backup --type=incr

This command will create an incremental backup based on the last full backup and store it in the Wasabi S3 bucket.

Restoring backups with pgBackRest

Restoring backups with pgBackRest is also straightforward. To restore a full backup, run the following command:

pgbackrest restore

This command will restore the latest full backup from the Wasabi S3 bucket to the PostgreSQL database.

To restore an incremental backup, run the same command with the --type=incr option:

pgbackrest restore --type=incr

This command will restore the latest incremental backup from the Wasabi S3 bucket to the PostgreSQL database.

Conclusion

In this article, we have discussed how to use pgBackRest with Wasabi S3 as a cloud storage provider to create and restore backups of a PostgreSQL database. With pgBackRest, we can create full and incremental backups and store them securely in Wasabi S3. Wasabi S3 provides fast and cost-effective storage for backups, making it an ideal cloud storage provider for PostgreSQL