Main Tutorials

PostgreSQL Point-in-time Recovery (Incremental Backup)

author image
By |
Viewed: 1,716 (+13 pv/w)
Tags:

PostgreSQL “Point-in-time Recovery” (PITR) also called as incremental database backup , online backup or may be archive backup. The PostgreSQL server records all users’ data modification transaction like insert, update or delete and write it into a file call write-ahead (WAL) log file. This mechanism use the history records stored in WAL file to do roll-forward changes made since last database full backup.

Advantages

  1. Zero down time – The incremental database backup is important to critical system that can not afford even a minute down time. With Point-in-time Recovery, database backup down time can totally eliminated because this mechanism can make database backup and system access happened at the same time.
  2. Save storage size – with incremental database backup, we backup the latest archive log file since last backup instead of full database backup everyday.

If above advantages are concern to you, then you should always implement incremental database backup. Here i demonstrate how to do the Point-in-time Recovery (Incremental Backup) in PostgreSQL server.

Summary of PostgreSQL Backup Steps

  1. Modify postgresql.conf to support archive log
  2. Make a base backup (full database backup)
  3. Backup base backup to remote storage.
  4. Backup WAL (archive log files) to remote storage (continuous process)

Summary of PostgreSQL Point-in-time Recovery Steps

  1. Extract files from base backup
  2. Copy files from pg_xlog folder
  3. Create recovery.conf file
  4. Start Recover

Tutorials started ~ long journey, patient…

Database initialization

1) Create a testing database, all database files under /usr/local/pgsql/pgDataPITR/

Initialize database


[mkyong]$ initdb -D /usr/local/pgsql/pgDataPITR/

Start the database


[mkyong]$ pg_ctl start -D /usr/local/pgsql/pgDataPITR/

2) Make change in Postgresql configuration file (postgresql.conf), we need to make some changes in postgresql.conf file to tell PostgreSQL how to copy or archive WAL files that generated from PostgreSQL server.

Modify postgresql.conf


[mkyong]$ vi postgresql.conf

Make following changes in postgresql.conf


archive_command = on
archive_command = 'cp %p /usr/local/pgsql/pgDataPITR/wals/%f'

Create a wals folder


[mkyong]$ mkdir /usr/local/pgsql/pgDataPITR/wals

Restart the database


[mkyong]$ pg_ctl stop -D /usr/local/pgsql/pgDataPITR/
[mkyong]$ pg_ctl start -D /usr/local/pgsql/pgDataPITR/

Attention!!! Understand how PostgreSQL handle log files, pg_xlog and archive log
pg_xlog is a PostgreSQL log file folder that use to store all data history records. It located at /usr/local/pgsql/pgDataPITR/pg_xlog. For example, when user inserted , update or delete a record, all transaction hisroty will automatically create or append to a file log file under pg_xlog folder. Log file format is look like following format 000000010000000000000001 -> 000000010000000000000006

For example, 


[postgres@localhost pg_xlog]$ ls -lsh
total 113M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:25 000000010000000000000006

Every log file can handle around 16M data, when it excess this limit, it will automatically create a new log file, filename is follow 0-9 and A-Z


000000010000000000000001
..
..
000000010000000000000009
..
..
00000001000000000000000A
..
..
00000001000000000000000Z

This is the log files that we going to use as the roll-forward PostgreSQL Point-in-time Recovery 🙂

Attention!!! Do you Still remember we configure the WAL filepath in postgresql.conf file?


archive_command = on
archive_command = 'cp %p /usr/local/pgsql/pgDataPITR/wals/%f'

This means when pg_xlog folder grow to certain limitation, like 6 log files each contain 16M, when PostgreSQL try to insert a new history record and detected that pg_xlog is full, it will automatically archive the oldest history log file and move it to /usr/local/pgsql/pgDataPITR/wals folder.

Attention!!! We have to backup these archive files continuously (that why it call incremental backup 🙂 ). We do not need to do full database backup anymore, but we do backup those archive log files constantly.

Important log files folder


/usr/local/pgsql/pgDataPITR/pg_xlog
/usr/local/pgsql/pgDataPITR/wals

Data Simulation & Backup Process

Create dummy tables and records – we will dynamic create 455,252 records in a new table, 400k records will force PostgreSQL to create enough log files in pg_xlog folder and fire the archive process to archive the log files from /usr/local/pgsql/pgDataPITR/pg_xlog to /usr/local/pgsql/pgDataPITR/wals, every logs file contain around 16M size file.

1) Table testPITR1 created at 2008-11-25 17:17


# psql
# select (*) from pg_class; –- contain 229 records
# select (*) from pg_description; –- contains 1988 records

# create table testPITR1 as select * from pg_class, pg_description; 
#--totally 229 x 1988 = 455,252 records
# select * from current_timestamp; –-2008-11-25 17:17

Log files look like following


[postgres@localhost pgDataPITR]$ cd pg_xlog/
[postgres@localhost pg_xlog]$ ls -lsh
total 113M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:25 000000010000000000000006
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:16 000000010000000000000007
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000008
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000009
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 00000001000000000000000A
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 00000001000000000000000B
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 00000001000000000000000C

[postgres@localhost pgDataPITR]$ cd wals
[postgres@localhost wals]$ ls -lsh
total 97M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000000
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000001
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000002
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000003
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000004
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000005

2) Create a full databse backup – base backup


# psql
# select pg_start_backup(’Full Backup - Testing’);

pg_start_backup()
——————–
0/6BA9328
(1 row)

pg_start_backup is use to create a label, and log it into log file. (actually this is optional, good habit)

Use a tar command to compress all pgDataPITR folder to make a database base backup.


tar -cvzf /usr/local/pgsql/pgDataPITR/pgdatabk20081125.tar 
 /usr/local/pgsql/pgDataPITR/

Remember !!! pgdatabk20081125.tar this is the full database backup (base backup) including Postgresql configuration , system and all others files and folder.

pg_stop_backup() create a label in log file as well. (actually this is optional, good habit)


select pg_stop_backup();
————————
0/6BA9384
(1 row)

3) Table testPITR2 created at 2008-11-25 18:08:06 –prepare for Point-in-time Recovery


# psql
# create table testPITR2 as select * from pg_class, pg_description; 
# select * from current_timestamp; --2008-11-25 18:08:06


[postgres@localhost pgDataPITR]$ cd pg_xlog/
[postgres@localhost pg_xlog]$ ls -lsh
total 113M
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000A
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000B
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000C
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:08 00000001000000000000000D
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:06 00000001000000000000000E
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000F
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000010

[postgres@localhost wals]$ ls -lsh
total 209M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000000
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000001
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000002
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000003
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000004
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000005
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:02 000000010000000000000006
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:06 000000010000000000000007
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000008
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000009
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000A
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000B
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000C

P.S The pg_start_backup() and pg_stop_backup() backup labels will created in 000000010000000000000006.00BA9328.backup file. This is a good habit to make a label here.


[postgres@localhost wals]$ cat 000000010000000000000006.00BA9328.backup
START WAL LOCATION: 0/6BA9328 (file 000000010000000000000006)
STOP WAL LOCATION: 0/6BA9384 (file 000000010000000000000006)
CHECKPOINT LOCATION: 0/6BA9328
START TIME: 2008-11-25 17:45:24 MYT
LABEL: Full Backup - Testing
STOP TIME: 2008-11-25 18:02:18 MYT

3) Table testPITR3 created at 2008-11-25 18:15:23 –prepare for Point-in-time Recovery


# psql
# create table testPITR3 as select * from pg_class, pg_description; 
# select * from current_timestamp; --–2008-11-25 18:15:23


[postgres@localhost pg_xlog]$ ls -lsh
total 129M
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000010
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000011
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000012
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000013
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000014
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000015
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000016
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000017

[postgres@localhost wals]$ ls -lsh
total 321M

17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000000
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000001
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000002
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000003
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000004
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000005
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:02 000000010000000000000006
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:06 000000010000000000000007
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000008
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000009
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000A
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000B
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000C
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000D
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000E
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000F
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000010
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000011
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000012
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000013

4) Table testPITR4 created at 2008-11-25 20:00:04 –prepare for Point-in-time Recovery


# psql
# create table testPITR4 as select * from pg_class, pg_description; 
# select * from current_timestamp; –-2008-11-25 20:00:04


[root@localhost pg_xlog]# ls -lsh
total 129M
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000017
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000018
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000019
17M -rw——- 1 postgres postgres 16M 2008-11-25 20:01 00000001000000000000001A
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 00000001000000000000001B
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:58 00000001000000000000001C
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:58 00000001000000000000001D
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 00000001000000000000001E

[root@localhost wals]# ls -lsh
total 417M
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000000
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000001
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000002
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000003
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000004
17M -rw——- 1 postgres postgres 16M 2008-11-25 17:17 000000010000000000000005
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:02 000000010000000000000006
8.0K -rw——- 1 postgres postgres 254 2008-11-25 18:02 000000010000000000000006.00BA9328.backup
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:06 000000010000000000000007
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000008
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 000000010000000000000009
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000A
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000B
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:07 00000001000000000000000C
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000D
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000E
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 00000001000000000000000F
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000010
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:14 000000010000000000000011
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000012
17M -rw——- 1 postgres postgres 16M 2008-11-25 18:15 000000010000000000000013
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:58 000000010000000000000014
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:58 000000010000000000000015
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000016
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000017
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000018
17M -rw——- 1 postgres postgres 16M 2008-11-25 19:59 000000010000000000000019

We created 4 tables for PITR recovery, remember table creation time, we going to do recovery base on the time later.

Table testPITR1 created at 2008-11-25 17:17
Table testPITR2 created at 2008-11-25 18:08:06
Table testPITR3 created at 2008-11-25 18:15:23
Table testPITR4 created at 2008-11-25 20:00:04


postgres=# \d
List of relations
Schema | Name | Type | Owner
——–+———–+——-+———-
public | testpitr1 | table | postgres
public | testpitr2 | table | postgres
public | testpitr3 | table | postgres
public | testpitr4 | table | postgres
(4 rows)

Attention!!! Before move on, please study above transaction log files movement that generated by PostgreSQL. We have to fully understand when PostgreSQL will create a new log file and when it will move to archive folder, do not forget the log file format 🙂 ~ take sometime to review and understand the above log file generation sequence

Disaster come in

We have to do something in order to make our PostgreSQL server go down.

1) Kill the postgresql process


kill -9 $(head -1 /usr/local/pgsql/pgDataPITR/postmaster.pid)

2) Just turn off your Power 🙂

Recovery Process

Finally we reach recovery process, Please remember 1 file and 2 folders

a) Base backup file – pgdatabk20081125.tar
b) Log files hanv’t archive yet – all files under Pg_xlog folder
c) WALs – all archive files under wals folder (may be a remote storage in real environment)

Remember!!! Assume we already backup pgdatabk20081125.tar and all archived files to pgbackup folder under /usr/local/pgsql/pgbackup


$ mkdir /usr/local/pgsql/pgbackup/pgbackup
$ mv pgdatabk20081125.tar /usr/local/pgsql/pgbackup/pgbackup
$ cp -r /usr/local/pgsql/pgDataPITR/wals /usr/local/pgsql/pgbackup/pgbackup

1) Rename pgDataPITR to pgDataPITR.bad.data, assume database file in pgDataPITR folder was damaged due to disaster we created just now, we need to create a fresh database later.


$ mv pgDataPITR pgDataPITR.bad.data

2) Unzip / extract files from pgdatabk20081125.tar, create a new pgDataPITR folder under /usr/local/pgdata/, it just like what we did before. Move all extracted files from pgdatabk20081125.tar to /usr/local/pgsql/pgDataPITR.


$ tar -xvzf pgdatabk20081125.tar 
[extract-path/pgDataPITR]$ mv * /usr/local/pgsql/pgDataPITR

Start database


$ ./pg_ctl start -D /usr/local/pgsql/pgDataPITR/
$ psql

postgres=# \d
List of relations
Schema | Name | Type | Owner
——–+———–+——-+———-
public | testpitr1 | table | postgres

Table testPITR1 created at 2008-11-25 17:17 is restored. This testPITR1 table is created before base backup process launched, so this is correct.

3) Copy log files from pg_xlog folder. Some log files still located in pgDataPITR.bad.data pg_xlog folder (those log files hanv’t archive yet) during disaster happening, we need to copy the log file back and recover it as much as possible.


 cp /usr/local/pgsql/pgDataPITR.bad.data/pg_xlog/0* /usr/local/pgsql/pgDataPITR

4) Create a recovery.conf file and put it under /usr/local/pgsql/pgDataPITR


vi recovery.conf

Create following content in recovery.conf 


restore_command = 'cp /usr/local/pgsql/pgbackup/wals/%f %p'
recovery_target_time = '2008-11-25 18:08:06'

Attention !!! This is the final process and most critical backup process

a) /usr/local/pgsql/pgbackup/wals/ is the folder that we backup our archive log files
b) recovery_target_time is the time we need to recover to. Omit this setting will make PostgreSQL recover as much as it can, it may recover all changes.

Remember four tables creation time
Table testPITR1 created at 2008-11-25 17:17:00
Table testPITR2 created at 2008-11-25 18:08:06
Table testPITR3 created at 2008-11-25 18:15:23
Table testPITR4 created at 2008-11-25 20:00:04

Remember !!! Above recovery.conf file will make PostgreSQL take the archive log files from /usr/local/pgsql/pgbackup/wals/ folder and recover the data changes until 2008-11-25 18:08:06 (table testPITR2 created).

5) Start database and output log file to /usr/local/pgsql/pgDataPITR/pg.log


$ pg_ctl start -D /usr/local/pgsql/pgDataPITR -l /usr/local/pgsql/pgDataPITR/pg.log
$ psql
postgres=# \d
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | testpitr1 | table | postgres
 public | testpitr2 | table | postgres
(2 rows)

Table testpitr2 is restored back.

P.S After recovery process finished, recovery.conf will rename to recovery.done by PostgreSQL to avoid start the recovery process again.

We can the view pg.log file to understand how PostgreSQL process the recovery process.


[postgres@localhost pgDataPITR]$ cat pg.log
STATEMENT:  select pg_start_backup("Full Backup - Testing");
LOG:  database system was interrupted; last known up at 2008-11-25 17:45:23 MYT
LOG:  starting archive recovery
LOG:  restore_command = 'cp /usr/local/pgsql/pgbackup/wals/%f %p'
LOG:  recovery_target_time = '2008-11-25 18:08:06+08'
cp: cannot stat `/usr/local/pgsql/pgbackup/wals/00000001.history': No such file or directory
LOG:  restored log file "000000010000000000000006.00BA9328.backup" from archive
LOG:  restored log file "000000010000000000000006" from archive
LOG:  automatic recovery in progress
LOG:  redo starts at 0/6BA9368
LOG:  restored log file "000000010000000000000007" from archive
LOG:  restored log file "000000010000000000000008" from archive
LOG:  restored log file "000000010000000000000009" from archive
LOG:  restored log file "00000001000000000000000A" from archive
LOG:  restored log file "00000001000000000000000B" from archive
LOG:  restored log file "00000001000000000000000C" from archive
LOG:  restored log file "00000001000000000000000D" from archive
LOG:  recovery stopping before commit of transaction 395, time 2008-11-25 18:08:34.180397+08
LOG:  redo done at 0/D85E0FC
LOG:  last completed transaction was at log time 2008-11-25 18:08:34.180397+08

Attention !!!Attention !!! THIS IS ONE TIME PROCESS, after recovery process started and finished, we cant make any recovery changes (like roll forward to another time).

If we want to roll forward to another restore time, we need to start whole recovery process again, like extract files from base backup and copy log files. This is because after PostgreSQL recovered the data , all log files format will changed to other format like following


[postgres@localhost pgDataPITR]$ cd pg_xlog/
[postgres@localhost pg_xlog]$ ls -ls
total 147696
16408 -rw------- 1 postgres postgres 16777216 2008-11-26 14:28 00000002000000000000000D
    8 -rw------- 1 postgres postgres       83 2008-11-26 14:22 00000002.history
16408 -rw------- 1 postgres postgres 16777216 2008-11-26 14:30 00000003000000000000000D
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:16 00000003000000000000000E
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:17 00000003000000000000000F
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:17 000000030000000000000010
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:45 000000030000000000000011
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:17 000000030000000000000012
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:17 000000030000000000000013
16408 -rw------- 1 postgres postgres 16777216 2008-11-25 17:17 000000030000000000000014
    8 -rw------- 1 postgres postgres      158 2008-11-26 14:30 00000003.history

After recovered, log file number will increased
00000001 –> 00000002 –> 00000003

If we want to restore table testPITR3 created at 2008-11-25 18:15:23, we are unable to do it, it will output error in log file unless we start the whole recovery process again.

Conclusion

This archive log files transaction backup and restore mechanism is implemented in many enterprise database like Oracle. The archive log files backup concept is very important, please do some real practice and master it, it really help when your database went down.

Thanks for reading this long article, please correct me if i write something wrong 🙂 hope help.

PostgreSQL Official Point-in-time Recovery Reference
http://www.postgresql.org/docs/8.0/interactive/backup-online.html

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments