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;
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 <== 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,
- Set recovery_target_time = '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 = '/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 = 'rm -f /tmp/pgsql.trigger.5442'
recovery_target_time = '2013-08-12 16:58:30 HKT'
recovery_target_inclusive = 'false'
d) Result (Not Good )
PG ignored the recovery_target_time, actually it just replayed all WAL
which is not good.
- Log :
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
<== showing the point-in-time of the
recovery
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=# \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | test22 | table | postgres
public | test23 | table | postgres
public | test25 | table | postgres
public | test26 | table | postgres
<=== this table was created at 17:01:53 and should not be restored
(4 rows)
postgres=# select count(1) from test26;
count
---------
2600000
(1 row)
I am new to PITR, can you please advise if this is a bug and any work around?
regards
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
