Forums PostgreSQL.fr

Bonjour,

Nous avons 2 serveurs PostgreSQL en version 8.4.2.1

Sur le serveur primaire:
archive_mode = on
archive_command = 'copy E:\\PostgreSQL\\8.4\\data\\%p \\\\192.168.50.62\\LogShipping\\%f'
archive_timeout = 120

Sur le serveur secondaire:
restore_command = '"C:\recovery_command.bat" %f %p %r'

Contenu du fichier recovery_command.bat

@echo off
@set nextwalfile=%1
@set xlogfilepath=%2
@set restartwalfile=%3
@echo CETLOG:  restore_command >> E:\PostgreSQL\8.4\data\pg_bckplog\standby.log
@"C:\Program Files (x86)\PostgreSQL\8.4\bin\pg_standby.exe" -d -s 5 -t E:\PostgreSQL\8.4\data\stoprestore.file D:\LogShipping %nextwalfile% E:\PostgreSQL\8.4\data\%xlogfilepath% %restartwalfile% 2>> E:\PostgreSQL\8.4\data\pg_bckplog\standby.log

Au bout d'un certain, temps nous avons ce type d'erreur dans le fichier de log de postgre
CESTLOG:  restored log file "0000000300000222000000CD" from archive
CESTLOG:  restored log file "0000000300000222000000CE" from archive
CESTLOG:  restored log file "0000000300000222000000CF" from archive
CESTLOG:  restored log file "0000000300000222000000D0" from archive
CESTLOG:  restored log file "0000000300000222000000D1" from archive
CESTLOG:  restored log file "0000000300000222000000D2" from archive
CESTLOG:  restored log file "0000000300000222000000D3" from archive
CESTLOG:  restored log file "0000000300000222000000D4" from archive
CESTLOG:  could not open file "pg_xlog/0000000300000222000000D5" (log file 546, segment 213): No such file or directory
CESTLOG:  redo done at 222/D4128940
CESTLOG:  last completed transaction was at log time 2010-09-02 07:55:36.407+02
CESTLOG:  restored log file "0000000300000222000000D4" from archive
CESTLOG:  selected new timeline ID: 4
CESTLOG:  archive recovery complete
CESTLOG:  database system is ready to accept connections
CESTLOG:  autovacuum launcher started

Et voilà l'erreur dans le fichier standby.log
[2010-09-02 07:56:32] CETLOG:  restore_command
Trigger file         : E:\PostgreSQL\8.4\data\stoprestore.file
Waiting for WAL file    : 0000000300000222000000D5
WAL file path        : D:\LogShipping\0000000300000222000000D5
Restoring to        : E:\PostgreSQL\8.4\data\pg_xlog\RECOVERYXLOG
Sleep interval        : 5 seconds
Max wait interval    : 0 forever
Command for restore    : copy "D:\LogShipping\0000000300000222000000D5" "E:\PostgreSQL\8.4\data\pg_xlog\RECOVERYXLOG"
Keep archive history    : 0000000300000222000000CD and later
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...
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...
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...
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...
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...
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...
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...
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
running restore        :Le processus ne peut pas acc‚der au fichier car ce fichier est utilis‚ par un autre processus.
Le processus ne peut pas acc‚der au fichier car ce fichier est utilis‚ par un autre processus.
Le processus ne peut pas acc‚der au fichier car ce fichier est utilis‚ par un autre processus.
Le processus ne peut pas acc‚der au fichier car ce fichier est utilis‚ par un autre processus.
not restored
[2010-09-02 07:59:02] CETLOG:  restore_command
Trigger file         : E:\PostgreSQL\8.4\data\stoprestore.file
Waiting for WAL file    : 0000000300000222000000D4
WAL file path        : D:\LogShipping\0000000300000222000000D4
Restoring to        : E:\PostgreSQL\8.4\data\pg_xlog\RECOVERYXLOG
Sleep interval        : 5 seconds
Max wait interval    : 0 forever
Command for restore    : copy "D:\LogShipping\0000000300000222000000D4" "E:\PostgreSQL\8.4\data\pg_xlog\RECOVERYXLOG"
Keep archive history    : 000000000000000000000000 and later
running restore        : OK

[2010-09-02 07:59:07] CETLOG:  restore_command
Trigger file         : E:\PostgreSQL\8.4\data\stoprestore.file
Waiting for WAL file    : 00000004.history
WAL file path        : D:\LogShipping\00000004.history
Restoring to        : E:\PostgreSQL\8.4\data\pg_xlog\RECOVERYHISTORY
Sleep interval        : 5 seconds
Max wait interval    : 0 forever
Command for restore    : copy "D:\LogShipping\00000004.history" "E:\PostgreSQL\8.4\data\pg_xlog\RECOVERYHISTORY"
Keep archive history    : 000000000000000000000000 and later
running restore        :not restored
history file not found
[2010-09-02 07:59:37] CETLOG:  restore_command
Trigger file         : E:\PostgreSQL\8.4\data\stoprestore.file
Waiting for WAL file    : 00000003.history
WAL file path        : D:\LogShipping\00000003.history
Restoring to        : E:\PostgreSQL\8.4\data\pg_xlog\RECOVERYHISTORY
Sleep interval        : 5 seconds
Max wait interval    : 0 forever
Command for restore    : copy "D:\LogShipping\00000003.history" "E:\PostgreSQL\8.4\data\pg_xlog\RECOVERYHISTORY"
Keep archive history    : 000000000000000000000000 and later
running restore        :not restored
history file not found

Un fichier dans le dossier D:\LogShipping n'est à priori pas lisible.
Nous supposons qu'un fichier est encore en cours de transfert au moment où postgre essaye de le lire (malgré la ligne gigabit entre les deux sites).

Est-ce bien le problème ?
Y a-t-il une solution ?

Merci d'avance