Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Point In Time Recovery

  • 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.
  • It is backup the latest archivelog since the last backup instead of full database backup.
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.
Point-in-time Recovery (Incremental Backup) in PostgreSQL server.
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) 
Point-in-time Recovery Steps:
  • Extract files from base backup
  • Copy files from pg_xlog folder
  • Create recovery.conf file
  • Start Recover
1) Create a testing database cluster Using initdb, all database files under /usr/local/pgsql/pgDataPITR/
-bash-3.2$ pwd/opt/PostgresPlus/9.1AS/bin
-bash-3.2$ initdb start -D /u02/data1/

Start the database-bash-3.2$ ./pg_ctl start -D /u02/data1                             
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.
archive directory:
-bash-3.2$ mkdir -p /u02/ssslocation/pgpitr/walbkp 

Backup Data directory(#tar -cvzf u02/ssslocation/pgpitr/databkp/basebkp.tar.gz /u02/data1/):
-bash-3.2$ mkdir -p /u02/ssslocation/pgpitr/databkp 

Modify postgresql.conf
-bash-3.2$ vi /u02/data1/postgresql.conf 
archive_mode = on               # allows archiving to be done(change requires restart)
archive_command = 'cp %p /u02/ssslocation/pgpitr/walbkp/%f' # command to use to archive a logfile segment
wal_level = hot_standby         # minimal, archive, or hot_standby
Restart the database:
-bash-3.2$ ./pg_ctl stop -D /u02/data1 -m i
waiting for server to shut down....LOG:  received immediate shutdown request
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
 done
server stopped

 -bash-3.2$ ./pg_ctl start -D /u02/data1 
server starting
LOG:  
        ** EnterpriseDB Dynamic Tuning Agent ********************************************
         *       System Utilization: 66 %                                               
*        *         Database Version: 9.1.2.2                                             
*        * Operating System Version:                                                     
*        *     Number of Processors: 0                                                  
*        *           Processor Type:                                                     
*        *   Processor Architecture:                                                     
*        *            Database Size: 0.1    GB                                           
*        *                      RAM: 1.0    GB                                           
*        *            Shared Memory: 1011   MB                                           
*        *       Max DB Connections: 104                                                 
*        *               Autovacuum: on                                                  
*        *       Autovacuum Naptime: 60   Secnds                                        
*        *            InfiniteCache: off                                                      
*        *    InfiniteCache Servers: 0                                                   
*        *       InfiniteCache Size: 0.000  GB                                           
*       ***********************************************************************
-bash-3.2$ LOG:  loaded library "$libdir/dbms_pipe"
LOG:  loaded library "$libdir/edb_gen"
LOG:  loaded library "$libdir/plugins/plugin_debugger"
LOG:  loaded library "$libdir/plugins/plugin_spl_debugger"
LOG:  database system was interrupted; last known up at 2012-11-14 12:10:38 IST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  consistent recovery state reached at 0/20FA714
LOG:  record with zero length at 0/20FA714
LOG:  redo is not required
LOG:  
         ** EnterpriseDB Dynamic Tuning Agent ********************************************
         *       System Utilization: 66 %                                                
*        *         Database Version: 9.1.2.2                                             
*        * Operating System Version:                                                     
*        *     Number of Processors: 0                                                   
*        *           Processor Type:                                                     
*        *   Processor Architecture:                                                     
*        *            Database Size: 0.1    GB                                           
*        *                      RAM: 1.0    GB                                           
*        *            Shared Memory: 1011   MB                                           
*        *       Max DB Connections: 104                                                 
*        *               Autovacuum: on                                                  
*        *       Autovacuum Naptime: 60   Secnds                                        
*        *            InfiniteCache: off                                                 
*        *    InfiniteCache Servers: 0                                                   
*        *       InfiniteCache Size: 0.000  GB                                           
*        ****************************************************************************
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

3)First you need to 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 /u02/data1/pg_xlog. 
  • 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
  • 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 
For example,
[root@asmhost pg_xlog]# ls -lsh
total 33M
 17M -rw------- 1 enterprisedb enterprisedb  16M Nov 14 12:28 000000010000000000000002
 17M -rw------- 1 enterprisedb enterprisedb  16M Nov  9 14:55 000000010000000000000003
4.0K drwx------ 2 enterprisedb enterprisedb 4.0K Nov  9 12:20 archive_status     
This is the log files that we going to use as the roll-forward PostgreSQL Point-in-time Recovery .
configure the WAL filepath in postgresql.conf file:-
-bash-3.2$ ./psql -p 5445 sssdb

 sssdb=# show archive_command;
             archive_command             
-----------------------------------------
 cp %p /u02/ssslocation/pgpitr/walbkp/%f
(1 row)

sssdb=# show archive_mode;
  archive_mode 
 --------------
   on
(1 row) 
  • 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 /u02/ssslocation/pgpitr/walbkp/ folder.
  • 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
[root@asmhost pg_xlog]# pwd
/u02/data1/pg_xlog
/u02/ssslocation/pgpitr/walbkp/
/u02/ssslocation/pgpitr/databkp 
4)Data Simulation & Backup Process
Create dummy tables and records – we will dynamic records in a new table, 1000k records will force PostgreSQL to create enough log files in pg_xlog folder and fire the archive process to archive the log files from /u02/data1/pg_xlog to /u02/ssslocation/pgpitr/walbkp, every logs file contain around 16M size file.
sssdb=# create table test_1 as select * from pg_class;
SELECT 456
 
sssdb=# select * from current_timestamp;
        current_timestamp         
----------------------------------
 14-NOV-12 12:36:23.264212 +05:30
(1 row)

sssdb=# create table test_2 as select * from pg_description;
SELECT 3534

sssdb=# create table test_3 as select * from pg_description;
SELECT 3534

sssdb=# create table test_4(id number);
CREATE TABLE
 
sssdb=# select * from current_timestamp;
        current_timestamp         
----------------------------------
 14-NOV-12 12:46:00.076725 +05:30
(1 row)
 
sssdb=# insert into test_4 values(generate_series(100000,10000000));
INSERT 0 990000
 
sssdb=# select * from current_timestamp;
        current_timestamp         
----------------------------------
 14-NOV-12 12:51:42.144344 +05:30
(1 row) 
Log files look like following
[root@asmhost pg_xlog]# cd /u02/ssslocation/pgpitr/walbkp
[root@asmhost walbkp]# ls
000000010000000000000002  00000001000000000000000F  00000001000000000000001C
000000010000000000000003  000000010000000000000010  00000001000000000000001D
000000010000000000000004  000000010000000000000011  00000001000000000000001E
000000010000000000000005  000000010000000000000012  00000001000000000000001F
000000010000000000000006  000000010000000000000013  000000010000000000000020
000000010000000000000007  000000010000000000000014  000000010000000000000021
000000010000000000000008  000000010000000000000015  000000010000000000000022
000000010000000000000009  000000010000000000000016  000000010000000000000023
00000001000000000000000A  000000010000000000000017  000000010000000000000024
00000001000000000000000B  000000010000000000000018  000000010000000000000025
00000001000000000000000C  000000010000000000000019  000000010000000000000026
00000001000000000000000D  00000001000000000000001A  000000010000000000000027
00000001000000000000000E  00000001000000000000001B

[root@asmhost walbkp]# pwd
/u02/ssslocation/pgpitr/walbkp
 
[root@asmhost walbkp]# ls
000000010000000000000002  00000001000000000000000F  00000001000000000000001C
000000010000000000000003  000000010000000000000010  00000001000000000000001D
000000010000000000000004  000000010000000000000011  00000001000000000000001E
000000010000000000000005  000000010000000000000012  00000001000000000000001F
000000010000000000000006  000000010000000000000013  000000010000000000000020
000000010000000000000007  000000010000000000000014  000000010000000000000021
000000010000000000000008  000000010000000000000015  000000010000000000000022
000000010000000000000009  000000010000000000000016  000000010000000000000023
00000001000000000000000A  000000010000000000000017  000000010000000000000024
00000001000000000000000B  000000010000000000000018  000000010000000000000025
00000001000000000000000C  000000010000000000000019  000000010000000000000026
00000001000000000000000D  00000001000000000000001A  000000010000000000000027
00000001000000000000000E  00000001000000000000001B

[root@asmhost walbkp]# cd /u02/data1/pg_xlog
[root@asmhost pg_xlog]# pwd
/u02/data1/pg_xlog

[root@asmhost pg_xlog]# ls
000000010000000000000002  000000010000000000000010  00000001000000000000001E
000000010000000000000003  000000010000000000000011  00000001000000000000001F
000000010000000000000004  000000010000000000000012  000000010000000000000020
000000010000000000000005  000000010000000000000013  000000010000000000000021
000000010000000000000006  000000010000000000000014  000000010000000000000022
000000010000000000000007  000000010000000000000015  000000010000000000000023
000000010000000000000008  000000010000000000000016  000000010000000000000024
000000010000000000000009  000000010000000000000017  000000010000000000000025
00000001000000000000000A  000000010000000000000018  000000010000000000000026
00000001000000000000000B  000000010000000000000019  000000010000000000000027
00000001000000000000000C  00000001000000000000001A  000000010000000000000028
00000001000000000000000D  00000001000000000000001B  archive_status
00000001000000000000000E  00000001000000000000001C
00000001000000000000000F  00000001000000000000001D

5)Create a full databse backup – base backup
sssdb=# select pg_start_backup('Full backup');
 pg_start_backup 
-----------------
 0/29000020
(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.
[root@asmhost databkp]# pwd
/u02/ssslocation/pgpitr/databkp
[root@asmhost databkp]# tar -cvzf basebkp.tar.gz /u02/data1/
[root@asmhost databkp]# ls
basebkp.tar.gz                                                                 
  • basebkp.tar.gz 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)
sssdb=# select pg_stop_backup();
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/29000144
(1 row)
6) prepare for Point-in-time Recovery  
The pg_start_backup() and pg_stop_backup() backup labels will created in 000000010000000000000029.00000020.backup file. This is a good habit to make a label here.
[root@asmhost pg_xlog]# pwd
/u02/data1/pg_xlog

[root@asmhost pg_xlog]# cat 000000010000000000000029.00000020.backup
START WAL LOCATION: 0/29000020 (file 000000010000000000000029)
STOP WAL LOCATION: 0/29000144 (file 000000010000000000000029)
CHECKPOINT LOCATION: 0/29000050
BACKUP METHOD: pg_start_backup
START TIME: 2012-11-14 12:57:05 IST
LABEL: Full backup
STOP TIME: 2012-11-14 13:04:17 IST
[root@asmhost pg_xlog]# ls -l|wc -l
43

[root@asmhost pg_xlog]# cd /u02/ssslocation/pgpitr/walbkp/
[root@asmhost walbkp]# echo *.backup
000000010000000000000029.00000020.backup

[root@asmhost walbkp]# cat 000000010000000000000029.00000020.backup
START WAL LOCATION: 0/29000020 (file 000000010000000000000029)
STOP WAL LOCATION: 0/29000144 (file 000000010000000000000029)
CHECKPOINT LOCATION: 0/29000050
BACKUP METHOD: pg_start_backup
START TIME: 2012-11-14 12:57:05 IST
LABEL: Full backup
STOP TIME: 2012-11-14 13:04:17 IST

[root@asmhost walbkp]# ls -l|wc -l
42
7) Table test_5, test_6 created time notify–prepare for Point-in-time Recovery
sssdb=# create table test_5(id number(8));
CREATE TABLE
sssdb=# insert into test_5 values(generate_series(1,1000000));
INSERT 0 1000000
sssdb=# insert into test_5 values(generate_series(1,1000000));
INSERT 0 1000000
 
sssdb=# select * from current_timestamp;
        current_timestamp         
----------------------------------
 14-NOV-12 13:13:54.047576 +05:30
(1 row)
sssdb=# create table test_6(id number(8));
CREATE TABLE

sssdb=# select * from current_timestamp;
        current_timestamp         
----------------------------------
 14-NOV-12 13:14:34.626699 +05:30
(1 row)

sssdb=# create table test_7(id number(8));
CREATE TABLE
sssdb=# insert into test_6 values(generate_series(1,1000000));
INSERT 0 1000000
sssdb=# insert into test_7 values(generate_series(1,10000000));
INSERT 0 10000000
[root@asmhost walbkp]# pwd
/u02/ssslocation/pgpitr/walbkp
[root@asmhost walbkp]# ls -l|wc -l
91

[root@asmhost walbkp]# cd /u02/data1/pg_xlog
[root@asmhost pg_xlog]# ls -l|wc -l
53 
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, don't forget the log file format :) ~ take sometime to review and understand the above log file generation sequence
sssdb=# select table_name, status from user_tables;
 table_name | status 
------------+--------
 TEST_1     | VALID # created time   14-NOV-12 12:36:23
 TEST_2     | VALID # created time   14-NOV-12 12:46:00
 TEST_3     | VALID # created time
 TEST_4     | VALID # created time   14-NOV-12 12:51:42
 TEST_5     | VALID     # created time   14-NOV-12 13:13:54
 TEST_6     | VALID # created time   14-NOV-12 13:14:34
 TEST_7     | VALID # created time   14-NOV-12 14:07:40
(7 rows)
8)Disaster come in
We have to do something in order to make our PostgreSQL server go down.
sssdb=# select * from current_timestamp;
        current_timestamp        
---------------------------------
 14-NOV-12 14:07:40.00999 +05:30
(1 row)

sssdb=# select * from current_timestamp;
        current_timestamp        
---------------------------------
 14-NOV-12 14:07:40.00999 +05:30
(1 row)
--Kill the postgresql process
[root@asmhost ~]# ps -ef|grep data1
506       6536     1  0 12:28 pts/1    00:00:00 /opt/PostgresPlus/9.1AS/bin/edb-postgres -D /u02/data1
root      9542  6101  0 14:21 pts/3    00:00:00 grep data1

[root@asmhost ~]# kill -9 $(head -1 /u02/data1/postmaster.pid)

[root@asmhost ~]# ps -ef|grep data1
root      9680  6101  0 14:24 pts/3    00:00:00 grep data1
9)steps of Recovery Process 
Finally we reach recovery process, Please remember 1 file and 2 folders
  • Base backup file /u02/ssslocation/pgpitr/databkp/basebkp.tar.gz
  •  Log files have not archive yet – /u02/data1/pg_xlog  (all files under Pg_xlog folder)
  •  WALs –  /u02/ssslocation/pgpitr/walbkp ( all archive files under  folder may be a remote storage in real environment)
step1.Rename data1 to olddata1.bad.data, assume database file in data1 folder was damaged due to disaster we created just now, we need to create a fresh database later.
[root@asmhost u02]# mv data1 olddata1.bad.data
[root@asmhost u02]# ls
admin  data            lost+found  spl_bkp  ssslocation  sssw.csv  tbs1
app    olddata1.bad.data  oradata  sss.csv  sssnew.csv   tbs

[root@asmhost u02]# pwd
/u02
[root@asmhost u02]# mkdir data1
[root@asmhost u02]# ls -l|grep data1
drwxr-xr-x  2 root         root          4096 Nov 14 14:38 data1
drwx------ 14 enterprisedb enterprisedb  4096 Nov 14 13:04 data1.bad.data

--change the owner permission
[root@asmhost u02]# chown enterprisedb:enterprisedb data1/

[root@asmhost u02]# ls -l|grep data1
drwxr-xr-x  2 enterprisedb enterprisedb  4096 Nov 14 14:38 data1
drwx------ 14 enterprisedb enterprisedb  4096 Nov 14 13:04 data1.bad.dat 
step2.Unzip / extract files basebkp.tar, create a new data1 folder under , it just like what we did before. Move all extracted files from current location to /u02/data1
[root@asmhost pgpitr]# pwd
/u02/ssslocation/pgpitr

[root@asmhost pgpitr]# cd databkp/
[root@asmhost databkp]# ls
basebkp.tar.gz

[root@asmhost databkp]# mv  /u02/data1
step 3.Start database
-bash-3.2$ ./pg_ctl start -D /u02/data1
pg_ctl: another server might be running; trying to start server anyway
server starting
-bash-3.2$ LOG:  
         ** EnterpriseDB Dynamic Tuning Agent ********************************************
         *       System Utilization: 66 %                                                
*        *         Database Version: 9.1.2.2                                             
*        * Operating System Version:                                                     
*        *     Number of Processors: 0                                                   
*        *           Processor Type:                                                     
*        *   Processor Architecture:                                                     
*        *            Database Size: 0.1    GB                                           
*        *                      RAM: 1.0    GB                                           
*        *            Shared Memory: 1011   MB                                           
*        *       Max DB Connections: 104                                                 
*        *               Autovacuum: on                                                  
*        *       Autovacuum Naptime: 60   Seconds                                        
*        *            InfiniteCache: off                                                 
*        *    InfiniteCache Servers: 0                                                   
*        *       InfiniteCache Size: 0.000  GB                                           
*        *****************************************************************************
LOG:  loaded library "$libdir/dbms_pipe"
LOG:  loaded library "$libdir/edb_gen"
LOG:  loaded library "$libdir/plugins/plugin_debugger"
LOG:  loaded library "$libdir/plugins/plugin_spl_debugger"
LOG:  ** EnterpriseDB Dynamic Tuning Agent ********************************************

         *       System Utilization: 66 %                                                
*        *         Database Version: 9.1.2.2                                             
*        * Operating System Version:                                                     
*        *     Number of Processors: 0                                                  
*        *           Processor Type:                                                     
*        *   Processor Architecture:                                                     
*        *            Database Size: 0.5    GB                                           
*        *                      RAM: 1.0    GB                                           
*        *            Shared Memory: 1011   MB                                           
*        *       Max DB Connections: 104                                                 
*        *               Autovacuum: on                                                  
*        *       Autovacuum Naptime: 60   Seconds                                        
*        *            InfiniteCache: off                                                 
*        *    InfiniteCache Servers: 0                                                   
*        *       InfiniteCache Size: 0.000  GB                                           
*        *****************************************************************************
LOG:  database system was interrupted; last known up at 2012-11-14 12:57:05 IST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  consistent recovery state reached at 0/29000050
LOG:  redo starts at 0/29000020
LOG:  record with zero length at 0/290000A0
LOG:  redo done at 0/29000050
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
-bash-3.2$ pwd
/opt/PostgresPlus/9.1AS/bin
-bash-3.2$ ./psql -p 5445 sssdb
psql (9.1.2.2)
Type "help" for help.
sssdb=# select * from user_tables;
    owner     | schemaname | table_name | table_space | status
--------------+------------+------------+-------------+--------
 ENTERPRISEDB | PUBLIC     | TEST_1     |             | VALID
 ENTERPRISEDB | PUBLIC     | TEST_2     |             | VALID
 ENTERPRISEDB | PUBLIC     | TEST_3     |             | VALID
 ENTERPRISEDB | PUBLIC     | TEST_4     |             | VALID
(4 rows)
untill Table test_4 is restored. This testPITR1 table is created before base backup process launched, so this is correct.
step 4. 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.
[root@asmhost pg_xlog]# pwd
/u02/olddata1.bad.data/pg_xlog
[root@asmhost pg_xlog]# cp -R 000* /u02/data1/pg_xlog/
step 5. Create a recovery.conf file and put it under /u02/data1/
vi recovery.conf
restore_command = 'cp /u02/ssslocation/pgpitr/walbkp/%f %p'
recovery_target_time = '14-NOV-12 13:14:34'
This is the final process and most critical backup process
  •  /usr/local/pgsql/pgbackup/wals/ is the folder that we backup our archive log files
  •  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.
-bash-3.2$ ./pg_ctl stop -D /u02/data1 -m i
LOG:  received immediate shutdown request
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
waiting for server to shut down... done
server stopped
-bash-3.2$ ./pg_ctl start -D /u02/data1 
server starting
-bash-3.2$ LOG:  
 
        ** EnterpriseDB Dynamic Tuning Agent ********************************************
         *       System Utilization: 66 %                                                
*        *         Database Version: 9.1.2.2                                            
*        * Operating System Version:                                                     
*        *     Number of Processors: 0                                                   
*        *           Processor Type:                                                     
*        *   Processor Architecture:                                                     
*        *            Database Size: 0.1    GB                                           
*        *                      RAM: 1.0    GB                                           
*        *            Shared Memory: 1011   MB                                           
*        *       Max DB Connections: 104                                                 
*        *               Autovacuum: on                                                  
*        *       Autovacuum Naptime: 60   Seconds                                        
*        *            InfiniteCache: off                                                 
*        *    InfiniteCache Servers: 0                                                   
*        *       InfiniteCache Size: 0.000  GB                                           
*        ********************************************************************************
LOG:  loaded library "$libdir/dbms_pipe"
LOG:  loaded library "$libdir/edb_gen"
LOG:  loaded library "$libdir/plugins/plugin_debugger"
LOG:  loaded library "$libdir/plugins/plugin_spl_debugger"
LOG:  database system was interrupted; last known up at 2012-11-14 14:52:03 IST
LOG:          ** EnterpriseDB Dynamic Tuning Agent ********************************************
         *       System Utilization: 66 %                                                
*        *         Database Version: 9.1.2.2                                             
*        * Operating System Version:                                                     
*        *     Number of Processors: 0                                                   
*        *           Processor Type:                                                     
*        *   Processor Architecture:                                                     
*        *            Database Size: 0.5    GB                                           
*        *                      RAM: 1.0    GB                                           
*        *            Shared Memory: 1011   MB                                           
*        *       Max DB Connections: 104                                                 
*        *               Autovacuum: on                                                  
*        *       Autovacuum Naptime: 60   Secnds                                        
*        *            InfiniteCache: off                                                 
*        *    InfiniteCache Servers: 0                                                   
*        *       InfiniteCache Size: 0.000  GB                                           
*        ********************************************************************************
LOG:  starting point-in-time recovery to 2012-11-14 13:14:34+05:30
LOG:  restored log file "000000010000000000000029" from archive
LOG:  invalid resource manager ID in primary checkpoint record
LOG:  using previous checkpoint record at 0/29000050
LOG:  consistent recovery state reached at 0/290000A0
LOG:  redo starts at 0/29000020
LOG:  restored log file "00000001000000000000002A" from archive
LOG:  restored log file "00000001000000000000002B" from archive
LOG:  restored log file "00000001000000000000002C" from archive
LOG:  restored log file "00000001000000000000002D" from archive
LOG:  restored log file "00000001000000000000002E" from archive
LOG:  restored log file "00000001000000000000002F" from archive
LOG:  restored log file "000000010000000000000030" from archive
LOG:  restored log file "000000010000000000000031" from archive
LOG:  recovery stopping before commit of transaction 2237, time 2012-11-14 13:14:38.881279+05:30
LOG:  redo done at 0/31A76348
LOG:  last completed transaction was at log time 2012-11-14 13:14:17.399818+05:30
cp: cannot stat `/u02/ssslocation/pgpitr/walbkp/00000002.history': No such file or directory
LOG:  selected new timeline ID: 2
cp: cannot stat `/u02/ssslocation/pgpitr/walbkp/00000001.history': No such file or directory
LOG:  archive recovery complete
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
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  14-NOV-12 13:14:34 (table test_6 created).
-bash-3.2$ ./psql -p 5445 sssdb
psql (9.1.2.2)
Type "help" for help.

sssdb=# select * from user_tables;
    owner     | schemaname | table_name | table_space | status 
--------------+------------+------------+-------------+--------
 ENTERPRISEDB | PUBLIC     | TEST_1     |             | VALID
 ENTERPRISEDB | PUBLIC     | TEST_2     |             | VALID
 ENTERPRISEDB | PUBLIC     | TEST_3     |             | VALID
 ENTERPRISEDB | PUBLIC     | TEST_4     |             | VALID
 ENTERPRISEDB | PUBLIC     | TEST_5     |             | VALID
 ENTERPRISEDB | PUBLIC     | TEST_6     |             | VALID                             
(6 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.
  • 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
[root@asmhost data1]# cat recovery.done 
restore_command = 'cp /u02/ssslocation/pgpitr/walbkp/%f %p'
recovery_target_time = '14-NOV-12 13:14:34'
[root@asmhost data1]# 
  • After recovered, log file number will increased
  • 00000001 –> 00000002 –> 00000003
  • If we want to restore table test_6 created at 14-NOV-12 13:14:34, we are unable to do it, it will output error in log file unless we start the whole recovery process again.
  • This archive log files transaction backup and restore mechanism is implemented in many enterprise database like Oracle. 

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL