Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id EB9BC2E0104 for ; Mon, 10 Mar 2008 19:23:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 64093-01 for ; Mon, 10 Mar 2008 19:23:03 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web30401.mail.mud.yahoo.com (web30401.mail.mud.yahoo.com [68.142.200.104]) by postgresql.org (Postfix) with SMTP id 373592E00FE for ; Mon, 10 Mar 2008 19:23:08 -0300 (ADT) Received: (qmail 39217 invoked by uid 60001); 10 Mar 2008 22:23:06 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=JHisQUGaNWKzGbxXFUg8dDH/1rlgsk52Cux6d5GjN7St6B0tltGay4JHRyTZ2xwzZNv8QU54eNuV0q0k6VbWAnAuhtAwmUbOnVjhlFIK5EGNLoG4am/84f7JjBkcDZMxm9AY+NJbZaEKCJoTHjAPcLc3u6B/TUEEz4OrWuY0Nng=; X-YMail-OSG: TuqJJOUVM1n.kRTsPFb0IvpX.F7Rr_2SV8y4PrMpC84vY5SlVDMhZpjT91LjdVarBXP7P9C2CBmnzPSgpVjyBsLuOeF2SjPAIh76Vq9rRtiWF3mxPN8- Received: from [129.128.11.174] by web30401.mail.mud.yahoo.com via HTTP; Mon, 10 Mar 2008 15:23:06 PDT Date: Mon, 10 Mar 2008 15:23:06 -0700 (PDT) From: Grzegorz Dostatni Subject: WAL Archiving, warm standby - edge cases questions: To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <322877.37825.qm@web30401.mail.mud.yahoo.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: 200803/80 X-Sequence-Number: 28563 Assume we have a primary database server and a warm standby. WAL files get shipped over to the standby and applied as they arrive. Questions: 1. I know the recommendation is to fail over to the standby and re-configure the primary as a new standby. Is it possible to do that without any data loss? I know I can copy un-archived WAL files to the archive area, but is that guaranteed not to result in any data loss? I suppose that would require turning on the option that forces a sync of the WAL file before finishing a transaction. I guess I'm asking as to the recommended procedure that minimizes data loss / outage window when purposely failing over to the standby. 2. How would you deal with archiving the new master DB server? Should there be a second WAL archive area? If you copied un-archived WAL files, and the DB server tries to archive them - you're going to have a problem. The documentation says that the archive script should not overwrite files silently. Can those un-archived WAL files be copied to the pg_xlog directory directly? Will postgresql look in there first before activating the restore_command? 3. If you want to have an automatic failover to the standby - does that mean that your standby needs to archive WAL files as well? Otherwise you'd have a window where the current primary is not doing any archiving. OR you'd have 2 archive copies of your WAL files. Mostly I'm looking for input from people who are running warm standby in production environment, especially with respect to any gotchas. I did manage to successfully setup a standby server and I am able to fail over to it. I found the messages on this list that recommend failing over periodically, and I am wondering if people are accepting data loss for what should be a maintenance event. Thanks in advance for any input. As I mentioned before, I believe I have the base case covered. Just trying to play with a bit of *what if*. Greg ____________________________________________________________________________________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs