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. 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$