cancel
Showing results for 
Search instead for 
Did you mean: 

Point in time recovery (PITR) till latest timestamp

 

Point in time recovery can be done using the backup and latest WAL Files. "Latest" in a time line ensures that you are able to recover the database till the last a moment with least data loss. 

 

Please find the below steps to perform PITR:

 

1. Take FULL BART backup :

 

[enterprisedb@4a6711827ac3 bin]$ ./bart backup -s ppas11 --backup-name ppas11_full_backup

INFO: DebugTarget - getVar(checkDiskSpace.bytesAvailable)
INFO: creating full backup using pg_basebackup for server 'ppas11'
INFO: creating backup for server 'ppas11'
INFO: backup identifier: '1557166032805'
INFO: backup completed successfully
INFO:
BART VERSION: 2.3.0
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1557166032805
BACKUP NAME: ppas11_full_backup
BACKUP PARENT: none
BACKUP LOCATION: /opt/backups/ppas11/1557166032805
BACKUP SIZE: 62.88 MB
BACKUP FORMAT: tar
BACKUP TIMEZONE: UTC
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 00000001000000000000001A
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-05-06 18:07:13 UTC
STOP TIME: 2019-05-06 18:07:13 UTC
TOTAL DURATION: 0 sec(s)

[enterprisedb@4a6711827ac3 bin]$ date
Mon May 6 18:07:35 UTC 2019

 

 

2. Verify backup that was taken :

 


[enterprisedb@4a6711827ac3 bin]$ ./bart show-backups

SERVER NAME BACKUP ID BACKUP NAME BACKUP PARENT BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS
ppas11 1557166032805 ppas11_full_backup none 2019-05-06 18:07:13 UTC 62.88 MB 0.00 bytes 0 active

 

 

3. Create table for PITR test & switch a few archives after to make sure change gets written :

 


[enterprisedb@4a6711827ac3 bin]$ psql

psql.bin (11.2.9)
Type "help" for help.

edb=# \dt
Did not find any relations.

edb=# create table a1 (id int);
CREATE TABLE

edb=# insert into a1 values (generate_series(1,10));
INSERT 0 10

edb=# select * from a1;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)

edb=# select pg_switch_wal;
pg_switch_wal
---------------
0/1B00A328
(1 row)

edb=# select pg_switch_wal;
pg_switch_wal
---------------
0/1C000078
(1 row)

edb=# select pg_switch_wal;
pg_switch_wal
---------------
0/1D000000
(1 row)

 

 

4. Make the new data dir for restore & Restore DB Cluster just a few minutes after backup time to include table :

 


[enterprisedb@4a6711827ac3 bin]$ mkdir /usr/edb/as11/data_restore

- BACKUP TIME - {2019-05-06 18:07:13}

[enterprisedb@4a6711827ac3 bin]$ ./bart restore -p /usr/edb/as11/data_restore -i 1557166032805- g '2019-05-06 18:10:06' -s ppas11

INFO: restoring backup '1557166032805' of server 'ppas11'
INFO: base backup restored
INFO: creating recovery.conf file
INFO: WAL file(s) will be streamed from the BART host
INFO: archiving is disabled
INFO: permissions set on $PGDATA
INFO: restore completed successfully

 

 

5. Go to new $PGDATA restored DIR to change port number in the "postgresql.conf" file so no port conflicts, start new DB CLuster, & check PITR with new table :


[enterprisedb@4a6711827ac3 bin]$ cd /usr/edb/as11/data_restore

 

Before :

 

[enterprisedb@4a6711827ac3 data_restore]$ grep "port =" postgresql.conf

port = 5444 # (change requires restart)

After :

 

port = 5445 # (change requires restart)

[enterprisedb@4a6711827ac3 bin]$ pg_ctl -D /usr/edb/as11/data_restore start

waiting for server to start....2019-05-06 18:12:19 UTC LOG: listening on IPv4 address "0.0.0.0", port 5445
2019-05-06 18:12:19 UTC LOG: listening on IPv6 address "::", port 5445
2019-05-06 18:12:19 UTC LOG: listening on Unix socket "/tmp/.s.PGSQL.5445"
2019-05-06 18:12:19 UTC LOG: redirecting log output to logging collector process
2019-05-06 18:12:19 UTC HINT: Future log output will appear in directory "log".
done
server started


[enterprisedb@4a6711827ac3 bin]$ psql
psql.bin (11.2.9)
Type "help" for help.

edb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------------
public | a1 | table | enterprisedb
(1 row)


edb=# select * from a1
edb-# ;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)

 

Version history
Revision #:
9 of 9
Last update:
‎05-07-2019 09:06 AM
Updated by:
 
Contributors