Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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
1 | sudo apt-get install pgbackrest |
Install PgBackRest on RedHat and its derivatives
1 | sudo yum install pgbackrest.x86_64 |
Change Parameters on postgresql.conf to Track WALs
1 2 | archive_mode = on 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'; |
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
1 2 3 4 5 6 7 8 9 10 11 12 | $ 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
1 2 3 4 5 6 7 | $ 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
1 2 3 4 5 6 7 8 9 10 11 | $ 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
1 2 3 4 5 6 7 8 9 10 11 | $ 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $ 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.
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 🙂
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).
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
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:
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.
how to install in windows server
It can be another article content.
How to take incremental and differential backups.
Make sure you have taken full backup first.
Then second time, when you run the the above command, change – -type = Incr (for incremental) or – -type = diff (for differential)
It also provides functionality of PITR too
By default pgBackRest will attempt to perform an incremental backup. However, an incremental backup must be based on a full backup and since no full backup existed pgBackRest ran a full backup instead.
Really brief- clear content Thx
You’re welcome