Point in Time Recovery (PITR) using pgBackRest in PostgreSQL

Jamsher Khan 16 mins4
()

Point in Time Recovery (PITR) using pgBackRest in PostgreSQL

In the previous blog, we saw How to perform Full recovery using backup taken from pgBackRest Click here to read more.

 

In this blog, we will see How to perform PITR using backup taken from pgBackRest in PostgreSQL.

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$


Delete All Records: Let simulate human error that caused accidental delete of table data after 2021-07-07 10:20:32

sample2=# select now();
              now
-------------------------------
 2021-07-07 10:20:32.888117+03
(1 row)

sample2=#
sample2=# delete from t_test;
DELETE 1000
sample2=#
sample2=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=# delete from t_test;
DELETE 1000
sample1=#
sample1=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/23033920
(1 row)

sample1=#
postgres=# select now();
              now
-------------------------------
 2021-07-07 10:36:29.140257+03
(1 row)


Stop PostgreSQL Cluster:  Stop the Database cluster as we need to remove datafiles.

[root@test-machine01 ~]#  systemctl stop postgresql-13
[root@test-machine01 ~]# rm -rf /var/lib/pgsql/13/data/*
[root@test-machine01 ~]# rm -rf /u01/pg1_tbls/*
[root@test-machine01 ~]# rm -rf /u01/pg2_tbls/*
[root@test-machine01 ~]#


Follow the below steps to perform PITR


Step 1. Issue pgBackRest restore command: Execute pgBackRest restore command in Database Server. We need to specify recovery parameter type & target. Please note we have already removed all files before restore command.

type: Define which recovery type example it can be name, xid, time.
target: Defines the recovery target when –type is name, xid, or time.

-bash-4.2$
-bash-4.2$ pgbackrest --stanza=prod_backup --type=time --target="2021-07-07 10:20:32.888117+03" restore
2021-07-06 17:56:34.046 P00   INFO: restore command begin 2.34: --exec-id=1419-e6513997 --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 --target="2021-07-06 17:46:52.079973+03" --type=time
2021-07-06 17:56:34.412 P00   INFO: repo1: restore backup set 20210706-160402F
2021-07-06 17:56:34.771 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 17:56:37.789 P01   INFO: restore file /var/lib/pgsql/13/data/base/1/14027 (0B, 100%)
2021-07-06 17:56:37.790 P00   INFO: write updated /var/lib/pgsql/13/data/postgresql.auto.conf
2021-07-06 17:56:37.797 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2021-07-06 17:56:37.800 P00   INFO: restore command end: completed successfully (3755ms)
-bash-4.2$


Step 2. 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. Verify parameter recovery_target_time shows desire recovery time.

-bash-4.2$ pwd
/var/lib/pgsql/13/data
-bash-4.2$  ls -ltr
total 64
-rw-------. 1 postgres postgres     0 Jul  6 17:50 recovery.signal
-rw-------. 1 postgres postgres   379 Jul  6 17:50 postgresql.auto.conf
-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:56:37
restore_command = 'pgbackrest --stanza=prod_backup archive-get %f "%p"'
#recovery_target_time = '2021-07-06 17:46:52.079973+03'

# Recovery settings generated by pgBackRest restore on 2021-07-07 10:36:09
restore_command = 'pgbackrest --stanza=prod_backup archive-get %f "%p"'
recovery_target_time = '2021-07-07 10:20:32.888117+03'
-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 logfile: Verify logfile to see everything went fine after restore and recovery.

-bash-4.2$ pwd
/var/lib/pgsql/13/data/log
-bash-4.2$
-bash-4.2$ vi postgresql-Wed.log
2021-07-07 10:37:34.129 P00   INFO: archive-get command begin 2.34: [000000040000000000000023, pg_wal/RECOVERYXLOG] --exec-id=58427-74888df0 --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-07 10:37:34.397 P00   INFO: found 000000040000000000000023 in the repo1: 13-1 archive
2021-07-07 10:37:34.498 P00   INFO: archive-get command end: completed successfully (369ms)
2021-07-07 10:37:34.499 +03 [58410] LOG:  restored log file "000000040000000000000023" from archive
2021-07-07 10:37:34.531 +03 [58410] LOG:  recovery stopping before commit of transaction 1074, time 2021-07-07 10:32:14.734383+03
2021-07-07 10:37:34.531 +03 [58410] LOG:  pausing at the end of recovery
2021-07-07 10:37:34.531 +03 [58410] HINT:  Execute pg_wal_replay_resume() to promote.


Step 5. Verify Data: Verify that required data available 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=#
sample1=# select count(*) from t_Test;
 count
-------
  1000
(1 row)

sample1=# \connect sample2
You are now connected to database "sample2" as user "postgres".
sample2=#
sample2=# select count(*) from t_Test;
 count
-------
  1000
(1 row)

sample2=#


Step 6. End the recovery: As suggested in the logfile execute pg_wal_replay_resume() to end recovery and open DB to established connections. You can use utility pg_isready to verify it.

postgres=#
postgres=# select  pg_wal_replay_resume();
 pg_wal_replay_resume
----------------------

(1 row)

postgres=#

-bash-4.2$ /usr/pgsql-13/bin/pg_isready
/var/run/postgresql:5432 - accepting connections
-bash-4.2$
-bash-4.2$


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?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

4 thoughts on “Point in Time Recovery (PITR) using pgBackRest in PostgreSQL”

Comments are closed.

Related Articles