In my previous post about pgBackRest, we saw how to install and setup pgBackRest and make a backup of a PostgreSQL database with it. It was a very basic single server setup, only intended to get the hang of the tool. Such setups are not used in a production environment, as it is not recommended (or rather does not serve the purpose) to perform the backup on the same server where the database is running. So: let’s get familiar with how remote backup servers are set up with pgBackRest, and how a full and incremental backup is performed from the backup server and restored on the database server.
We need two servers. Let’s call ours:
pgbackup
db1
Installing pgbackrest:
We need to install pgBackRest on the database and the backup server. Make sure you install the same version on both.
For the database server, please follow the installation steps from my previous post. The steps are slightly different for the backup server, since it is a better practice to create a separate user to own the pgBackRest repository.
Create a pgbackrest
user on the backup server
sudo adduser --disabled-password --gecos "" pgbackrest
Install required Perl package and pgBackRest from a package or manually on pgbackup as below
sudo apt-get install libdbd-pg-perl sudo scp BUILD_HOST:/root/pgbackrest-release-2.14/src/pgbackrest /usr/bin/ sudo chmod 755 /usr/bin/pgbackrest
Create pgBackRest configuration files, directories and repository on pgbackup
sudo mkdir -p -m 770 /var/log/pgbackrest sudo chown pgbackrest:pgbackrest /var/log/pgbackrest sudo mkdir -p /etc/pgbackrest sudo mkdir -p /etc/pgbackrest/conf.d sudo touch /etc/pgbackrest/pgbackrest.conf sudo chmod 640 /etc/pgbackrest/pgbackrest.conf sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf sudo mkdir -p /var/lib/pgbackrest sudo chmod 750 /var/lib/pgbackrest sudo chown pgbackrest:pgbackrest /var/lib/pgbackrest
Now we are ready to proceed with enabling communication between the database and the backup server. For that, pgBackRest requires a passwordless SSH connection.
We can do that by generating an SSH authentication key file using the
ssh-keygen
command.
On the pgbackup
server as pgbackrest
user:
sudo -u pgbackrest mkdir -m 750 /home/pgbackrest/.ssh sudo -u pgbackrest ssh-keygen -f /home/pgbackrest/.ssh/id_rsa \ -t rsa -b 4096 -N ""
On db1
as postgres
user:
sudo -u postgres mkdir -m 750 -p /var/lib/postgresql/.ssh sudo -u postgres ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa \ -t rsa -b 4096 -N ""
Exchange the public keys generated between the servers
On pgbackup
:
cat ~/.ssh/id_rsa.pub | ssh postgres@db1 "cat >> ~/.ssh/authorized_keys"
On db1
:
cat ~/.ssh/id_rsa.pub | ssh pgbackrest@pgbackup "cat >> ~/.ssh/authorized_keys"
Test the passwordless connection as follows:
root@pgbackup:~$ sudo -u pgbackrest ssh postgres@db1 root@db1:~$ sudo -u postgres ssh pgbackrest@repository
You can find more details about passwordless SSH connection here.
Configuration
On the pgbackup
server, configure the pgBackRest configuration file with the database host and path, along with the repository path where the backups will be stored. We have added the database host as ‘pg1-host
‘ so that we can add more database hosts later as ‘pg2-host
‘ and so on. The same goes for pg1-path
.
cat /etc/pgbackrest/pgbackrest.conf [demo] pg1-host=db1 pg1-path=/var/lib/postgresql/10/main [global] repo1-path=/var/lib/pgbackrest repo1-retention-full=2 start-fast=y
The Start Fast Option (--start-fast
) has been used to force a checkpoint to start the backup quickly. Otherwise, the backup will start after the next regular checkpoint.
On server db1
, configure the pgBackRest configuration file with the database path and backup host as follows:
cat /etc/pgbackrest/pgbackrest.conf [demo] pg1-path=/var/lib/postgresql/10/main [global] log-level-file=detail repo1-host=pgbackup
On db1
, update postgresql.conf
to have the following changes:
archive_command = 'pgbackrest --stanza=demo archive-push %p' archive_mode = on listen_addresses = '*' log_line_prefix = '' max_wal_senders = 3 wal_level = replica
Now restart PostgreSQL to reflect the configuration changes.
Create a stanza on the pgbackup server and check if it’s working. Getting no result means the stanza has been created successfully.
sudo -u pgbackrest pgbackrest --stanza=demo stanza-create sudo -u pgbackrest pgbackrest --stanza=demo check
Check if the stanza configuration is correct on db1
:
sudo -u postgres pgbackrest --stanza=demo check
Remote Backup
With the setup ready, let’s take our first remote backup from pgbackup
. The default backup is incremental, but the first backup is always a full backup.
sudo -u pgbackrest pgbackrest --stanza=demo backup sudo -u pgbackrest pgbackrest --stanza=demo info stanza: demo status: ok cipher: none db (current) wal archive min/max (10-1): 000000010000000000000007/000000010000000000000008 full backup: 20190726-133657F timestamp start/stop: 2019-07-26 13:36:57 / 2019-07-26 13:37:07 wal start/stop: 000000010000000000000007 / 000000010000000000000007 database size: 22.5MB, backup size: 22.5MB repository size: 2.6MB, repository backup size: 2.6MB
You will find directories and files being created in the backup location.
Restore
Taking a full backup was easy. Let’s see some examples of incremental backup and recovery with a target:
Create a test database on db1
:
postgres=# create database test; CREATE DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres postgres=CTc/postgres test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows)
Make another backup. By default, this will be incremental since it’s the second backup and we are not specifying the type:
sudo -u pgbackrest pgbackrest --stanza=demo backup
To restore, stop PostgreSQL on db1
and run pgbackrest with the restore
command:
sudo service postgresql stop sudo -u postgres pgbackrest --stanza=demo --delta restore sudo service postgresql start
If you check the database now, you will not find the test database. This is because the backup was restored from the first full backup.
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres postgres=CTc/postgres (3 rows)
To restore the data up to the incremental backup, run the restore command with recovery_target
in --recovery-option
.
sudo service postgresql stop sudo -u postgres pgbackrest --stanza=demo --delta restore \ --recovery-option=recovery_target=immediate sudo service postgresql start
Let’s check the database now:
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows)
I hope this exercise could help to get you started with remote backup and pgBackRest! To learn more commands, please visit the pgBackRest official site here.
Stay tuned for more tutorials and exercises with pgBackRest. If you are not sure how to use PostgreSQL efficiently or how to setup your systems in general consider checking out our support services.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.