Configure pgbackrest on Database Server – Local in PostgreSQL

June 28, 2021

Configure pgbackrest on Database Server – Local in PostgreSQL

pgBackRest is an open-source backup tool for PostgreSQL which offers easy configuration and reliable backups. pgBackRest is a reliable, easy-to-use backup and restore solution that can seamlessly scale up to the largest databases and workloads by utilizing algorithms optimized for database-specific requirements.

Some of the key features of pgBackRest are:

Parallel backup & restore
Local and remote operations
Full, incremental & differential backups
Backup rotation & archive expiration
Backup integrity
Page checksums
Resume backups
Streaming compression and checksums

In this blog, we will install pgbackrest tool on the same Server (Local server) where PostgreSQL Cluster is running.

Step 1. Verify PostgresQL repository: Use OS command yum repolist to confirm postgresql repository exist in Backup Server. Click here to configure postgresql repository if it is not configured. Packages for pgBackRest are available in the PGDG yum repositories.

[root@test-machine01 ~]# yum repolist
Loaded plugins: langpacks, ulninfo
repo id                                                              repo name                                                                                                          status
!mysql-connectors-community/x86_64                                   MySQL Connectors Community                                                                                            203
!mysql-tools-community/x86_64                                        MySQL Tools Community                                                                                                 129
!mysql80-community/x86_64                                            MySQL 8.0 Community Server                                                                                            265
!ol7_UEKR5/x86_64                                                    Latest Unbreakable Enterprise Kernel Release 5 for Oracle Linux 7Server (x86_64)                                      404
!ol7_UEKR6/x86_64                                                    Latest Unbreakable Enterprise Kernel Release 6 for Oracle Linux 7Server (x86_64)                                      323
!ol7_latest/x86_64                                                   Oracle Linux 7Server Latest (x86_64)                                                                               22,766
!pgdg-common/7Server/x86_64                                          PostgreSQL common RPMs for RHEL/CentOS 7Server - x86_64                                                               303
!pgdg10/7Server/x86_64                                               PostgreSQL 10 for RHEL/CentOS 7Server - x86_64                                                                        918
!pgdg11/7Server/x86_64                                               PostgreSQL 11 for RHEL/CentOS 7Server - x86_64                                                                      1,006
!pgdg12/7Server/x86_64                                               PostgreSQL 12 for RHEL/CentOS 7Server - x86_64                                                                        572
!pgdg13/7Server/x86_64                                               PostgreSQL 13 for RHEL/CentOS 7Server - x86_64                                                                        298
!pgdg95/7Server/x86_64                                               PostgreSQL 9.5 for RHEL/CentOS 7Server - x86_64                                                                       763
!pgdg96/7Server/x86_64                                               PostgreSQL 9.6 for RHEL/CentOS 7Server - x86_64                                                                       889
repolist: 28,839
[root@test-machine01 ~]#

Step 2. Install pgbackrest: Use OS command yum install pgbackrest to install pgbackrest package on Local Server.

[root@test-machine01 ~]# yum install pgbackrest
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pgbackrest.x86_64 0:2.34-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                       Arch                                      Version                                         Repository                                      Size
==============================================================================================================================================================================================
Installing:
 pgbackrest                                    x86_64                                    2.34-1.rhel7                                    pgdg-common                                    320 k

Transaction Summary
==============================================================================================================================================================================================
Install  1 Package

Total download size: 320 k
Installed size: 855 k
Is this ok [y/d/N]: y
Downloading packages:
pgbackrest-2.34-1.rhel7.x86_64.rpm                                                                                                                                     | 320 kB  00:00:03
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pgbackrest-2.34-1.rhel7.x86_64                                                                                                                                             1/1
  Verifying  : pgbackrest-2.34-1.rhel7.x86_64                                                                                                                                             1/1

Installed:
  pgbackrest.x86_64 0:2.34-1.rhel7

Complete!
[root@test-machine01 ~]#

Step 3: Check pgbackrest Installed Correctly: Use some basic commands like pgbackrest version & pgbackrest help to verify after install pgbackrest.

[root@test-machine01 ~]#
[root@test-machine01 ~]# pgbackrest version
pgBackRest 2.34
[root@test-machine01 ~]#


[root@test-machine01 ~]# pgbackrest help
pgBackRest 2.34 - General help

Usage:
    pgbackrest [options] [command]

Commands:
    archive-get     Get a WAL segment from the archive.
    archive-push    Push a WAL segment to the archive.
    backup          Backup a database cluster.
Use 'pgbackrest help [command]' for more information.
[root@test-machine01 ~]#

Step 4: Configure pgBackRest to backup Local Database Cluster: By default, the configuration file is /etc/pgbackrest.conf. The global section allows specifying the repository to stores the backups and WAL segments archives. The prod_backup section is called a stanza. A stanza is a configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc.

repo1-path=/u01/pg_backup: Path where all backups and WAL archive will Store.
log-level-console=info: Level for console logging, Show Log info, warnings, and errors.
log-level-file=debug: Level for file logging, Show Log debug, detail, info, warnings, and errors.
start-fast=y: To force a checkpoint to start the backup quickly. Otherwise, the backup will start after the next regular checkpoint.
repo1-retention-full: Options define how long backups will be retained. The expiration only occurs when the count of complete backups exceeds the allowed retention.

[root@test-machine01 ~]#  cp /etc/pgbackrest.conf /etc/pgbackrest_bkp.conf
[root@test-machine01 ~]# vi /etc/pgbackrest.conf
[global]
repo1-path=/u01/pg_backup
log-level-console=info
log-level-file=debug
start-fast=y

[prod_backup]
pg1-path=/var/lib/pgsql/13/data
repo1-retention-full=2
:wq!

Step 5 : Configure archiving in the postgresql.conf file: pgBackRest provides the archive-push command to push a WAL segment to the archive. Please note stanza name in Step 4 is the same we used in archive_command.

[root@test-machine01 ~]#
[root@test-machine01 ~]# su - postgres
Last login: Sun Jun 27 10:59:10 +03 2021 on pts/0
-bash-4.2$ psql
psql (13.1)
Type "help" for help.

postgres=#
postgres=# alter system set archive_mode = on;
ALTER SYSTEM
postgres=# alter system set archive_command = 'pgbackrest --stanza=prod_backup archive-push %p';
ALTER SYSTEM
postgres=#

[root@test-machine01 ~]#
[root@test-machine01 ~]# systemctl restart postgresql-13.service
[root@test-machine01 ~]#

postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('archive_mode','archive_command');
      name       |                    setting                    | unit
-----------------+-----------------------------------------------+------
 archive_command | pgbackrest --stanza=prod_backup archive-push %p |
 archive_mode    | on                                            |
(2 rows)

postgres=#

Step 6: Create the stanza and Check the configuration: Login to postgres user. The stanza-create command must be run on the host where the repository is located to initialize the stanza. It is recommended that the check command be run after stanza-create to ensure archiving and backups are properly configured.

-bash-4.2$
-bash-4.2$ pgbackrest --stanza=prod_backup stanza-create
2021-06-27 11:51:31.571 P00   INFO: stanza-create command begin 2.34: --exec-id=109077-20ae97ed --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --repo1-path=/u01/pg_backup --stanza=prod_backup
2021-06-27 11:51:32.186 P00   INFO: stanza-create for stanza 'prod_backup' on repo1
2021-06-27 11:51:32.188 P00   INFO: stanza 'prod_backup'  on repo1 and is valid
2021-06-27 11:51:32.188 P00   INFO: stanza-create command end: completed successfully (618ms)
-bash-4.2$
-bash-4.2$
-bash-4.2$ pgbackrest --stanza=prod_backup check
2021-06-27 11:52:08.968 P00   INFO: check command begin 2.34: --exec-id=109121-29d5e929 --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --repo1-path=/u01/pg_backup --stanza=prod_backup
2021-06-27 11:52:09.583 P00   INFO: check repo1 configuration (primary)
2021-06-27 11:52:10.393 P00   INFO: check repo1 archive for WAL (primary)
2021-06-27 11:52:10.596 P00   INFO: WAL segment 000000010000000000000009 successfully archived to '/u01/pg_backup/archive/prod_backup/13-1/0000000100000000/000000010000000000000009-959be8a78a5273f3cccc2995899e712a7931b311.gz' on repo1
2021-06-27 11:52:10.597 P00   INFO: check command end: completed successfully (1629ms)
-bash-4.2$
-bash-4.2$

Reference: pgBackRest: https://pgbackrest.org/

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

BACKUP, BACKUP AND RECOVERY, PGBACKREST, POSTGRESQL

Configure pgbackrest on Database Server – Local in PostgreSQL

Related Articles