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.
To proceed with testing the above-mentioned restoration scenarios, we need the following:
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 |
postgres@pgBackRest:~$ pgBackRest --config=/etc/pgBackRest/pgBackRest.conf --log-level-console=detail --stanza=demo info<br>stanza: demo<br> status: ok<br> cipher: none<br><br> db (current)<br> wal archive min/max (11): 00000001000000000000001E/00000001000000000000002A<br><br> full backup: 20221104-102722F<br> timestamp start/stop: 2022-11-04 10:27:22 / 2022-11-04 10:27:32<br> wal start/stop: 000000010000000000000028 / 000000010000000000000028<br> database size: 37.6MB, database backup size: 37.6MB<br> repo1: backup set size: 4.5MB, backup size: 4.5MB<br><br> incr backup: 20221104-102722F_20221104-102807I<br> timestamp start/stop: 2022-11-04 10:28:07 / 2022-11-04 10:28:12<br> wal start/stop: 00000001000000000000002A / 00000001000000000000002A<br> database size: 37.6MB, database backup size: 8.3KB<br> repo1: backup set size: 4.5MB, backup size: 402B<br> backup reference list: 20221104-102722F<br> |
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 |
postgres@spareserver:~$ cat /etc/pgbackrest/pgbackrest.conf<br>[global]<br># repo details<br>repo1-path=/pgrdbackups # repository storage derails<br>repo1-host=192.168.64.18 # repository host address <br>repo1-host-user=postgres # user details to access the repo <br>[demo] <br>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 |
postgres@spareserver:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo restore<br><br>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<br>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<br>2022-11-04 10:39:50.104 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf<br>2022-11-04 10:39:50.116 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)<br>2022-11-04 10:39:50.124 P00 INFO: restore size = 37.6MB, file total = 1535<br>2022-11-04 10:39:50.125 P00 INFO: restore command end: completed successfully (7954ms)<br><br><change archive_commad on the spare server> and start the database <br>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.
In this scenario, we will restore only a single database on the spare server. I have below mentioned databases on my primary database.
|
1 |
postgres=# l<br> List of databases<br> Name | Owner | Encoding | Collate | Ctype | Access privileges<br>-----------+----------+----------+---------+---------+-----------------------<br> mydb | postgres | UTF8 | C.UTF-8 | C.UTF-8 |<br> percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 |<br> postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |<br> template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +<br> | | | | | postgres=CTc/postgres<br> template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +<br> | | | | | postgres=CTc/postgres<br><br>postgres=# l+ percona<br> List of databases<br> Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description<br>---------+----------+----------+---------+---------+-------------------+--------+------------+-------------<br> percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 157 MB | pg_default <br> |
We will restore only the Percona database to my spare server.
|
1 |
postgres@spareserver:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo --db-include=percona restore<br>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<br>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<br>2022-11-04 10:58:15.791 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf<br>2022-11-04 10:58:15.805 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)<br>2022-11-04 10:58:15.816 P00 INFO: restore size = 187.4MB, file total = 1548<br>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 |
postgres@spareserver:~$ pg_ctlcluster 11 main start<br><br>postgres@spareserver:~$ psql postgres<br>psql (14.5 (Ubuntu 2:14.5-3.jammy), server 11.17 (Ubuntu 2:11.17-3.jammy))<br>Type "help" for help.<br><br>postgres=# l<br> List of databases<br> Name | Owner | Encoding | Collate | Ctype | Access privileges<br>-----------+----------+----------+---------+---------+-----------------------<br> mydb | postgres | UTF8 | C.UTF-8 | C.UTF-8 |<br> percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 |<br> postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |<br> template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +<br> | | | | | postgres=CTc/postgres<br> template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +<br> | | | | | postgres=CTc/postgres<br><br>postgres=# l+ percona<br> List of databases<br> Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description<br>---------+----------+----------+---------+---------+-------------------+--------+------------+-------------<br> 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”.
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 |
percona=# dt+ pitr<br> List of relations<br> Schema | Name | Type | Owner | Persistence | Size | Description<br>--------+------+-------+----------+-------------+------------+-------------<br> public | pitr | table | postgres | permanent | 8192 bytes |<br>(1 row)<br><br>percona=# select now();drop table pitr;<br> now<br>----------------------------------<br> 2022-11-04 14:24:32.231309+05:30<br>(1 row)<br><br>DROP TABLE<br>percona=# select pg_switch_wal();<br> pg_switch_wal<br>---------------<br> 0/7B0162A0<br>(1 row)<br> |
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 |
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<br>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<br>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<br>2022-11-04 14:35:44.054 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf<br>2022-11-04 14:35:44.069 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)<br>2022-11-04 14:35:44.084 P00 INFO: restore size = 187.4MB, file total = 1548<br>2022-11-04 14:35:44.086 P00 INFO: restore command end: completed successfully |
Start the Postgres cluster and check the pitr table.
|
1 |
postgres@spareserver:~$ pg_ctlcluster 11 main start<br>percona=# dt pitr<br> List of relations<br> Schema | Name | Type | Owner<br>--------+------+-------+----------<br> public | pitr | table | postgres |
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.
Two replica servers are connected to the primary server.
|
1 |
postgres=# select usename,client_addr,sync_state,state from pg_catalog.pg_stat_replication<br>;<br> usename | client_addr | sync_state | state<br>---------+---------------+------------+-----------<br> repl | 192.168.64.16 | async | streaming<br> repl | 192.168.64.17 | async | streaming |
Let’s go ahead and add another replica using the pgBackRest.
|
1 |
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<br>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<br>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<br>2022-11-04 15:02:57.767 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf<br>2022-11-04 15:02:57.778 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)<br>2022-11-04 15:02:57.791 P00 INFO: restore size = 187.4MB, file total = 1548<br>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 |
postgres=# select usename,client_addr,sync_state,state from pg_catalog.pg_stat_replication<br>;<br> usename | client_addr | sync_state | state<br>---------+---------------+------------+-----------<br> repl | 192.168.64.16 | async | streaming<br> repl | 192.168.64.17 | async | streaming<br> repl | 192.168.64.19 | async | streaming<br>(3 rows) |
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 |
postgres@spareserver:~$ pg_ctlcluster 11 main stop<br>postgres@spareserver:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo --delta restore<br>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<br>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<br>2022-11-04 15:36:58.136 P00 INFO: remove invalid files/links/paths from '/var/lib/postgresql/11/main'<br>2022-11-04 15:37:04.155 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf<br>2022-11-04 15:37:04.168 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)<br>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.
Resources
RELATED POSTS
