{"id":6175,"date":"2026-08-28T13:16:52","date_gmt":"2026-08-28T10:16:52","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=6175"},"modified":"2026-08-28T16:23:22","modified_gmt":"2026-08-28T13:23:22","slug":"pos-kanoyme-backup-restore-sti-postgresql-ti-chrisi-to","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-backup-restore-sti-postgresql-ti-chrisi-to\/","title":{"rendered":"How to Backup and Restore PostgreSQL using the pgBackrest tool"},"content":{"rendered":"

As we have seen in previous articles in older versions of PostgreSQL before version 17 there was no possibility for block level backup as a result we could not do differential and incremental backups and we had to take full backup every day. Also the management of backups and restore \/ recovery through WALs (archive logs) was complicated. These two problems can be solved by using a 3rd party tool of pgBackrest<\/strong>. <\/p>\n\n\n\n

<\/p>\n\n\n\n

The installation steps<\/h2>\n\n\n\n

To install it on a Unix version Oracle Linux \/ Red Hat, run the following command:<\/p>\n\n\n\n

sudo yum install pgbackrest --nogpgcheck -y<\/pre>\n\n\n\n
After the installation is complete, to see that it has been installed successfully, we can check the version with the following command:<\/p>\n\n\n\n
pgbackrest version<\/pre>\n\n\n\n
Then we create the folders where the backups will be stored and give them the correct permissions:<\/p>\n\n\n\n
sudo mkdir -p \/var\/lib\/pgbackrest\nsudo chmod 750 \/var\/lib\/pgbackrest\nsudo chown postgres:postgres \/var\/lib\/pgbackrest<\/pre>\n\n\n\n
With the following command we find the path where PostgreSQL is installed, we will need it later:<\/p>\n\n\n\n
sudo -u postgres psql -c \"SHOW data_directory;\"<\/pre>\n\n\n\n
Then we modify the configuration file, as follows: repo1-path<\/code> the location where the backups will be stored, as repo1-process-max<\/code> is the parallelism we allow him, as repo1-retention-full<\/code> is how many backups we want to keep, below we define the name of the backupset, for our example I have said postdb11 and below as pg1-path<\/code> we put the path where PostgreSQL is installed:<\/p>\n\n\n\n
sudo vi \/etc\/pgbackrest.conf<\/pre>\n\n\n\n
[global]\nrepo1-path=\/var\/lib\/pgbackrest\nrepo1-retention-full=2\nprocess-max=2\n\n[postdb11]\npg1-path=\/var\/lib\/pgsql\/11\/data<\/code><\/pre>\n\n\n\n
We need to give this file the correct permissions:<\/p>\n\n\n\n
sudo chown postgres:postgres \/etc\/pgbackrest.conf\nsudo chmod 640 \/etc\/pgbackrest.conf<\/pre>\n\n\n\n
Then we go to the PostgreSQL configuration file where we will need to set some parameters:<\/p>\n\n\n\n
sudo vi \/var\/lib\/pgsql\/11\/data\/postgresql.conf<\/pre>\n\n\n\n
Let's go down to the category. # – <\/em><\/strong>Archiving <\/strong><\/em>and add the following:<\/p>\n\n\n\n
# - Archiving -\n\narchive_mode = on               # enables archiving; off, on, or always\n\narchive_command = 'pgbackrest --stanza=postdb11 archive-push %p'<\/code><\/pre>\n\n\n\n
As well as the wal_level<\/code>:<\/p>\n\n\n\n
wal_level = replica<\/code><\/pre>\n\n\n\n
Then we will need to restart the PostgresSQL service:<\/p>\n\n\n\n
sudo systemctl restart postgresql-11<\/pre>\n\n\n\n
<\/p>\n\n\n\n
How backups are performed via pgBackrest<\/h3>\n\n\n\n
To now perform a backup, we must first create the backup set with the name we gave in the configuration file, in our case with the name postdb11:<\/p>\n\n\n\n
sudo -u postgres pgbackrest --stanza=postdb11 stanza-create<\/pre>\n\n\n\n
To take a full backup, run the following command:<\/p>\n\n\n\n
sudo -u postgres pgbackrest --stanza=postdb11 --type=full backup<\/pre>\n\n\n\n
If we want to click on it and take a differential backup, we run the following:<\/p>\n\n\n\n
sudo -u postgres pgbackrest --stanza=postdb11 --type=diff backup<\/pre>\n\n\n\n
Now to see what backups have been performed and we have, we run the command:<\/p>\n\n\n\n
sudo -u postgres pgbackrest --stanza=postdb11 info<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Adding a process to a scheduler (cronjob)<\/h2>\n\n\n\n
We can now simply add two jobs to automate the backup process with a full backup every Sunday at 11:00 PM and differential backups on the other days.<\/p>\n\n\n\n
To give this we need to add the following to crontab<\/strong>:<\/p>\n\n\n\n
sudo -u postgres crontab -e<\/pre>\n\n\n\n
# Full Backup \n0 23 * * 0 pgbackrest --stanza=postdb11 --type=full backup >> \/var\/log\/pgbackrest\/backup-full.log 2>&1\n\n# Differential Backup \n0 23 * * 1-6 pgbackrest --stanza=postdb11 --type=diff backup >> \/var\/log\/pgbackrest\/backup-diff.log 2>&1\n<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
How to restore the database using pgBackrest<\/h2>\n\n\n\n
First we stop the service:<\/p>\n\n\n\n
sudo systemctl stop postgresql-11<\/pre>\n\n\n\n
We delete the database files for the example:<\/p>\n\n\n\n
sudo -u postgres rm -rf \/var\/lib\/pgsql\/11\/data\/*<\/pre>\n\n\n\n
To start the restore, run the following command with the parameter target <\/code>we can do Point In Time Restore<\/strong>:<\/p>\n\n\n\n
sudo -u postgres pgbackrest --stanza=postdb11 restore --type=time --target=\"2026-07-25 23:30:00\"<\/pre>\n\n\n\n
To see the restore log, run the following:<\/p>\n\n\n\n
sudo tail -f \/var\/log\/pgbackrest\/postdb11-restore.log<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To view the PostgreSQL log, run the following:<\/p>\n\n\n\n
sudo journalctl -u postgresql-11.service -e --no-pager<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n