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.
Table of Contents
We need two servers. Let's call ours:
pgbackupdb1
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.
pgbackrest user on the backup server
|
1 |
sudo adduser --disabled-password --gecos '' pgbackrest |
|
1 2 3 |
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 |
pgbackup
|
1 2 3 4 5 6 7 8 9 10 11 |
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
|
1 |
ssh-keygen |
command.
On the pgbackup server as pgbackrest user:
|
1 2 3 |
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:
|
1 2 3 |
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 '' |
On pgbackup:
|
1 |
cat ~/.ssh/id_rsa.pub | ssh postgres@db1 'cat >> ~/.ssh/authorized_keys' |
On db1:
|
1 |
cat ~/.ssh/id_rsa.pub | ssh pgbackrest@pgbackup 'cat >> ~/.ssh/authorized_keys' |
Test the passwordless connection as follows:
|
1 2 3 |
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.
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.
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 |
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.
|
1 2 |
sudo -u pgbackrest pgbackrest --stanza=demo stanza-create sudo -u pgbackrest pgbackrest --stanza=demo check |
Check if the stanza configuration is correct on db1:
|
1 |
sudo -u postgres pgbackrest --stanza=demo check |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 |
sudo -u pgbackrest pgbackrest --stanza=demo backup |
To restore, stop PostgreSQL on db1 and run pgbackrest with the restore command:
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
+43 (0) 2622 93022-0
office@cybertec.at
Nice article. Just one question where do I setup async-archiving, on DB server or repository server?
I am not able to restore one backup to another new servers.
i get error "invalid backupset"
Requesting to post a solution on restores on remote nodes .
say backup on hots1 and restore the same backup on host2,host3..hostn..
I get error like "invalid backup set"
May i know what is the syntax to backup only 1 database using pgbackrest command? Tnx
Since it's works on the same principle as pg_basebackup, it doesn't backup individual database but entire cluster. However, you can restore just a selected database with pgBackRest https://pgbackrest.org/user-guide.html#restore/option-db-include
Great Article.
I have some questions regarding pgbackrest.
1: can i restore the full backup on another machine using Pgbackrest restore command?
2: PITR is functioning properly when i dropped a table and restored it( with differential backup) but when i apply incremental backup and then drop table, the table isn't restored.
3: If my data resides in different table spaces, is it going to be a problem when performing backups?
Thanks Granthana Biswas