Re: standby waiting for what? - Mailing list pgsql-admin

> I'm curious why you are focused on the archive on the primary?

Postgres docs do not say that during initial recovery you need a backup
history file. But It is required during initial recovery.  That's why I am
focused on it.
You lost 00000002000000000000001C.00512178.backup. That means your archiving
is working wrong.

I simulated your situation.

Standby server (the WAL files location)
# ls -l /pg_restore/pg_xlog/backup_03012009_0200/
total 49216
-rw------- 1 postgres postgres 16777216 2009-03-01 02:03
0000000100000005000000B7
-rw------- 1 postgres postgres      256 2009-03-01 02:03
__0000000100000005000000B7.00718400.backup
-rw------- 1 postgres postgres 16777216 2009-03-01 12:02
0000000100000005000000B8
-rw------- 1 postgres postgres 16777216 2009-03-01 22:01
0000000100000005000000B9

My restore command (debug is on):
# cat recovery.conf
restore_command = '/opt/postgresplus/8.3/bin/pg_standby -d -r 3 -w 0 -s
10 -t /opt/postgresplus/8.3/data/pg_trigger /pg_restore/pg_xlog/backup_03012009_0200 %f %p'

The piece of serverlog (pay attention to the backup history filename and
compare it with the ls -l output before):

Trigger file            : /opt/postgresplus/8.3/data/pg_trigger
Waiting for WAL file    : 0000000100000005000000B7.00718400.backup
WAL file
path           : /pg_restore/pg_xlog/backup_03012009_0200/0000000100000005000000B7.00718400.backup
Restoring to...         : pg_xlog/RECOVERYHISTORY
Sleep interval          : 10 seconds
Max wait interval       : 0 forever
Command for restore     :
cp "/pg_restore/pg_xlog/backup_03012009_0200/0000000100000005000000B7.00718400.backup" "pg_xlog/RECOVERYHISTORY"
Keep archive history    : No cleanup required
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...



Now I renamed __0000000100000005000000B7.00718400.backup to
0000000100000005000000B7.00718400.backup and restarted the standby server:

Trigger file            : /opt/postgresplus/8.3/data/pg_trigger
Waiting for WAL file    : 0000000100000005000000B7.00718400.backup
WAL file
path           : /pg_restore/pg_xlog/backup_03012009_0200/0000000100000005000000B7.00718400.backup
Restoring to...         : pg_xlog/RECOVERYHISTORY
Sleep interval          : 10 seconds
Max wait interval       : 0 forever
Command for restore     :
cp "/pg_restore/pg_xlog/backup_03012009_0200/0000000100000005000000B7.00718400.backup" "pg_xlog/RECOVERYHISTORY"
Keep archive history    : No cleanup required
running restore         : OKLOG:  restored log
file "0000000100000005000000B7.00718400.backup" from archive

Trigger file            : /opt/postgresplus/8.3/data/pg_trigger
Waiting for WAL file    : 0000000100000005000000B7
WAL file
path           : /pg_restore/pg_xlog/backup_03012009_0200/0000000100000005000000B7
Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 10 seconds
Max wait interval       : 0 forever
Command for restore     :
cp "/pg_restore/pg_xlog/backup_03012009_0200/0000000100000005000000B7" "pg_xlog/RECOVERYXLOG"
Keep archive history    : No cleanup required
running restore         : OKLOG:  restored log file "0000000100000005000000B7"
from archive
LOG:  automatic recovery in progress
LOG:  redo starts at 5/B7718440

.... (recovering from other WAL files)

WAL file not present yet. Checking for trigger file...


You see that the database server is looking for the history file at first
which is not necessary and the next file which is a backup history file.
That's why your standby server is waiting for it.

Yauheni Labko (Eugene Lobko)
Junior System Administrator
Chapdelaine & Co.
(212)208-9150