Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E9EE3632A28 for ; Fri, 6 Mar 2009 11:33:17 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51071-08 for ; Fri, 6 Mar 2009 11:33:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail1.chappy.com (mail1.chappy.com [67.132.255.10]) by mail.postgresql.org (Postfix) with ESMTP id E5C70632CBB for ; Fri, 6 Mar 2009 11:33:08 -0400 (AST) Received: from nas01.chappy.com (nas01.chappy.com [192.168.40.21]) by mail1.chappy.com (Postfix) with ESMTP id 5A2EE36000 for ; Fri, 6 Mar 2009 10:31:27 -0500 (EST) Received: from tech-pc04.chappy.com (tech-pc04.chappy.com [192.168.105.223]) by nas01.chappy.com (Postfix) with ESMTP id 469AE63F78 for ; Fri, 6 Mar 2009 10:31:27 -0500 (EST) From: Yauheni Labko Reply-To: yyl@chappy.com Organization: Chapdelaine & Co To: pgsql-admin@postgresql.org Subject: Re: standby waiting for what? Date: Fri, 6 Mar 2009 10:31:26 -0500 User-Agent: KMail/1.9.9 References: <20090304200612.GA25611@cns.vt.edu> <200903051004.44932.yyl@chappy.com> <20090305195249.GC1068@cns.vt.edu> In-Reply-To: <20090305195249.GC1068@cns.vt.edu> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200903061031.27058.yyl@chappy.com> X-Chappy-MailScanner: Found to be clean X-Chappy-MailScanner-SpamCheck: not spam (whitelisted), SpamAssassin (score=-4.399, required 5, autolearn=not spam, ALL_TRUSTED -1.80, BAYES_00 -2.60) X-Chappy-MailScanner-From: yyl@chappy.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/89 X-Sequence-Number: 31589 > 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