X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 7F49611F60B8 for ; Fri, 28 Apr 2006 12:23:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 41921-02 for ; Fri, 28 Apr 2006 12:23:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sydney.mailnetwork.co.uk (server88-208-192-113.live-servers.net [88.208.192.113]) by postgresql.org (Postfix) with ESMTP id 007F011F602A for ; Fri, 28 Apr 2006 12:23:00 -0300 (ADT) Received: from [213.146.143.243] (port=53284 helo=it020361) by sydney.mailnetwork.co.uk with esmtpa (Exim 4.60) (envelope-from ) id 1FZUo3-0007jv-DM for pgsql-admin@postgresql.org; Fri, 28 Apr 2006 16:22:55 +0100 Reply-To: From: "Andy Shellam" To: Subject: WAL recovery question - 0000001.history Date: Fri, 28 Apr 2006 16:23:01 +0100 Organization: the mail network MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcZq16Lwp1oToLp8TQSpEKfDtL+2xw== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 Message-Id: <20060428152301.007F011F602A@postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/334 X-Sequence-Number: 21503 I've developed and am now testing a new "rolling-WAL" script, and have noticed something a little peculiar with Postgres 8.1.3. Basically I've taken a dump of my live database directory (between pg_start_backup and pg_stop_backup) calls - shipped this to my standby, set up a recovery.conf file (which calls the rolling-WAL script). This script is designed to wait until the log file arrives, or a flag file is set to return code 1 to postgres. However as soon as the recovery starts, the script is waiting for a file called 00000001.history, which will never arrive because it was never generated on the live box since the base backup was taken. So, what I did, was to touch this file (i.e. so it existed, but was empty) - at which point my script recovered it, then PG ignored it and asked for the correct backup files, as shown in the log below. Note I intend to make this script public once testing has been carried out - if anyone is interested in testing, please let me know. The "DEBUG" lines have been generated by my script, not Postgresql. --- START LOG OUTPUT --- 2006-04-28 16:07:33 BST LOG: database system was interrupted at 2006-04-19 10:48:50 BST 2006-04-28 16:07:33 BST LOG: starting archive recovery 2006-04-28 16:07:33 BST LOG: restore_command = "/mndata/scripts/wal_log_recovery.sh /mndata/archive/xlog_transfer/%f %p" DEBUG: Recovering /mndata/archive/xlog_transfer/00000001.history to pg_xlog/RECOVERYHISTORY DEBUG: WAL log /mndata/archive/xlog_transfer/00000001.history does not exist DEBUG: Checking for flag file at /tmp/recoverdb.flag DEBUG: Flag file does not exist DEBUG: 30s to wait before next check --- > I touched the 00000001.history file here < --- DEBUG: Source file /mndata/archive/xlog_transfer/00000001.history exists DEBUG: copy command returned: 0 DEBUG: Returning code 0 to PostgreSQL 2006-04-28 16:10:03 BST LOG: restored log file "00000001.history" from archive DEBUG: Recovering /mndata/archive/xlog_transfer/000000010000000900000009.009FF34C.backup to pg_xlog/RECOVERYHISTORY DEBUG: WAL log /mndata/archive/xlog_transfer/000000010000000900000009.009FF34C.backup does not exist DEBUG: Checking for flag file at /tmp/recoverdb.flag DEBUG: Flag file does not exist DEBUG: 30s to wait before next check --- END LOGOUTPUT --- There was an error during recovering this file: 000000010000000900000009.009FF34C.backup (as for some reason copy returns 0 even when the file has failed to be moved - I'll have to build this check in myself.) So PG carried on looking for 000000010000000900000009 instead. Does Postgres not use the *.history and *.backup files during the recovery - so it's not going to be harmful to the recovery if these files are empty? Thanks Andy