In this blog, we will see How to perform a restore of a backup taken from pgBackRest. We will complete steps for the below two scenarios:
1. Restore in the same directory structure as of the backup database.
2. Restore in the different directory structures as of backup database.
PostgreSql Cluster Physical Structure: PostgreSQL Cluster data directory: /var/lib/pgsql/13/data, Created two non-default tablespaces myts01: /u01/pg1_tbls & myts02: /u01/pg2_tbls. Created two sample database sample1 & sample2 and loaded them with sample data along with configured WAL archiving to copy in pgBackRest Server.
postgres=# show data_directory;
data_directory
------------------------
/var/lib/pgsql/13/data
(1 row)
postgres=#
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+---------------
myts01 | postgres | /u01/pg1_tbls
myts02 | postgres | /u01/pg2_tbls
pg_default | postgres |
pg_global | postgres |
(4 rows)
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
dvdrental | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sample1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sample2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(6 rows)
postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('archive_mode','archive_command','archive_timeout');
name | setting | unit
-----------------+-------------------------------------------------+------
archive_command | pgbackrest --stanza=prod_backup archive-push %p |
archive_mode | on |
archive_timeout | 0 | s
(3 rows)
Create and Load test table and Switch WAL File: Let’s Create Table T_TEST and insert 1000 random records. Once the insert operation is done do force the WAL switch to make sure all changes are archived.
postgres=#
postgres=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=# create table t_Test (slno integer, ts timestamp);
CREATE TABLE
sample1=# insert into t_test values (generate_series(1,1000),now());
INSERT 0 1000
sample1=#
sample1=# \connect sample2
You are now connected to database "sample2" as user "postgres".
sample2=#
sample2=# create table t_Test (slno integer, ts timestamp);
CREATE TABLE
sample2=# insert into t_test values (generate_series(1,1000),now());
INSERT 0 1000
sample2=#
sample2=# select pg_switch_wal();
pg_switch_wal
---------------
0/1A000078
(1 row)
sample2=#
Perform Full Cluster backup: Perform full cluster-level backup using the pgbackrest backup command. Use command pgbackrest info to list down all available backups.
-bash-4.2$ pgbackrest --stanza=prod_backup backup --type=full
2021-07-06 16:03:59.788 P00 INFO: backup command begin 2.34: --exec-id=71575-09154242 --log-level-console=info --pg1-host=test-machine01 --pg1-host-user=postgres --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup --start-fast --stop-auto --type=full
2021-07-06 16:04:00.733 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-07-06 16:04:01.147 P00 INFO: backup start archive = 000000010000000000000019, lsn = 0/19000028
2021-07-06 16:04:07.793 P00 INFO: full backup size = 71.6MB
2021-07-06 16:04:07.793 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-07-06 16:04:07.996 P00 INFO: backup stop archive = 000000010000000000000019, lsn = 0/19000100
2021-07-06 16:04:08.002 P00 INFO: check archive for segment(s) 000000010000000000000019:000000010000000000000019
2021-07-06 16:04:08.329 P00 INFO: new backup label = 20210706-160402F
2021-07-06 16:04:08.371 P00 INFO: backup command end: completed successfully (8584ms)
2021-07-06 16:04:08.371 P00 INFO: expire command begin 2.34: --exec-id=71575-09154242 --log-level-console=info --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup
2021-07-06 16:04:08.373 P00 INFO: repo1: expire full backup set 20210630-142653F, 20210630-142653F_20210630-143155I, 20210630-142653F_20210630-143524D, 20210630-142653F_20210630-143536I
2021-07-06 16:04:08.375 P00 INFO: repo1: remove expired backup 20210630-142653F_20210630-143536I
2021-07-06 16:04:08.375 P00 INFO: repo1: remove expired backup 20210630-142653F_20210630-143524D
2021-07-06 16:04:08.376 P00 INFO: repo1: remove expired backup 20210630-142653F_20210630-143155I
2021-07-06 16:04:08.376 P00 INFO: repo1: remove expired backup 20210630-142653F
2021-07-06 16:04:08.438 P00 INFO: repo1: 13-1 remove archive, start = 00000001000000000000000F, stop = 000000010000000000000016
2021-07-06 16:04:08.439 P00 INFO: expire command end: completed successfully (68ms)
-bash-4.2$ pgbackrest info --stanza=prod_backup
stanza: prod_backup
status: ok
cipher: none
db (current)
wal archive min/max (13): 000000010000000000000017/00000001000000000000001A
full backup: 20210630-150037F
timestamp start/stop: 2021-06-30 15:00:37 / 2021-06-30 15:00:44
wal start/stop: 000000010000000000000017 / 000000010000000000000017
database size: 71.5MB, database backup size: 71.5MB
repo1: backup set size: 11.7MB, backup size: 11.7MB
full backup: 20210706-160402F
timestamp start/stop: 2021-07-06 16:04:02 / 2021-07-06 16:04:09
wal start/stop: 000000010000000000000019 / 000000010000000000000019
database size: 71.6MB, database backup size: 71.6MB
repo1: backup set size: 11.7MB, backup size: 11.7MB
-bash-4.2$
Stop PostgreSQL Cluster: Stop the cluster as we need to remove datafiles.
[root@test-machine01 ~]# systemctl stop postgresql-13
[root@test-machine01 ~]#
pgBackRest Restore Comamnd: pgBackRest restore command can be run in two different modes.
1. Normal restore mode: Need to remove all files from PG_DATA and Tablespace folder before we issue the restore command.
2. delta restores mode: delta option to automatically determine which files can be preserved and which ones need to be restored from the backup.
Please note pgbackrest restore command need to execute in PostgreSQL Database Server, not on the pgbackrest backup server. If you try to run on pgbackrest restore command on a backup server you will receive ERROR: [072]: restore command must be run on the PostgreSQL host.
Normal Restore Mode
-bash-4.2$ pgbackrest --stanza=prod_backup restore
2021-07-06 16:43:16.324 P00 INFO: restore command begin 2.34: --exec-id=127494-e783ff5c --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-host=test-machine02 --repo1-host-user=postgres --stanza=prod_backup
2021-07-06 16:43:16.681 P00 INFO: repo1: restore backup set 20210706-160402F
ERROR: [040]: unable to restore to path '/var/lib/pgsql/13/data' because it contains files
HINT: try using --delta if this is what you intended.
2021-07-06 16:43:16.683 P00 INFO: restore command end: aborted with exception [040]
-bash-4.2$
-bash-4.2$
Delta Restore Mode
-bash-4.2$ pgbackrest --stanza=prod_backup --delta restore
2021-07-06 16:27:03.163 P00 INFO: restore command begin 2.34: --delta --exec-id=126382-dc599458 --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-host=test-machine02 --repo1-host-user=postgres --stanza=prod_backup
2021-07-06 16:27:03.509 P00 INFO: repo1: restore backup set 20210706-160402F
2021-07-06 16:27:03.509 P00 INFO: remove invalid files/links/paths from '/var/lib/pgsql/13/data'
2021-07-06 16:27:03.577 P00 INFO: remove invalid files/links/paths from '/u01/pg1_tbls/PG_13_202007201'
2021-07-06 16:27:03.609 P00 INFO: remove invalid files/links/paths from '/u01/pg2_tbls/PG_13_202007201'
2021-07-06 16:27:03.935 P01 INFO: restore file /var/lib/pgsql/13/data/log/dvdrental.tar (2.7MB, 51%) checksum d7fd2c08ba8600ad20ff535ba6cd06c5e405ca66
2021-07-06 16:27:04.138 P02 INFO: restore file /var/lib/pgsql/13/data/log/dvdrental.zip (538.0KB, 84%) checksum 641eeaf07178afa9c5f9025b76ffedd4bf51aba4
2021-07-06 16:27:04.282 P01 INFO: restore file /var/lib/pgsql/13/data/global/pg_control.pgbackrest.tmp (8KB, 94%) checksum 65853b13888f43b1d96528073d4a2fef2e3bc082
2021-07-06 16:27:04.436 P01 INFO: restore file /var/lib/pgsql/13/data/log/postgresql-Tue.log (490B, 99%) checksum 3b958e09474a0aefaf73510136a39738b9ac8e42
2021-07-06 16:27:04.438 P02 INFO: restore file /var/lib/pgsql/13/data/backup_label (259B, 99%) checksum d813c2fdc354cec97ac65fc1857b98f6ba9bfed2
2021-07-06 16:27:04.812 P00 INFO: write updated /var/lib/pgsql/13/data/postgresql.auto.conf
2021-07-06 16:27:04.815 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2021-07-06 16:27:04.816 P00 INFO: restore command end: completed successfully (1653ms)
-bash-4.2$
pgbackrest restore command on backup server
-bash-4.2$ pgbackrest --stanza=prod_backup --delta restore
2021-07-06 16:19:12.268 P00 INFO: restore command begin 2.34: --delta --exec-id=72448-79efe8b4 --log-level-console=info --pg1-host=test-machine01 --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/pg_backup --stanza=prod_backup
ERROR: [072]: restore command must be run on the PostgreSQL host
2021-07-06 16:19:12.268 P00 INFO: restore command end: aborted with exception [072]
-bash-4.2$
Restore on same directory structure as of backup database
Step 1. Issue pgBackRest restore command: Execute pgBackRest restore command on Database Server. We will use remove all files before restore command.
-bash-4.2$ rm -rf /var/lib/pgsql/13/data/*
-bash-4.2$
-bash-4.2$ rm -rf /u01/pg1_tbls/*
-bash-4.2$
-bash-4.2$ rm -rf /u01/pg2_tbls/*
-bash-4.2$
-bash-4.2$ hostname
test-machine01
-bash-4.2$ whoami
postgre
-bash-4.2$ pgbackrest --stanza=prod_backup restore
2021-07-06 16:49:48.565 P00 INFO: restore command begin 2.34: --exec-id=127929-d8c05119 --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-host=test-machine02 --repo1-host-user=postgres --stanza=prod_backup
2021-07-06 16:49:48.939 P00 INFO: repo1: restore backup set 20210706-160402F
2021-07-06 16:49:49.243 P02 INFO: restore file /var/lib/pgsql/13/data/pg_tblspc/16713/PG_13_202007201/16716/17178 (1.2MB, 1%) checksum c9d663bccee764ef657c9678060b69df6925b313
2021-07-06 16:49:49.335 P02 INFO: restore file /var/lib/pgsql/13/data/pg_tblspc/16713/PG_13_202007201/16715/16850 (1.2MB, 3%) checksum b753a2294d3b7024cbc1835112eb069843379896
2021-07-06 16:49:49.338 P01 INFO: restore file /var/lib/pgsql/13/data/log/dvdrental.tar (2.7MB, 7%) checksum d7fd2c08ba8600ad20ff535ba6cd06c5e405ca66
2021-07-06 16:49:52.462 P01 INFO: restore file /var/lib/pgsql/13/data/base/1/14027 (0B, 100%)
2021-07-06 16:49:52.463 P00 INFO: write updated /var/lib/pgsql/13/data/postgresql.auto.conf
2021-07-06 16:49:52.466 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2021-07-06 16:49:52.468 P00 INFO: restore command end: completed successfully (3904ms)
Step 2. Verify PG_DATA directory: In PG_DATA directory you will notice a new file: recovery.signal and postgresql.auto.conf. recovery.signal tells PostgreSQL to enter normal archive recovery. pgBackRest restore command will also add many recovery parameters in postgresql.auto.conf.
-bash-4.2$ pwd
/var/lib/pgsql/13/data
-bash-4.2$ ls -ltr
-rw-------. 1 postgres postgres 324 Jul 6 16:27 postgresql.auto.conf
-rw-------. 1 postgres postgres 0 Jul 6 16:27 recovery.signal
-bash-4.2$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
archive_mode = 'on'
archive_command = 'pgbackrest --stanza=prod_backup archive-push %p'
# Recovery settings generated by pgBackRest restore on 2021-07-06 16:27:04
restore_command = 'pgbackrest --stanza=prod_backup archive-get %f "%p"'
-bash-4.2$
Step 3. Start PostgreSQL Service: Once all the above steps are done. Start postgresql services.
[root@test-machine01 ~]# systemctl start postgresql-13
[root@test-machine01 ~]#
Step 4. Verify Data: Verify that required data exists after restore and recovery.
sample2=#
sample2=# select count(*) from t_Test;
count
-------
1000
(1 row)
sample2=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=#
sample1=# select count(*) from t_Test;
count
-------
1000
(1 row)
sample1=#
Restore in different directory structure as of backup database
Let assume we are doing a restore in a different server having a directory structure as below.
[root@test-machine01 ~]# cd /u01
[root@test-machine01 u01]#
[root@test-machine01 u01]# mkdir psql-data
[root@test-machine01 u01]# mkdir psql5_tbls
[root@test-machine01 u01]# mkdir psql6_tbls
[root@test-machine01 u01]# chown postgres:postgres psql-data
[root@test-machine01 u01]# chown postgres:postgres psql5_tbls
[root@test-machine01 u01]# chown postgres:postgres psql6_tbls
[root@test-machine01 u01]#
Step 1. Issue pgBackrest restore command: Execute pgBackRest restore command in Database Server. Please note we need to use option tablespace-map to do new tablespace mapping.
-bash-4.2$
-bash-4.2$ pgbackrest --stanza=prod_backup --db-path=/u01/psql-data --tablespace-map=myts01=/u01/psql5_tbls --tablespace-map=myts02=/u01/psql6_tbls restore
2021-07-06 17:25:40.968 P00 INFO: restore command begin 2.34: --exec-id=130077-bbffade2 --log-level-console=info --log-level-file=debug --pg1-path=/u01/psql-data --process-max=2 --repo1-host=test-machine02 --repo1-host-user=postgres --stanza=prod_backup --tablespace-map=myts01=/u01/psql5_tbls --tablespace-map=myts02=/u01/psql6_tbls
2021-07-06 17:25:41.343 P00 INFO: repo1: restore backup set 20210706-160402F
2021-07-06 17:25:41.343 P00 INFO: remap data directory to '/u01/psql-data'
2021-07-06 17:25:41.343 P00 INFO: map tablespace 'pg_tblspc/16713' to '/u01/psql5_tbls'
2021-07-06 17:25:41.343 P00 INFO: map tablespace 'pg_tblspc/16714' to '/u01/psql6_tbls'
2021-07-06 17:25:41.796 P01 INFO: restore file /u01/psql-data/log/dvdrental.tar (2.7MB, 3%) checksum d7fd2c08ba8600ad20ff535ba6cd06c5e405ca66
2021-07-06 17:25:45.568 P01 INFO: restore file /u01/psql-data/base/1/14027 (0B, 100%)
2021-07-06 17:25:45.568 P00 INFO: write updated /u01/psql-data/postgresql.auto.conf
2021-07-06 17:25:45.575 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2021-07-06 17:25:45.579 P00 INFO: restore command end: completed successfully (4613ms)
Step 2: Existing Table mapping: If you are not sure about the tablespace name you can check in the file called backup.manifest exists in the backup directory at pgBackRest backup server in the backup catalog.
-bash-4.2$ hostname
test-machine02
-bash-4.2$ pwd
/u01/pg_backup/backup/prod_backup/20210630-150037F
-bash-4.2$
-bash-4.2$ grep tablespace backup.manifest
pg_tblspc/16713={"path":"/u01/pg1_tbls","tablespace-id":"16713","tablespace-name":"myts01","type":"link"}
pg_tblspc/16714={"path":"/u01/pg2_tbls","tablespace-id":"16714","tablespace-name":"myts02","type":"link"}
pg_data/tablespace_map={"checksum":"0fc51ca8f6a82e7988db32f75cf3ee80c0da606b","master":true,"repo-size":48,"size":40,"timestamp":1625054444}
-bash-4.2$
Step 3. Verify PG_DATA directory: In PG_DATA directory you will notice a new file: recovery.signal and postgresql.auto.conf. recovery.signal which tells PostgreSQL to enter normal archive recovery. pgBackRest restore command will also add many recovery parameters in postgresql.auto.conf.
-bash-4.2$ pwd
/u01/psql-data
-bash-4.2$ ls -ltr
-rw-------. 1 postgres postgres 350 Jul 6 17:25 postgresql.auto.conf
-rw-------. 1 postgres postgres 0 Jul 6 17:25 recovery.signal
-bash-4.2$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
archive_mode = 'on'
archive_command = 'pgbackrest --stanza=prod_backup archive-push %p'
# Recovery settings generated by pgBackRest restore on 2021-07-06 17:25:45
restore_command = 'pgbackrest --pg1-path=/u01/psql-data --stanza=prod_backup archive-get %f "%p"'
-bash-4.2$
Step 4. Start PostgreSQL Service: Once all the above steps are done. Start postgresql services using pg_ctl
-bash-4.2$
-bash-4.2$ /usr/pgsql-13/bin/pg_ctl -D /u01/psql-data start
waiting for server to start....2021-07-06 17:28:39.128 +03 [130256] LOG: redirecting log output to logging collector process
2021-07-06 17:28:39.128 +03 [130256] HINT: Future log output will appear in directory "log".
.. done
server started
-bash-4.2$
Step 5. Verify Data: Verify that required data exists after restore and recovery.
-bash-4.2$ psql
psql (13.1)
Type "help" for help.
postgres=#
postgres=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=# select count(*) from t_Test;
count
-------
1000
(1 row)
sample1=# \connect sample2
You are now connected to database "sample2" as user "postgres".
sample2=# select count(*) from t_Test;
count
-------
1000
(1 row)
sample2=#