Restore Backup using pgBackRest in PostgreSQL

July 11, 2021
()

Restore Backup using pgBackRest in PostgreSQL

In the previous blog, we saw How to Install and Configure pgBackRest for Backup Click here to read more. 

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.

For demonstration, we will be using our PostgreSQL Cluster created on Port: 5432.  Click here to learn How to create PostgreSQL Cluster. For pgBackRest backup commands, Click here to read more.


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=#


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Related Articles

, , , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?