Hi,
I have tried following test cases about PITR:
a) Time and events on Master :
16:32:03 HKT Begin; CREATE TABLE test22 (id INTEGER PRIMARY =
KEY); INSERT INTO test22 VALUES (generate_series(1,220000)); End; =
Commit;=09
16:35:02 HKT Begin; CREATE TABLE test23 (id INTEGER PRIMARY =
KEY); INSERT INTO test23 VALUES (generate_series(1,230000)); End; =
Commit;
16:57:01 HKT Begin; CREATE TABLE test24 (id INTEGER PRIMARY =
KEY); INSERT INTO test24 VALUES (generate_series(1,240000)); End; =
Commit;
15:57:45 HKT Begin; CREATE TABLE test25 (id INTEGER PRIMARY =
KEY); INSERT INTO test25 VALUES (generate_series(1,250000)); End; =
Commit;
16:57:56 HKT drop table test24;
( 16:58:30 HKT <=3D=3D recovery_target_time )
17:01:53 HKT Begin; CREATE TABLE test26 (id INTEGER PRIMARY =
KEY); INSERT INTO test26 VALUES (generate_series(1,260000)); End; =
Commit;
b) Expected Result
- Do PITR on another test machine,=20
- Set recovery_target_time =3D '2013-08-12 16:58:30 HKT', i.e =
wish to get table test22, test23, test25 restored, but not test24 =
(dropped) / test26 (after recovery_target_time)
- PITR should stop the recovery according to =
recovery_target_time (i.e. should not replay all WAL)
c) Recovery.conf (only 4 lines)
restore_command =3D '/usr/local/pgsql/bin/pg_standby -d -s 2 -t =
/tmp/pgsql.trigger.5442 /var/pgsql/data/archive/ %f %p %r >> =
/tmp/pitr.log'
recovery_end_command =3D 'rm -f /tmp/pgsql.trigger.5442'
recovery_target_time =3D '2013-08-12 16:58:30 HKT'
recovery_target_inclusive =3D 'false'
d) Result (Not Good )=20
PG ignored the recovery_target_time, actually it just replayed all =
WAL which is not good.
- Log :=20
LOG: database system was interrupted; last known up at =
2013-08-12 17:07:28 HKT
LOG: starting point-in-time recovery to 2013-08-12 =
16:58:30+08 <=3D=3D showing =
the point-in-time of the recovery=20
Trigger file: <not set>
Waiting for WAL file: 00000001000000AD0000000B
WAL file path: =
/var/pgsql/data/archive//00000001000000AD0000000B
Restoring to: pg_xlog/RECOVERYXLOG
Sleep interval: 2 seconds
Max wait interval: 0 forever
Command for restore: cp =
"/var/pgsql/data/archive//00000001000000AD0000000B" =
"pg_xlog/RECOVERYXLOG"
Keep archive history: 000000000000000000000000 and later
running restore: OK
- check tables from psql:
postgres=3D# \d
List of relations
Schema | Name | Type | Owner =20
--------+--------+-------+----------
public | test22 | table | postgres
public | test23 | table | postgres
public | test25 | table | postgres
public | test26 | table | postgres =
=
<=3D=3D=3D this table was created at 17:01:53 and should not be =
restored
(4 rows)
postgres=3D# select count(1) from test26;
count =20
---------
2600000
(1 row)
I am new to PITR, can you please advise if this is a bug and any work =
around?
regards