Recovery.conf PITR by recovery_target_time

From: "ascot(dot)moss(at)gmail(dot)com" <ascot(dot)moss(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Cc: ascot(dot)moss(at)gmail(dot)com
Subject: Recovery.conf PITR by recovery_target_time
Date: 2013-08-12 10:34:23
Message-ID: B2E2466C-64B7-4749-804D-D22D0C18408F@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Pavel Raiskup 2013-08-12 10:37:29 [PATCH] Re: [BUGS] BUG #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve
Previous Message Romain Billon-Grand 2013-08-12 06:01:44 Re: BUG #8335: trim() un-document behaviour

Browse pgsql-general by date

  From Date Subject
Next Message ascot.moss@gmail.com 2013-08-12 11:17:49 Re: Replication delay
Previous Message raghu ram 2013-08-12 09:31:53 Re: Trigger to update table info