Re: pg_standby and continuous recovery

I tried pg_standby with PostgreSQL 8.3. But the result is the same. If
I create trigger file to finish recovery and restart recovering by
renaming recovery.done as recover.conf

I see the following line in the standby.log

Command for restore : ln -s -f "/pgarsiv/00000002.history"
"pg_xlog/RECOVERYHISTORY"

As you can see history file refers to the 000000010000000000000008
file (starting with . 00000001)
# cat /pgarsiv/00000002.history
1 000000010000000000000008 before transaction 0 at
2000-01-01 02:00:00+02

So next file should be "000000010000000000000009" (starting with
00000001) but pg_standby is looking for 000000020000000000000009
file (starting with 00000002).

Trigger file : /tmp/pgsql.trigger.5442
Waiting for WAL file : 000000020000000000000009
WAL file path : /pgarsiv/000000020000000000000009
Restoring to... : pg_xlog/RECOVERYXLOG
Sleep interval : 2 seconds
Max wait interval : 0 forever
Command for restore : ln -s -f "/pgarsiv/000000020000000000000009"
"pg_xlog/RECOVERYXLOG"
Keep archive history : 000000000000000000000000 and late[ OK ]

As you can see below 000000020000000000000009 file will never exist in
the archivedirectory. So new wal files will never be restored by
pg_standby.
# ls -1 /pgarsiv/
000000010000000000000001
000000010000000000000001.00000068.backup
000000010000000000000002
000000010000000000000003
000000010000000000000004
000000010000000000000005
000000010000000000000006
000000010000000000000007
000000010000000000000008
000000010000000000000009
00000001000000000000000A

I also tried to cleanup pg_xlog directory at standby server but the
result is the same!

Any idea?
On Thu, Oct 16, 2008 at 8:25 PM, Julio Leyva <jcleyva(at)hotmail(dot)com> wrote:
>
> You better move to postgresql 8.3
> PITR works better in that version
> WE have that in our system and so far ok
>
>
>> Date: Thu, 16 Oct 2008 11:18:21 +0300
>> From: ibrahim(dot)harrani(at)gmail(dot)com
>> To: pgsql-admin(at)postgresql(dot)org
>> Subject: [ADMIN] pg_standby and continuous recovery
>>
>> Hello,
>>
>> I am using pg_standby with PostgreSQL 8.2.4.
>> I configured master and standby server according to the articles at
>>
>> http://michsan.blogspot.com/2008/08/using-pgstandby-for-high-availability.html
>> and http://www.postgresql.org/docs/8.3/static/pgstandby.html.
>>
>> my recovery.conf file is like following:
>> # more recovery.conf
>> restore_command = 'pg_standby -l -d -s 2 -k 40 -t
>> /tmp/pgsql.trigger.5442 /pgarsiv %f %p %r 2>>standby.log'
>> [root(at)mysql-ndb2 data]#
>>
>>
>> I can successfully recover and start the standby server. I can see
>> that the database is completely restored. (recovery.conf was renamed
>> as recovery.done)
>> But I would like to continue recovering from last recovery point
>> restored by pg_standby without starting from scratch.
>> Can I do this? If the answer is positve, which procedure should I apply?
>> After recovering standby server. I have the following files in the
>> pg_xlog directory.
>> # ls -l
>> total 16412
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:51
>> 0000000200000000000000CA
>> lrwxrwxrwx 1 postgres postgres 33 Oct 16 12:30
>> 0000000200000000000000CB -> /pgarsiv/0000000100000000000000C9
>> -rw------- 1 postgres postgres 75 Oct 16 12:30 00000002.history
>> drwx------ 2 postgres postgres 4096 Oct 16 12:30 archive_status
>> lrwxrwxrwx 1 postgres postgres 25 Oct 16 12:54 RECOVERYHISTORY
>> -> /pgarsiv/00000002.history
>>
>> As you can see below, all archive file names are staring with "00000001"
>>
>> # ls -l /pgarsiv/
>> total 262468
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:03
>> 0000000100000000000000C3
>> -rw------- 1 postgres postgres 241 Oct 16 12:03
>> 0000000100000000000000C3.00000070.backup
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:09
>> 0000000100000000000000C4
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:13
>> 0000000100000000000000C5
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:19
>> 0000000100000000000000C6
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:21
>> 0000000100000000000000C7
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:23
>> 0000000100000000000000C8
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:25
>> 0000000100000000000000C9
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:29
>> 0000000100000000000000CA
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:33
>> 0000000100000000000000CB
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:39
>> 0000000100000000000000CC
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:43
>> 0000000100000000000000CD
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:47
>> 0000000100000000000000CE
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:49
>> 0000000100000000000000CF
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:53
>> 0000000100000000000000D0
>> -rw------- 1 postgres postgres 16777216 Oct 16 12:59
>> 0000000100000000000000D1
>> -rw------- 1 postgres postgres 16777216 Oct 16 13:03
>> 0000000100000000000000D2
>>
>> But pg_standby is looking for the files name starting with 00000002!
>>
>> Trigger file : /tmp/pgsql.trigger.5442
>> Waiting for WAL file : 00000002.history
>> WAL file path : /pgarsiv/00000002.history
>> Restoring to... : pg_xlog/RECOVERYHISTORY
>> Sleep interval : 2 seconds
>> Max wait interval : 0 forever
>> Command for restore : ln -s -f "/pgarsiv/00000002.history"
>> "pg_xlog/RECOVERYHISTORY"
>> Keep archive history : %r and later
>> running restore : OK
>> Trigger file : /tmp/pgsql.trigger.5442
>> Waiting for WAL file : 0000000200000000000000CA
>> WAL file path : /pgarsiv/0000000200000000000000CA
>> Restoring to... : pg_xlog/RECOVERYXLOG
>> Sleep interval : 2 seconds
>> Max wait interval : 0 forever
>> Command for restore : ln -s -f "/pgarsiv/0000000200000000000000CA"
>> "pg_xlog/RECOVERYXLOG"
>> Keep archive history : %r and later
>>
>> Any help is appreciated
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>