PGBackRest Backup Server(Dedicated Repository Host)

---

With a dedicated pgbackrest backup server, backup management can be managed
from a single point. Since the backup and archived WAL files will be on another server,
it will be not be affected by issues on the database server.
Planning should be done by considering database sizes, network speed, processor
capacity, time periods when databases are busy.

Kurulum

ROLE	HOSTNAME	IP	DB
PGBackRest Backup Server	pgbr	192.168.56.60	–
Database Server	srvr1	192.168.56.61	mxdb
Database Server	srvr2	192.168.56.62	dmdb

In all 3 servers:
The following lines are entered in the /etc/hosts file.

192.168.56.60       pgbr
192.168.56.61       srv1
192.168.56.62       srv2

• The following lines are entered in the /etc/sudoers file.

postgres          ALL=(ALL)            NOPASSWD:ALL

Note: the sudo privilege may not comply with every company’s IT security policy. It can
be undone after installations.

STEP 1

In the pgbr machine

root@pgbr:~# apt install postgresql-client libxml2
root@pgbr:~# useradd postgres
root@pgbr:~# mkdir /home/postgres
root@pgbr:~# chown -R postgres:postgres /home/postgres
root@pgbr:~# su - postgres
postgres@pgbr:~$ pwd
/home/postgres
postgres@pgbr:~$ cp /etc/skel/.* .

pgbackrest is installed in this server by following the pgbackrest installation steps
described in the previous pages. Since postgresql server is not installed on the pgbr
machine, the postgresql client package is also installed. Since we will perform
the backup operations with the postgres user, a postgres user is created on the pgbr
machine.

STEP 2

# With postgres user on all 3 machines
# All default entries are selected. Nothing is entered.
ssh-keygen -t rsa
# In the pgbr machine
ssh-copy-id srv1
ssh-copy-id srv2
# In machine srv1
ssh-copy-id pgbr
# In the srv2 machine

Password less ssh using postgres user between pgbr and database servers is
established.
After the access settings are made, the connection should be tested one by one.

STEP 3

# In srv1 and srv2 machines
sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

# In the pgbr machine
sudo scp /usr/bin/pgbackrest srv1:/usr/bin/
sudo scp /usr/bin/pgbackrest srv2:/usr/bin/

It should be checked by running pgbackrest version with postgres user on
srv1 and srv2 database servers.
Note: Dedicated pgbackrest server and database servers must have the same version
of pgbackrest installed.

STEP 4 (add disk onpgbr machine)

postgres@pgbr:~$ sudo pvcreate /dev/sdb
postgres@pgbr:~$ sudo pvdisplay
postgres@pgbr:~$ sudo vgcreate ra /dev/sdb

# To use all disk space
postgres@pgbr:~$ sudo lvcreate -l 100%FREE -n RA-LV ra
postgres@pgbr:~$ sudo lvdisplay /dev/ra/RA-LV
postgres@pgbr:~$ sudo mkfs.xfs /dev/ra/RA-LV
postgres@pgbr:~$ sudo mount /dev/ra/RA-LV /RA
postgres@pgbr:~$ df -h # Check the disk.

# In order for the disk to be mounted to /RA automatically
after reboot

# The following line is added to the file.
sudo vim /etc/fstab
/dev/ra/RA-LV /RA xfs nofail,defaults 0 0
postgres@pgbr:~$ sudo umount /RA
postgres@pgbr:~$ sudo mount -a # should be tested
postgres@pgbr:~$ sudo mkdir -p /RA/pgbackrest
postgres@pgbr:~$ sudo chown -R postgres:postgres /RA/

In the pgbr machine, the operating system is located on the /dev/sda disk. A second
disk is installed for backups. The ID of the new disk is learned by checking with
fdisk -l. In the example made here, the 2nd disk is seen as /dev/sdb.

STEP 5 (pgbackrest settings in pgbr)

[global]
repo1-path=/RA/pgbackrest
repo1-retention-full=2
process-max=4
log-level-console=info
start-fast=y
[global:archive-push]
compress-level=3

[mx]
pg1-path=/pg/data/13.6
pg1-host=srv1
pg1-host-user=postgres

[dm]
pg1-path=/var/lib/postgresql/13/main
pg1-host=srv2
pg1-host-user=postgres

As explained in the previous sections, in srv1, postgresql database server was installed
in the $PGDATA=/pg/data/13.6 directory from the source codes.
In the srv2 database server, ready packages were installed with apt. That’s why
$PGDATA=/var/lib/postgresql/13/main is installed by default.

STEP 6 (pgbackrest setting in srv1)

vim /etc/pgbackrest/pgbackrest.conf #add the following lines
[mx]
pg1-path=/pg/data/13.6
pg1-port=5434
pg1-socket-path=/tmp

[global]
repo1-host=pgbr
repo1-host-user=postgres
process-max=4
log-level-console=info
log-level-file=debug
[global:archive-push]
compress-level=3

STEP 7 (pgbackrest setting insrv2)

vim /etc/pgbackrest/pgbackrest.conf  #add the following lines
[dm]
pg1-path=/var/lib/postgresql/13/main
pg1-port=5432
[global]
repo1-host=pgbr
repo1-host-user=postgres
process-max=4
log-level-console=info
log-level-file=debug
[global:archive-push]
compress-level=3

STEP 8 ( In srv1 ve srv2 machines)

# IN srv1
postgres@srv1:~$ psql
postgres# alter system set archive_mode = 'on' ;
postgres# alter system set archive_command = 'pgbackrest
--stanza=mx archive-push %p'
postgres# select pg_reload_conf();
postgres@srv1:~$ pgbackrest stop
postgres@srv1:~$ pgbackrest start

# IN srv2’de
postgres@srv2:~$ psql
postgres# alter system set archive_mode = 'on' ;
alter system set archive_command = 'pgbackrest --stanza=dm archive-push %p'
postgres# select pg_reload_conf();
postgres@srv2:~$ pgbackrest stop
postgres@srv2:~$ pgbackrest start

STEP 9 (In pgbr machine)

postgres@pgbr:~$ pgbackrest --stanza=mx stanza-create
postgres@pgbr:~$ pgbackrest --stanza=dm stanza-create
postgres@pgbr:~$ pgbackrest --stanza=mx check
postgres@pgbr:~$ pgbackrest --stanza=dm check
INFO: check command begin 2.40: --exec-id=5984-90166d79 --loglevel-
console=info --pg1-host=srv2 --pg1-host-user=postgres
--pg1-path=/var/lib/postgresql/13/main --repo1-path=/RA/
pgbackrest --stanza=dm
P00 INFO: check repo1 configuration (primary)
P00 INFO: check repo1 archive for WAL (primary)
P00 INFO: WAL segment 000000010000000000000007
successfully archived to ‘/RA/pgbackrest/archive/dm/13-
1/0000000100000000/000000010000000000000007-23bcdb5a5765d40ca93
d09dcadbed85706a8a7d4.gz’ on repo1
P00 INFO: check command end: completed successfully (1271ms)

Archived WAL files are seen in the /RA/pgbackrest/archive/dm/13-1 and
/RA/pgbackrest/archive/mx/13-1 directories.

Backup (In pgbr machine)

pgbackrest --stanza=dm backup
pgbackrest --stanza=mx backup
pgbackrest --stanza=dm info
pgbackrest --stanza=mx info

Backup is taken from the backup (pgbr) server.
The restore operation is performed on the machine where the database will be
restored.
For example, srv2’s database is restored in srv2 server.

Restore (Restore on machine srv1 or srv2)

postgres@srv2:~$ sudo systemctl stop postgresql.service
postgres@srv2:~$ pgbackrest --stanza=demo --delta restore
postgres@srv2:~$ sudo systemctl start postgresql.service

Note: “pgbackrest stop“. In some cases, it may be necessary to stop pgbackrest.
For example, it is not recommended to take a backup from the problematic primary
machine during the failover process.
A test environment can be prepared for the software team by installing in the another
machine with the same configurations and quickly taking the databases to this
machine. Thus, the live environment is not affected, as well as backup copying etc. No
time is wasted with transactions. pgbackrest can also be used for standby installation.
See chapter 12 and pgbackrest.org for details.

Monitoring

postgres@srv2:~$ psql
postgres=# \i /pgbackrest-release-2.40/doc/example/pgsqlpgbackrest-
info.sql
postgres=# \i /pgbackrest-release-2.40/doc/example/pgsqlpgbackrest-
query.sql
name | last_successful_backup | last_archived_wal
------+------------------------+--------------------------
“dm” | 06:22:19+03 | 00000001000000000000000B
“mx” | 14:53:15+03 | 0000000400000000000000B4

Backups can be monitored with ‘check’ and ‘info’ as well as querying with
the SQLs that come with pgbackrest. If this query is empty, an alarm can be triggered
through an e-mail.