What is PgBackRest and PgBackRest Installation in PostgreSQL 12

Zekiye AYDEMİR

5 years ago

What is pgBackRest

pgBackRest is a backup/restore tool in postgresql.

PostgreSQL Backup Types

In Postgresql, we create backups in two ways: logical and physical.

Logical Backup (pg_dump, pg_dumpall)
Physical Backup (base backup)

Replication mechanism is not a baclıup method!

pgBackRest software is classified as “physical backup / restore”.

What can you do with pgBackRest?

You can create backups on the same server or on a different server.
You can create a backup from Replication. pgBackRest allows you to create backups on a backup server connected with master and replica. pgBackRest backs up data that is not available in Replica from the master. All remaining backup operations are performed from Replica. Thus, you do not create a backup cost on the master server.
You can perform all backup and restore operations in parallel.
pgBackRest compresses and sends data when creating backups. Thus, you do not overload the network traffic. This makes backup/restore faster.
pgBackRest maintains a catalog of backups;

Time Information
Size Information
Required segment files
Backup type

pgBackRest has 3 backup types.

PgBackRest Backup Types

Full backup: All data in the PostgreSQL Cluster is backed up.

Differential backup: Backups files that have changed from the last full backup.

Incremental backup: Backups the files that have changed from the last backup.

pgBackRest tracks wal files. It uses the archive_command parameter in Postgresql.conf. It deletes old backups and old wal files. Incremental backups related to full and diff backups are also automatically deleted.
pgBackRest stores the number of full and differential backups you specify.
pgBackRest supports PostgreSQL 12 with the latest version v2.18.

PgBackRest Installation

In this section, we will install PgBackRest and create backups with pgBackRest in the following ways.

Create PostgreSQL Cluster Backup with pgBackRest on the Same Server
Create PostgreSQL Cluster Backup with pgBackRest to Backup Server
Create PostgreSQL Cluster Backup From Replication with pgBackRest

First, according to the architecture you choose, we install pgBackrest on the PostgreSQL server and/or on all servers where the backup we create is stored:

Install PgBackRest on Debian and its derivatives

sudo apt-get install pgbackrest

1	sudo apt-get install pgbackrest

Install PgBackRest on RedHat and its derivatives

sudo yum install pgbackrest.x86_64

1	sudo yum install pgbackrest.x86_64

PgBackRest uses archive_mode and archive_command in postgresql.conf to track wal. We set these parameters on all database servers.

Change Parameters on postgresql.conf to Track WALs

archive_mode = on
archive_command = 'pgbackrest --stanza=12_data archive_push %p'

1 2	archive_mode = on archive_command = 'pgbackrest --stanza=12_data archive_push %p'

By connecting to PostgreSQL, you can do the same with the following commands;

ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=12_data archive_push %p';

1 2	ALTER SYSTEM SET archive_mode = on; ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=12_data archive_push %p';

Restart PostgreSQL to enable the change to the archive_mode parameter.

systemctl restart postgresql-12

1	systemctl restart postgresql-12

Create PostgreSQL Cluster Backup with pgBackRest on the Same Server

If you want to create a backup on the master server,

Configuration of pgBackRest

$ cat /etc/pgbackrest.conf
[global]
repo-path=/pg_backup/12/data/pgbackrest
backup-user=postgres
retention-full=3
retention-diff=2
process-max=2
log-path=/pg_backup/12/data/log/pgbackrest


[12_data]
pg1-path=/var/lib/pgsql/12/data/

$ cat /etc/pgbackrest.conf

[global]

repo-path=/pg_backup/12/data/pgbackrest

backup-user=postgres

retention-full=3

retention-diff=2

process-max=2

log-path=/pg_backup/12/data/log/pgbackrest

[12_data]

pg1-path=/var/lib/pgsql/12/data/

Create PostgreSQL Cluster Backup with pgBackRest to Backup Server

If you want to create a backup to backup server,

Configuration of pgBackRest on Master

$ cat /etc/pgbackrest.conf
[global]
repo1-host=Backup_Server_IP
repo1-host-user=postgres

[12_data]
pg1-path=/var/lib/pgsql/12/data/

$ cat /etc/pgbackrest.conf

[global]

repo1-host=Backup_Server_IP

repo1-host-user=postgres

[12_data]

pg1-path=/var/lib/pgsql/12/data/

Configuration of pgBackRest on Backup Server

$ cat /etc/pgbackrest.conf
[global]
repo1-path=/pg_backup/12/data/pgbackrest
repo1-retention-full=3
repo1-retention-diff=2
process-max=2

[12_data]
pg1-host=Master_Server_IP
pg1-path=/var/lib/pgsql/12/data/
pg1-user=postgres

$ cat /etc/pgbackrest.conf

[global]

repo1-path=/pg_backup/12/data/pgbackrest

repo1-retention-full=3

repo1-retention-diff=2

process-max=2

[12_data]

pg1-host=Master_Server_IP

pg1-path=/var/lib/pgsql/12/data/

pg1-user=postgres

Create PostgreSQL Cluster Backup From Replication with pgBackRest

In this scenario, we will create a backup from Replika to backup server.

Configurations of pgbackrest on Master and Replica

$ cat /etc/pgbackrest.conf
[global]
repo1-path=/pg_backup/12/data/pgbackrest
repo1-retention-full=3
repo1-retention-diff=2
process-max=2

[12_data]
pg1-host=Master_Server_IP
pg1-path=/var/lib/pgsql/12/data/
pg1-user=postgres

$ cat /etc/pgbackrest.conf

[global]

repo1-path=/pg_backup/12/data/pgbackrest

repo1-retention-full=3

repo1-retention-diff=2

process-max=2

[12_data]

pg1-host=Master_Server_IP

pg1-path=/var/lib/pgsql/12/data/

pg1-user=postgres

Configuration of pgBackRest on Backup Server

$ cat /etc/pgbackrest.conf
[global]
repo1-path=/pg_backup/12/data/pgbackrest
repo1-retention-full=3
repo1-retention-diff=2
process-max=2
backup-standby=y

[12_data]
pg1-host=Master_Server_IP
pg1-path=/var/lib/pgsql/12/data/
pg1-user=postgres
pg2-host=Replica_Server_IP
pg2-path=/var/lib/pgsql/12/data/
pg2-user=postgres

$ cat /etc/pgbackrest.conf

[global]

repo1-path=/pg_backup/12/data/pgbackrest

repo1-retention-full=3

repo1-retention-diff=2

process-max=2

backup-standby=y

[12_data]

pg1-host=Master_Server_IP

pg1-path=/var/lib/pgsql/12/data/

pg1-user=postgres

pg2-host=Replica_Server_IP

pg2-path=/var/lib/pgsql/12/data/

pg2-user=postgres

Backup PostgreSQL Cluster with PgBackRest

Check PgBackRest Configurations

We need to create a backup catalog first. Next, we need to check that the pgBackRest configurations are correct.

$ pgbackrest --stanza=12_data stanza-create --log-level-console=info
$ pgbackrest --stanza=12_data check --log-level-console=info

1 2	$ pgbackrest --stanza=12_data stanza-create --log-level-console=info $ pgbackrest --stanza=12_data check --log-level-console=info

We can create a backup 🙂

$ pgbackrest --stanza=12_data backup --log-level-console=info

1	$ pgbackrest --stanza=12_data backup --log-level-console=info

pgBackRest creates a full backup by default on the first backup. You can specify the backup type with –type(full, diff, or incr).

$ pgbackrest --stanza=12_data --type=backup_type_you_want_to_create backup

1	$ pgbackrest --stanza=12_data --type=backup_type_you_want_to_create backup

The sample command and output are as follows:

List the backup catalog and the backups

$ pgbackrest --stanza=12_data info

1	$ pgbackrest --stanza=12_data info

Restore PostgreSQL Cluster with PgBackRest

When the PostgreSQL service is closed on the server we want to restore, we run the following command:

$ pgbackrest --stanza=12_data restore

1	$ pgbackrest --stanza=12_data restore

pgBackRest returns the most recent backup by default.

In this article I talked about backing up with basic-level configurations.