Point in Time Recovery (PITR) using BARMAN in PostgreSQL

July 8, 2021
()

Point in Time Recovery (PITR) using BARMAN in PostgreSQL

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


In this blog, we will see How to perform PITR using backup taken from BARMAN 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 BARMAN 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/psql_tbls & myts02: /u01/psql2_tbls. Created two sample database dvdrental sample and loaded them with sample data along with configured WAL archiving to copy in BARMAN Server.

-bash-4.2$ psql
psql (13.2)
Type "help" for help.
 
postgres=#
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=#
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/psql_tbls
 myts02     | postgres | /u01/psql2_tbls
 pg_default | postgres |
 pg_global  | postgres |
(4 rows)
 
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 dvdrental | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sample    | 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
(5 rows)
 
postgres=# select name,setting,unit from pg_settings where name in ('archive_mode','archive_command','archive_timeout');
      name       |                                                                          setting                                                                           | unit
-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+------
 archive_command | test ! -f barman@test-machine01:/var/lib/barman/test-machine02/incoming/%f && rsync -a %p barman@test-machine01:/var/lib/barman/test-machine02/incoming/%f |
 archive_mode    | on                                                                                                                                                         |
 archive_timeout | 21600                                                                                                                                                      | s
(3 rows)
postgres=#


Create and Load test table and Switch WAL File: Let’s Create a 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 dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=# create table t_Test (slno integer, ts timestamp);
CREATE TABLE
dvdrental=# insert into t_test values (generate_series(1,1000),now());
INSERT 0 1000
dvdrental=#
dvdrental=# \connect sample
You are now connected to database "sample" as user "postgres".
sample=# create table t_Test (slno integer, ts timestamp);
CREATE TABLE
sample=# insert into t_test values (generate_series(1,1000),now());
INSERT 0 1000
sample=#
sample=#
sample=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/3F04D5B0
(1 row)
 
sample=#


Full DB backup: Perform full cluster-level backup using the barman backup command. Use command barman list-backup to list down all available backups.

-bash-4.2$ whoami
barman
-bash-4.2$ hostname
test-machine01
-bash-4.2$
-bash-4.2$ barman backup test-machine02 --reuse-backup=off
Starting backup using rsync-concurrent method for server test-machine02 in /var/lib/barman/test-machine02/base/20210705T155400
Backup start at LSN: 0/3E000028 (00000001000000000000003E, 00000028)
Starting backup copy via rsync/SSH for 20210705T155400
Copy done (time: 7 seconds)
Asking PostgreSQL server to finalize the backup.
Backup size: 46.8 MiB
Backup end at LSN: 0/3E000138 (00000001000000000000003E, 00000138)
Backup completed (start time: 2021-07-05 15:54:00.342610, elapsed time: 10 seconds)
Processing xlog segments from file archival for test-machine02
        00000001000000000000003E
        00000001000000000000003E.00000028.backup
-bash-4.2$
-bash-4.2$ barman list-backup test-machine02
test-machine02 20210705T155400 - Mon Jul  5 15:54:07 2021 - Size: 46.9 MiB - WAL Size: 0 B (tablespaces: myts01:/u01/psql_tbls, myts02:/u01/psql2_tbls)
test-machine02 20210705T154705 - Mon Jul  5 15:47:07 2021 - Size: 46.9 MiB - WAL Size: 32.3 KiB (tablespaces: myts01:/u01/psql_tbls, myts02:/u01/psql2_tbls)
test-machine02 20210623T154948 - Wed Jun 23 15:49:57 2021 - Size: 46.9 MiB - WAL Size: 96.5 KiB (tablespaces: myts01:/u01/psql_tbls, myts02:/u01/psql2_tbls)
test-machine02 20210623T143656 - Wed Jun 23 14:37:06 2021 - Size: 46.9 MiB - WAL Size: 32.3 KiB (tablespaces: myts01:/u01/psql_tbls, myts02:/u01/psql2_tbls)
-bash-4.2$


Delete All Records: Let simulate human error that caused accidental delete of table data after 2021-07-05 17:21:55

postgres=# select now();
              now
-------------------------------
 2021-07-05 17:21:55.076837+03
(1 row)
postgres=#
postgres=# \connect dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=#
dvdrental=# delete from t_test;
DELETE 1000
dvdrental=# \connect sample
You are now connected to database "sample" as user "postgres".
sample=#
sample=# delete from t_test;
DELETE 1000
sample=#
sample=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/42031280
(1 row)

sample=# select now();
              now
-------------------------------
 2021-07-05 17:23:50.276512+03
(1 row)

sample=#


Stop PostgreSQL Cluster: It is not recommended to issue a barman recover command using a target data directory where a PostgreSQL instance is running. At this point recovery from a shutdown or a crash won’t be much different because the recovery will erase your PostgreSQL data directory.

[root@test-machine02 ~]#  systemctl stop postgresql-13
[root@test-machine02 ~]#


Follow the below steps to perform PITR


Step 1. Issue barman recover command: Command Format: barman recover [server_name] [backup_id] /path/to/recover/dir. Barman recover command will copy all required files in tablespace and base directory. Also, WAL files will be copied in pg_wal. Please note we are using the latest backup: 20210705T155400.

remote-ssh-command: This will allow the barman to execute the copy on a remote server using ssh.
target-time: Determine the time up to when you want to perform recovery.

-bash-4.2$
-bash-4.2$ barman recover --remote-ssh-command "ssh postgres@test-machine02"  --target-time="2021-07-05 17:21:55.076837+03" test-machine02 20210705T155400  /var/lib/pgsql/13/data
Starting remote restore for server test-machine02 using backup 20210705T155400
Destination directory: /var/lib/pgsql/13/data
Remote command: ssh postgres@test-machine02
Doing PITR. Recovery target time: '2021-07-05 17:21:55.076837+03:00'
Using safe horizon time for smart rsync copy: 2021-07-05 15:53:58.685555+03:00
        23701, myts01, /u01/psql_tbls
        23704, myts02, /u01/psql2_tbls
Copying the base backup.
Copying required WAL segments.
Generating recovery configuration
Identify dangerous settings in destination directory.

IMPORTANT
These settings have been modified to prevent data losses

postgresql.auto.conf line 3: archive_command = false

Recovery completed (start time: 2021-07-05 17:42:14.651841, elapsed time: 12 seconds)

Your PostgreSQL server has been successfully prepared for recovery!
-bash-4.2$


Step 2: Verify PG_DATA directory: In PG_DATA directory you will notice a new file: recovery.signal and a new directory: barman_wal.

recovery.signal: Tells PostgreSQL to enter normal archive recovery.
barman_wal: folder contains all required WAL archived files.

-bash-4.2$ pwd
/var/lib/pgsql/13/data
-bash-4.2$
-rw-rw-r--. 1 postgres postgres   245 Jul  5 15:54 backup_label
drwxr-xr-x. 3 postgres postgres  4096 Jul  5 17:42 barman_wal
-rw-rw-r--. 1 postgres postgres     0 Jul  5 17:42 recovery.signal
-rw-------. 1 postgres postgres 28004 Jul  5 17:42 postgresql.conf
-rw-------. 1 postgres postgres   502 Jul  5 17:42 postgresql.auto.conf
-bash-4.2$
-bash-4.2$ ls -l barman_wal
total 81924
-rw-rw-r--. 1 postgres postgres 16777216 Jul  5 17:42 00000001000000000000003E
-rw-rw-r--. 1 postgres postgres      359 Jul  5 17:42 00000001000000000000003E.00000028.backup
-rw-rw-r--. 1 postgres postgres 16777216 Jul  5 17:42 00000001000000000000003F
-rw-rw-r--. 1 postgres postgres 16777216 Jul  5 17:42 000000010000000000000040
-rw-rw-r--. 1 postgres postgres 16777216 Jul  5 17:42 000000010000000000000041
-rw-rw-r--. 1 postgres postgres 16777216 Jul  5 17:42 000000010000000000000042
drwxr-xr-x. 2 postgres postgres        6 Jul  5 17:42 archive_status
-bash-4.2$


Step 3: Verify configuration file postgresql.auto.conf: barman recover command will also add many recovery parameters in postgresql.auto.conf, verify recovery_target_time is correct and same as we had mention in option target-time.

-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'
#BARMAN#archive_command = 'test ! -f barman@test-machine01:/var/lib/barman/test-machine02/incoming/%f && rsync -a %p barman@test-machine01:/var/lib/barman/test-machine02/incoming/%f'
archive_command = false
archive_timeout = '6 h'
wal_keep_size = '160 MB'
restore_command = 'cp barman_wal/%f %p'
recovery_end_command = 'rm -fr barman_wal'
recovery_target_time = '2021-07-05 17:21:55.076837+03'
-bash-4.2$


Step 4. Start PostgreSQL Service: Once all the above steps are done. Start postgresql services.

[root@test-machine02 ~]#  systemctl start postgresql-13
[root@test-machine02 ~]#


Step 5. 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$ vi postgresql-Tue.log
2021-07-06 10:33:09.130 +03 [52925] LOG:  restored log file "00000001000000000000003F" from archive
2021-07-06 10:33:09.162 +03 [52925] LOG:  restored log file "000000010000000000000040" from archive
2021-07-06 10:33:09.187 +03 [52925] LOG:  restored log file "000000010000000000000041" from archive
2021-07-06 10:33:09.210 +03 [52925] LOG:  restored log file "000000010000000000000042" from archive
2021-07-06 10:33:09.226 +03 [52925] LOG:  recovery stopping before commit of transaction 5171, time 2021-07-05 17:23:05.886102+03
2021-07-06 10:33:09.226 +03 [52925] LOG:  pausing at the end of recovery
2021-07-06 10:33:09.226 +03 [52925] HINT:  Execute pg_wal_replay_resume() to promote.


Step 6. Verify Data: Verify that required data exists after restore and recovery.

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

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

sample=#


Step 7. 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)

-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?