In one of our previous posts—pgBackRest: A Great Backup Solution and a Wonderful Year of Growth—we discussed the installation of pgBackRest and the backup, quick restore command. It would be ideal to have a restoration activity scheduled as per business needs so that backups are validated and provide critical metrics on recovery time. In this blog post, we will check out some restoration scenarios concerning the pgBackRest backup tool.
Scenarios
- Restoring the backup on a different host
- Restoring a single database from the Postgres cluster
- Restoring the backup to a specific point in time
- Restoring the backup on a different host to start as a streaming standby
- Restoring only the delta changes
To proceed with testing the above-mentioned restoration scenarios, we need the following:
- pgBackRest of the same version installed on source and target machines
- Repository information i.e, the place where we store our backups could be S3, GCS, Azure buckets, or dedicated storage.
- The target server should have access to the repository host via TLS or SSH
For testing the above-mentioned scenarios, I have a PostgreSQL primary server, two replica servers, a repository server, and a spare server.
My repository information: a FULL and INCR backup is available in the repository.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | postgres@pgBackRest:~$ pgBackRest --config=/etc/pgBackRest/pgBackRest.conf --log-level-console=detail --stanza=demo info stanza: demo status: ok cipher: none db (current) wal archive min/max (11): 00000001000000000000001E/00000001000000000000002A full backup: 20221104-102722F timestamp start/stop: 2022-11-04 10:27:22 / 2022-11-04 10:27:32 wal start/stop: 000000010000000000000028 / 000000010000000000000028 database size: 37.6MB, database backup size: 37.6MB repo1: backup set size: 4.5MB, backup size: 4.5MB incr backup: 20221104-102722F_20221104-102807I timestamp start/stop: 2022-11-04 10:28:07 / 2022-11-04 10:28:12 wal start/stop: 00000001000000000000002A / 00000001000000000000002A database size: 37.6MB, database backup size: 8.3KB repo1: backup set size: 4.5MB, backup size: 402B backup reference list: 20221104-102722F |
Restoring the backup on a different host
In this scenario, we will test the backup by restoring it to the spare server. My spare server’s pgBackRest conf has the information about the repository host, repository path, repository host user, and required PostgreSQL version installed and access to the repository.
pgBackRest can be used entirely by command line parameters but having a configuration file has more convenience. Below is my spare server pgBackRest configuration file.
1 2 3 4 5 6 7 8 | postgres@spareserver:~$ cat /etc/pgbackrest/pgbackrest.conf [global] # repo details repo1-path=/pgrdbackups # repository storage derails repo1-host=192.168.64.18 # repository host address repo1-host-user=postgres # user details to access the repo [demo] pg1-path=/var/lib/postgresql/11/main # data_dir path to restore the backup |
I will go ahead and restore the backup available in my repository onto the spare server. By default, it restores the latest available backup set available in the repository.
1 2 3 4 5 6 7 8 9 10 11 | postgres@spareserver:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo restore 2022-11-04 10:39:42.175 P00 INFO: restore command begin 2.41: --config=/etc/pgBackRest/pgBackRest.conf --exec-id=16557-1a840ffa --log-level-console=info --pg1-path=/var/lib/postgresql/11/main --repo1-host=192.168.64.18 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=demo 2022-11-04 10:39:42.959 P00 INFO: repo1: restore backup set 20221104-102722F_20221104-102807I, recovery will start at 2022-11-04 10:28:07 2022-11-04 10:39:50.104 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf 2022-11-04 10:39:50.116 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2022-11-04 10:39:50.124 P00 INFO: restore size = 37.6MB, file total = 1535 2022-11-04 10:39:50.125 P00 INFO: restore command end: completed successfully (7954ms) <change archive_commad on the spare server> and start the database pg_ctl -D <data_dir> start |
We need to make sure the archive_command is altered to a different path or disabled to prevent Postgres from writing archives to the existing repo upon starting on the spare server.
Restoring a single database from the Postgres cluster
In this scenario, we will restore only a single database on the spare server. I have below mentioned databases on my primary database.
Primary server:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | postgres=# l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+---------+----------------------- mydb | postgres | UTF8 | C.UTF-8 | C.UTF-8 | percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 | postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres postgres=# l+ percona List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ---------+----------+----------+---------+---------+-------------------+--------+------------+------------- percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 157 MB | pg_default |
We will restore only the Percona database to my spare server.
1 2 3 4 5 6 7 | postgres@spareserver:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo --db-include=percona restore 2022-11-04 10:58:05.869 P00 INFO: restore command begin 2.41: --config=/etc/pgBackRest/pgBackRest.conf --db-include=percona --exec-id=16647-4f3aa57d --log-level-console=info --pg1-path=/var/lib/postgresql/11/main --repo1-host=192.168.64.18 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=demo 2022-11-04 10:58:07.088 P00 INFO: repo1: restore backup set 20221104-105706F, recovery will start at 2022-11-04 10:57:06 2022-11-04 10:58:15.791 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf 2022-11-04 10:58:15.805 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2022-11-04 10:58:15.816 P00 INFO: restore size = 187.4MB, file total = 1548 2022-11-04 10:58:15.817 P00 INFO: restore command end: completed successfully |
Start the Postgres cluster on the spare server and check the Percona database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | postgres@spareserver:~$ pg_ctlcluster 11 main start postgres@spareserver:~$ psql postgres psql (14.5 (Ubuntu 2:14.5-3.jammy), server 11.17 (Ubuntu 2:11.17-3.jammy)) Type "help" for help. postgres=# l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+---------+----------------------- mydb | postgres | UTF8 | C.UTF-8 | C.UTF-8 | percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 | postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres postgres=# l+ percona List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ---------+----------+----------+---------+---------+-------------------+--------+------------+------------- percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 157 MB | pg_default | |
After restoring the specific database you will still get to see the other user databases as well. However, when a user will try to connect to them (i.e. apart from the database specified with –db-include option) the connection will not happen and the user will get a FATAL error like this: “FATAL: relation mapping file “base/xx/pg_filenode.map” contains invalid data”.
Restoring the backup to a specific point in time
Point-in-time recovery is possible with the pgBackRest, Consider a table/database has been dropped and needs to get restored. In this situation, we need the timestamp of the drop event, pgBackRest backup, and archives.
On my primary node, I have a table named pitr in the Percona database. This table has dropped at timestamp 2022-11-04 14:24:32.231309+05:30.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | percona=# dt+ pitr List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+------+-------+----------+-------------+------------+------------- public | pitr | table | postgres | permanent | 8192 bytes | (1 row) percona=# select now();drop table pitr; now ---------------------------------- 2022-11-04 14:24:32.231309+05:30 (1 row) DROP TABLE percona=# select pg_switch_wal(); pg_switch_wal --------------- 0/7B0162A0 (1 row) |
By using the pgBackRest restore option with the recovery type command we can achieve Point-In-Time-Recovery, by default this type restores the archive to the end of the stream, In this scenario, we will specify the exact time stamp to restore the table, xid and lsn can also be provided to the type command.
1 2 3 4 5 6 7 | pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo --db-include=percona --type=time "--target=2022-11-04 14:24:31" restore 2022-11-04 14:35:35.389 P00 INFO: restore command begin 2.41: --config=/etc/pgBackRest/pgBackRest.conf --db-include=percona --exec-id=17804-ca30e9a3 --log-level-console=info --pg1-path=/var/lib/postgresql/11/main --repo1-host=192.168.64.18 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=demo --target="2022-11-04 14:24:31" --type=time 2022-11-04 14:35:36.279 P00 INFO: repo1: restore backup set 20221104-105706F, recovery will start at 2022-11-04 10:57:06 2022-11-04 14:35:44.054 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf 2022-11-04 14:35:44.069 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2022-11-04 14:35:44.084 P00 INFO: restore size = 187.4MB, file total = 1548 2022-11-04 14:35:44.086 P00 INFO: restore command end: completed successfully |
Start the Postgres cluster and check the pitr table.
1 2 3 4 5 6 | postgres@spareserver:~$ pg_ctlcluster 11 main start percona=# dt pitr List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | pitr | table | postgres |
Restoring the backup on a different host to start as a streaming standby
This approach will be useful in case we need to rebuild a crashed standby node or add a new standby to the existing Postgres cluster.
Primary server:
Two replica servers are connected to the primary server.
1 2 3 4 5 6 | postgres=# select usename,client_addr,sync_state,state from pg_catalog.pg_stat_replication ; usename | client_addr | sync_state | state ---------+---------------+------------+----------- repl | 192.168.64.16 | async | streaming repl | 192.168.64.17 | async | streaming |
Let’s go ahead and add another replica using the pgBackRest.
1 2 3 4 5 6 7 | postgres@spareserver:~/11/main$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo --type=standby --recovery-option=primary_conninfo='user=repl password=<redacted> host=192.168.64.10 application_name=spare' restore 2022-11-04 15:02:49.142 P00 INFO: restore command begin 2.41: --config=/etc/pgBackRest/pgBackRest.conf --exec-id=18057-b13cb8f4 --log-level-console=info --pg1-path=/var/lib/postgresql/11/main --recovery-option="primary_conninfo=user=repl password=test host=192.168.64.10 application_name=spare" --repo1-host=192.168.64.18 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=demo --type=standby 2022-11-04 15:02:49.642 P00 INFO: repo1: restore backup set 20221104-105706F, recovery will start at 2022-11-04 10:57:06 2022-11-04 15:02:57.767 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf 2022-11-04 15:02:57.778 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2022-11-04 15:02:57.791 P00 INFO: restore size = 187.4MB, file total = 1548 2022-11-04 15:02:57.792 P00 INFO: restore command end: completed successfully |
The replica server is up and running, Check the pg_stat_replication on the primary node.
1 2 3 4 5 6 7 8 | postgres=# select usename,client_addr,sync_state,state from pg_catalog.pg_stat_replication ; usename | client_addr | sync_state | state ---------+---------------+------------+----------- repl | 192.168.64.16 | async | streaming repl | 192.168.64.17 | async | streaming repl | 192.168.64.19 | async | streaming (3 rows) |
Restoring only the delta changes
The delta option allows pgBackRest to automatically determine which files in the database cluster directory can be preserved and which ones need to be restored from the backup, Consider the situation as we already have a backup restored on a spare server and now we will perform the delta restore on the spare server again to get the delta data that has been backed up since the last restore.
1 2 3 4 5 6 7 8 | postgres@spareserver:~$ pg_ctlcluster 11 main stop postgres@spareserver:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo --delta restore 2022-11-04 15:36:57.101 P00 INFO: restore command begin 2.41: --config=/etc/pgBackRest/pgBackRest.conf --delta --exec-id=18175-ac9d25dc --log-level-console=info --pg1-path=/var/lib/postgresql/11/main --repo1-host=192.168.64.18 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=demo 2022-11-04 15:36:58.130 P00 INFO: repo1: restore backup set 20221104-153559F, recovery will start at 2022-11-04 15:35:59 2022-11-04 15:36:58.136 P00 INFO: remove invalid files/links/paths from '/var/lib/postgresql/11/main' 2022-11-04 15:37:04.155 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf 2022-11-04 15:37:04.168 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2022-11-04 15:37:04.181 P00 INFO: restore size = 202.2MB, file total = 2136 |
Overall, pgBackRest is an incredibly popular open-source database backup tool available for PostgreSQL. We also encourage you to try our product Percona Distribution for PostgreSQL, as pgBackRest tool is part of our distribution for backups.
Hi @Bhargav, Congratulations on your first blog. I have gone through and it has much data to play-around with pgbackrest utility. It would be helpful for DBAs on routine backup operations. Thank you for your efforts.
It is a common use case in enterprises to refresh a pre-production system from production on a regularly basis, e.g. daily. The backups done so far on the pre-production system should also be kept to be able to do a point-in-time restore just before such a refresh if needed.
I tried to implement this by doing a “Restoring the backup on a different host” on a regular basis. I always got ““ERROR: [045]: WAL file already exists in the repo1 archive with a different checksum” from “archive-push” afterwards when I used the same repo1.
I had to recreate the pgbackrest stanza with a different name to get a new empty repo1 to work around this error. This also means that I have to reconfigure my pgbackrest settings to use the old stanza before a restore. Not that good.
Any idea on this?
Remove old archives from the target node.
Anyone can explain what is delta in pgBackRest and give me use case to use delta option ? , i still don’t understand delta option although i have read some documents
Thank you so much
Delta means changes from the previous backup.
The –delta option allows you to start the restore with a full database directory (failed or user-broken or whatever) and use a file list with checksums to remove files which aren’t in the backup and restore those which are changed. This saves you restoring files which are not changed. It is analogous to an rsync with –delete to recover a broken directory structure – the end result is the same but time and data transfer is saved.