pgBackRest is a backup solution specifically designed for disaster recovery in PostgreSQL databases. Unlike other backup solutions like pg_dump, pgBackRest supports point-in-time recovery and offers many additional features.
pgBackRest can perform three types of backups:
- Full backups – these copy the entire contents of the database cluster to the backup.
- Differential backups – this copies only the database cluster files that have changed since the last full backup
- Incremental backups – which copy only the database cluster files that have changed since the last full, differential, or incremental.
- Creating a Delta Restore which will use database files already present and updated based on WAL segments. This makes potential restores much faster, especially if you have a large database and don’t want to restore the entire thing.
- Letting you have multiple backup repositories – say one local or one remote for redundancy.
With all its features, pgBackRest is undoubtedly the best option for backing up PostgreSQL.
installing and configuring
We will begin by installing pgBackRest on an Ubuntu Server 22 using the following command:
| sudo apt install pgbackrest |
Next, we will create a directory to store the backups. It is always best practice to store backups on a separate mount point from the root directory to avoid interfering with the operating system.
| su chown postgres:postgres /db_backup/ |
Now, we will configure pgBackRest. Open the /etc/pgbackrest.conf file using your preferred text editor. I will be usingvi
| sudo vi /etc/pgbackrest.conf |
We will remove the # from [main] and pg1-path and update them with the correct data directory for our PostgreSQL database.
If you don’t know the data directory path, use the command pg_lscluster to find it. We will also add the user that pgBackRest will use to connect to PostgreSQL and specify the port number.
In the [global] section, we will update the retention settings for full and incremental backups, and specify the location for storing the backups.
Below is the complete configuration I have specified for pgBackRest:
| [global] repo1-block=y repo1-bundle=y repo1-path=/db_backup/base_backup repo1-retention-diff=1 repo1-retention-full=2 start-fast=y compress-level=6 [main] pg1-path=/var/lib/postgresql/15/main pg1-user=postgres pg1-port=5432 |
I won’t go into detail about each option in the configuration, but in summary, I have specified the compression level as well as the retention settings for both full and incremental backups.
to view other option please visit EDB website https://www.enterprisedb.com/docs/supported-open-source/pgbackrest/04-recommended_settings/
Now, we’ll update the PostgreSQL configuration related to archiving, which is necessary for point-in-time recovery.
Open the postgresql.conf file using either vi or nano.
| sudo vi /etc/postgresql/15/main/postgresql.conf |
Update the following parameters as follows:
| listen_addresses = ‘*’ wal_level = replica archive_mode = on archive_command = ‘pgbackrest –stanza=main archive-push %p’ |
after that restart postgresql services
| systemctl restart postgresql |
creating stanza
A stanza is the configuration for a PostgreSQL database cluster that defines its location. To start, we will use the following command, which will create a stanza based on the configuration we have specified:
| sudo -u postgres pgbackrest –stanza=main –log-level-console=info stanza-create |
You may encounter a similar error. If so, simply apply ownership to the postgres user again and rerun the command; it should then work.
| sudo chown -R postgres:postgres /db_backup/ |
The following command will check our configuration and ensure that our backup will be taken without errors:
| sudo -u postgres pgbackrest –stanza=main –log-level-console=info check |
We are all set now. We can start taking a full backup and test the restoration process.
backup and restore
For testing purposes, I will log in to psql and drop the database. However, before doing that, I will take a full backup using the following command:
| sudo -u postgres pgbackrest –stanza=main –log-level-console=info –type=full backup |
The backup has been taken successfully. Now, let’s test the restoration process. Before doing so, I will log in to psql and drop a random database.
Let’s restore our backup. One key point to remember is to ensure that the PostgreSQL service is shut down and verify that the database cluster is not running.
| systemctl stop postgresql pg_lscluster |
Now, go to your data directory and remove all the files there.
Run the following command to restore the latest backup. Note that by default, pgBackRest will restore the most recent backup available.
| sudo -u postgres pgbackrest –stanza=main –log-level-console=info restore |
start the services for postgresql
| systenctl start postgresql |
For more information on how to use pgBackRest and explore additional options such as setting up a backup server, please visit the following URL: