PostgreSQL Point-in-time Recovery (Incremental Backup)

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 fullbackup.

Advantages

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

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

Summary of PostgreSQL Point-in-time Recovery Steps

Extract files from base backup
Copy files from pg_xlog folder
Create recovery.conf file
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 automaticallyarchive 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_xlogfolder 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

저작자표시

'데이터베이스 > PostgreSQL' 카테고리의 다른 글

Postgresql pg_hda.conf 환경설정. (0)	2010.10.31
postgresql 사용법 - psql (0)	2009.08.05
COPY 관련 설명 (0)	2008.07.24
pg_dump (0)	2008.07.24

자유로운 꿈을 꿈꾸며

PostgreSQL Point-in-time Recovery (Incremental Backup)

Advantages

Database initialization

Data Simulation & Backup Process

Disaster come in

Recovery Process

Conclusion

'데이터베이스 > PostgreSQL' 카테고리의 다른 글

티스토리툴바

PostgreSQL Point-in-time Recovery (Incremental Backup)

Advantages

Database initialization

Data Simulation & Backup Process

Disaster come in

Recovery Process

Conclusion

'데이터베이스 > PostgreSQL' 카테고리의 다른 글

'데이터베이스/PostgreSQL' Related Articles

티스토리툴바