Recovery.conf and PITR by recovery_target_time - Mailing list pgsql-general

Hi,

I am trying another way to test PITR: by recovery_target_time.

The test machine has the same PG version 9.2.4 and same O/S Ubuntu 12.04 64 bit.    All archived WAL files are shipped
andsaved in /var/pgsql/data/archive, the latest time stamp of them is "2013-08-09 19:30:01", the full hot backup time
isat '2013-08-09 16:47:12'.   

Case 1) I want to recover PG to the state before 18:03:02 that there were 6 tables deleted
Case 2) Hope to recover PG to the point of time right before table TEST8 was created

Transactions in master:
16:45:01    (create 4 test tables : test1, test2, test3, test4)
16:47:12      (FULL HOT BACKUP)
17:50:22      postgres=# CREATE TABLE test5 (id INTEGER PRIMARY KEY); INSERT INTO test5 VALUES
(generate_series(1,4000000)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test5; 
17:57:13      postgres=# CREATE TABLE test6 (id INTEGER PRIMARY KEY); INSERT INTO test6 VALUES
(generate_series(1,1000000)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test6; 
            postgres=# \d
                 List of relations
            Schema | Name  | Type  |  Owner
            --------+-------+-------+----------
             public | test1 | table | postgres (created before full hot backup)
             public | test2 | table | postgres (created before full hot backup)
             public | test3 | table | postgres (created before full hot backup)
             public | test4 | table | postgres (created before full hot backup)
             public | test5 | table | postgres
            public | test6 | table | postgres
18:03:02    postgres=# drop table test1; DROP TABLE
            postgres=# drop table test2; DROP TABLE
            postgres=# drop table test3; DROP TABLE
            postgres=# drop table test4; DROP TABLE
            postgres=# drop table test5; DROP TABLE
            postgres=# drop table test6; DROP TABLE
            postgres=# commit; WARNING: there is no transaction in progress COMMIT
18:04:34     postgres=# CREATE TABLE test7 (id INTEGER PRIMARY KEY); INSERT INTO test7 VALUES
(generate_series(1,1000000)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test7;             
18:11:31    postgres=# CREATE TABLE test8 (id INTEGER PRIMARY KEY); INSERT INTO test8 VALUES
(generate_series(1,1000000));EXPLAIN ANALYZE SELECT COUNT(*) FROM test8; 
            postgres=# CREATE TABLE test9 (id INTEGER PRIMARY KEY); INSERT INTO test9 VALUES
(generate_series(1,1000000));EXPLAIN ANALYZE SELECT COUNT(*) FROM test9; 
            postgres=# CREATE TABLE test10 (id INTEGER PRIMARY KEY); INSERT INTO test10 VALUES
(generate_series(1,1000000));EXPLAIN ANALYZE SELECT COUNT(*) FROM test10; 
19:26:18    postgres=# vacuum;
            VACUUM
            postgres=# begin; INSERT INTO test10 VALUES (generate_series(2000002,3000002));commit; end; BEGIN INSERT 0
1000001COMMIT WARNING: there is no transaction in progress COMMIT  
            postgres=# CREATE TABLE test11 (id INTEGER PRIMARY KEY); INSERT INTO test11 VALUES
(generate_series(1,1000000));EXPLAIN ANALYZE SELECT COUNT(*) FROM test11; 
19:30:01    (ship the WAL file to test machine)




CASE-1:     '2013-08-09 17:57:55'     (only 3 lines in recovery.conf)
            restore_command = 'cp /var/pgsql/data/archive/%f %p'
            recovery_target_time = '2013-08-09 17:57:55'
            recovery_target_inclusive = false
Result:
            LOG:  starting point-in-time recovery to 2013-08-09 17:57:55
            LOG:  restored log file "000000010000006F00000066" from archive
            LOG:  redo starts at 6F/66000020
            LOG:  recovery stopping before commit of transaction 75891, time 2013-08-09 18:07:09.547682+08
            LOG:  redo done at 6F/66003DF0
            FATAL:  requested recovery stop point is before consistent recovery point
            LOG:  startup process (PID 15729) exited with exit code 1
            LOG:  terminating any other active server processes
            [1]+  Exit 1                 ...

CASE-2:      '2013-08-09 18:06:01'     (only 3 lines in recovery.conf)
            restore_command = 'cp /var/pgsql/data/archive/%f %p'
            recovery_target_time = '2013-08-09 18:06:01'
            recovery_target_inclusive = false
Result:
            LOG:  starting point-in-time recovery to 2013-08-09 18:06:01
            LOG:  restored log file "000000010000006F000000B0" from archive
            LOG:  restored log file "000000010000006F0000009B" from archive
            LOG:  redo starts at 6F/9B000020
            LOG:  recovery stopping before commit of transaction 75967, time 2013-08-09 19:30:10.217888+08
            LOG:  redo done at 6F/9B003500
            FATAL:  requested recovery stop point is before consistent recovery point
            LOG:  startup process (PID 19100) exited with exit code 1
            LOG:  terminating any other active server processes
            [1]+  Exit 1                ...


So far I can only restore ALL (i.e. up to 19:30:01) but cannot recover PG at certain Point-of-time.

Can you please advise?

regards