Backup and replicate Postgresql 12 using pgBackRest in Ubuntu 20.04

In this post, I will guide you through the process of setting up a continuous backup system and a replication server using pgBackRest.

For this guide, we need to have 3 servers: pg-master, pg-replica and pg-backup.

On all of these servers, we need to add these lines to /etc/hosts file and put the correct ips there:

{master_server_ip} pg-master
{replica_server_ip} pg-replica
{backup_server_ip} pg-backup

On pg-master:

sudo apt install postgresql-12 pgbackrest

On pg-replica:

sudo apt install postgresql-12 pgbackrest

On pg-backup:

sudo apt install pgbackrest

Since our servers should communicate with each other using ssh connection, we create ssh key pairs on each of them:

On pg-master, pg-replica and pg-backup run these commands:

sudo su postgres
ssh-keygen -t rsa -b 4096 -N ""

And then share all of the public keys inside /var/lib/postgresql/.ssh/authorized_keys file on each server. Then we need to check that all connections can be established without any problem:

On pg-master:

sudo -u postgres ssh postgres@pg-backup
[exit]
sudo -u postgres ssh postgres@pg-replica
[exit]

On pg-replica:

sudo -u postgres ssh postgres@pg-backup
[exit]
sudo -u postgres ssh postgres@pg-master
[exit]

On pg-backup:

sudo -u postgres ssh postgres@pg-master
[exit]
sudo -u postgres ssh postgres@pg-replica
[exit]

If everything works correctly, it's time to configure pgBackRest.

On pg-master, change the content of /etc/pgbackrest.conf with these lines:

[my_cluster]
pg1-path=/var/lib/postgresql/12/main

[global]
repo1-host=pg-backup
repo1-host-user=postgres

[global:archive-push]
compress-level=3

And then, open /etc/postgresql/12/main/postgresql.conf and change these variables:

archive_command = 'pgbackrest --stanza=my_cluster archive-push %p'
archive_mode = on
listen_addresses = '*'
max_wal_senders = 3
wal_level = replica

And then restart postgresql:

sudo systemctl restart postgresql

After that, we will create some test data:

sudo -u postgres psql

psql (12.4 (Ubuntu 12.4-1.pgdg18.04+1))
Type "help" for help.

postgres=# CREATE DATABASE my_db;
CREATE DATABASE
postgres=# \c my_db
You are now connected to database "my_db" as user "postgres".
my_db=# CREATE TABLE nums(val int);
CREATE TABLE
my_db=# INSERT INTO nums VALUES(1);
INSERT 0 1
my_db=# INSERT INTO nums VALUES(2);
INSERT 0 1
my_db=# INSERT INTO nums VALUES(3);
INSERT 0 1

Now we have a database named my_db with a table named nums with three values.

It's time to create our backup on pg-backup server. First edit /etc/pgbackrest.conf on pg-backup as below:

[my_cluster]
pg1-host=pg-master
pg1-host-user=postgres
pg1-path=/var/lib/postgresql/12/main                                                                                                               

[global]                                                                                                                                  
process-max=2
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=1
repo1-host-user=postgres
start-fast=y

Then run this command to create a backup stanza on pg-backup server:

sudo -u postgres pgbackrest --stanza=my_cluster --log-level-console=info stanza-create

Then, run this command on both pg-master and pg-backup to check everything is okay:

sudo -u postgres pgbackrest --stanza=my_cluster --log-level-console=info check

If everything works good, it's time to perform our first backup on pg-backup server:

sudo -u postgres pgbackrest --stanza=my_cluster --log-level-console=info backup

By default, pgBackRest works by copying new archive files to backup server but if we let it be like that, the hard disk will be full very soon. So we need to create new backups based on some routines and pgBackRest will remove old backups for us based on retention policies that we have defined above. To do this, we will use Cronjob.

On pg-backup server:

sudo -u postgres
crontab -e

And add these lines at the end of the file and press enter to create a new line. Then save and exit.

30 06  *   *   0     pgbackrest --type=full --stanza=my_cluster backup
30 06  *   *   1-6   pgbackrest --type=diff --stanza=my_cluster backup

You can read more about full backup, diff backup and retention policies in pgBackRest documentation.

Now that our backup server has been configured correctly, it's time to configure our replication server. Since we want to have a streaming replication, we should first create a replication user on pg-master:

sudo -u postgres psql
CREATE USER replicator PASSWORD '123123' REPLICATION";

On pg-master, open /etc/postgresql/12/main/pg_hba.conf and add this line and change pg_replicate_ip to the ip of pg-replica server:

host    replication     replicator      {pg_replicate_ip}/32       md5

And then restart postgresql

systemctl restart postgresql

Now, on pg-replica open the file /etc/pgbackrest.conf and change the content with these lines:

[my_cluster]
pg1-path=/var/lib/postgresql/12/main
recovery-option=primary_conninfo=host=pg-master port=5432 user=replicator password=123123 application_name=replica1

[global]
repo1-host=pg-backup
repo1-host-user=postgres

Don't worry if you don't know what application_name is. We will talk about it later.
On pg-replica, run these commands to fetch master database data:

systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=my_cluster --delta --type=standby --log-level-console=info restore
systemctl start postgresql

To check that everything works correctly, on pg-replica go to psql and check that my_db database exists:

sudo -u postgres psql -d my_db
SELCET * FROM nums;

You should see these values as result:

 val 
-----
   1
   2
   3
(3 rows)

Now, we add some data on pg-master to check changes will be reflected on replica:

sudo -u postgres psql -d my_db
INSERT INTO nums VALUES (4);

On, pg-replica:

SELECT * FROM nums;

Now you should see these values as result:

 val 
-----
   1
   2
   3
   4
(3 rows)

If you don't see 4 values, look at the pg-replica's log file to find out the problem:

tail /var/log/postgresql/postgresql-12-main.log

As you saw earlier, we have set application_name=replica1 in our connection info, but what is that? By default, Postgresql will use asynchronous replication, meaning that, replication will be done with a lag (usually just a few milliseconds) but if you want to have synchronous replication (meaning that commits will be blocked until replication is done) then you can set this application_name in pg-master's postgresql.conf.
If you want to do this, on pg-master, open the file /ect/postgresql/12/main/postgresql.conf and change this value:

synchronous_standby_names = 'replica1'

And then restart postgresql. Now the replication should be synchronous. To test it, on pg-master go to sql shell and add a new row:

sudo -u postgres psql -d my_db
INSERT INTO nums VALUES(5);

If it doesn't hang, that means everything is okay. Buy if you stop postgresql replica server, command will hang and client will be blocked.

On pg-replica:

systemctl stop postgresql

On pg-master:

INSERT INTO nums VALUES(6);

Now as you see, pg-master's psql client is hanged but as soon as you start pg-replica postgresql server, it will be okay.

systemctl start postgresql

Now it's time to test recovery process. For the test purpose, we truncate nums table and recover the data.
On pg-master, run the command below and copy the time value:

sudo -u postgres psql -Atc  "select current_timestamp"
2020-09-24 14:17:23.105194+03:30

Open psql and truncate nums table:

sudo -u postgres psql -d my_db
TRUNCATE nums;
SELECT * FROM nums;
 val 
-----
(0 rows)

Stop Postgresql servers on pg-master and pg-replica

systemctl stop postgresql

On pg-master run the command below:

sudo -u postgres pgbackrest --stanza=my_cluster --delta --type=time "--target=2020-09-24 14:17:23.105194+03:30" --target-action=promote restore

And then start postgresql server:

systemctl start postgresql

Check the nums table:

sudo -u postgres psql -d my_db

 val 
-----
   1
   2
   3
   4
   5
   6
(6 rows)

As you can see, we have restored our truncated table on pg-master. Now we need to restore it on pg-replica. First, go to pg-backup server and get a new backup:

sudo -u postgres pgbackrest --stanza=my_cluster --log-level-console=info backup

Then, on pg-replica server, restore the data:

sudo -u postgres pgbackrest --stanza=my_cluster --delta --type=standby restore
systemctl start postgresql

Now for testing that everything works correctly add a value to nums table in pg-master and check that it will reflect on pg-replica.

That's all.