Recovery.conf PITR by recovery_target_time

Поиск
Список
Период
Сортировка
От ascot.moss@gmail.com
Тема Recovery.conf PITR by recovery_target_time
Дата
Msg-id B2E2466C-64B7-4749-804D-D22D0C18408F@gmail.com
обсуждение исходный текст
Ответы Re: Recovery.conf PITR by recovery_target_time  (Michael Paquier <michael.paquier@gmail.com>)
Список pgsql-bugs
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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Romain Billon-Grand
Дата:
Сообщение: Re: BUG #8335: trim() un-document behaviour
Следующее
От: Pavel Raiskup
Дата:
Сообщение: [PATCH] Re: BUG #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve