Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id AE5EC9FB3F8 for ; Thu, 2 Aug 2007 05:26:58 -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 41315-06 for ; Thu, 2 Aug 2007 05:26:45 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id 218069FA28D for ; Thu, 2 Aug 2007 05:26:48 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so340355ugf for ; Thu, 02 Aug 2007 01:26:47 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=bkWPtjBCMOGIcGniGGmPyDR1rdS/GTocKmOwoJmPRf6BXy1h4JIhUEomymdnWr8SSCFwbgMX5Wi6ZhuEfzGHFABIF1j4Widpenp+Niu7wDbv0wXBh5lrdqfg71ojc6B1qIOqarOqhA+6Ntv/oK4Qaxbeog0jlnS4cy+q7y++CfU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=DckI7Hk3DeSb1PPhTA3jdaszFRahSZ5jwiFLWwQfq8SKJhG0L3rUpvS/+CDdUOwspDzmUlAn696izs3Uf2C2DuM15BDBOtwi13AcGDiMxf7vvbTa4mB5Iabc4Aiq5phHg3c5tpxrfs0OZZcMi4H+MZAUELjhHYulwzlgcyELUvo= Received: by 10.78.145.5 with SMTP id s5mr462422hud.1186043207165; Thu, 02 Aug 2007 01:26:47 -0700 (PDT) Received: by 10.78.155.17 with HTTP; Thu, 2 Aug 2007 01:26:47 -0700 (PDT) Message-ID: <79eb93740708020126i5ac550bdw7a42cec1930793e7@mail.gmail.com> Date: Thu, 2 Aug 2007 12:26:47 +0400 From: "Dimitri Gritsenko" To: pgsql-admin@postgresql.org Subject: Wal standbies switchover MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_62291_20068726.1186043207137" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/16 X-Sequence-Number: 26347 ------=_Part_62291_20068726.1186043207137 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Goal - having 1 master and 2 standbies - make switchover in the way , that except minimal downtime - there was always 1 running master and 2 warm standbies. Lets say i have 3 hosts (1,2,3) , 1 master and 2 warm standbies . ( M1 SB2 SB3) Each SB gets (Master rsyncs from his side to SB) archived wal files from master and recovers in continues way untill triggered to start as master., also it gets incomplete wals from pg_xlog dir . Switchover looks for me like this. 1. M1 stops , all sync crons stopped. 2. Do last rsync of archived wals and pg_xlog to SB2 , SB3 3. SB2 triggered to start , it recovers last incomplete wals from pg_xlog and starts (becomes M2), and starts cron rsync to SB3 4. SB3 does nothing , it continues get recover wal archives which now it gets from M2. (Question - is it ok ? It looks like new archived wal files from M2 should be valid without any changes) 5. What happens on host 1 (former master) is a question. It needs to become a SB1 . Do i need to make large rsync of whole db from M2 (like during SB install) or i can just keep old data and start db in recovery mode , getting new wal files? There is a difference between SB2 and new SB1. SB2 had data_dir - corresponding to last recovered ARCHIVED file , and SB1 - to last INCOMPLETE WAL files , so its not the same. So , 2 questions on points 4 and 5. Any comments? ------=_Part_62291_20068726.1186043207137 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Goal - having 1 master and 2 standbies - make switchover in the way , that except minimal downtime - there was always 1 running master and 2 warm standbies.

Lets say i have 3 hosts (1,2,3) , 1 master and 2  warm standbies .  ( M1 SB2 SB3)
Each SB gets (Master rsyncs from his side to SB)  archived wal  files from master  and recovers in continues way untill triggered to start as master., also it gets incomplete wals from  pg_xlog dir .
Switchover looks for me  like this.
1. M1  stops , all sync crons  stopped.
2. Do last rsync of  archived wals and pg_xlog  to  SB2 , SB3
3. SB2 triggered to start , it recovers last incomplete wals from pg_xlog and starts (becomes M2), and starts cron rsync to SB3
4. SB3 does nothing , it continues get recover wal archives which now it gets from M2. (Question - is it ok ? It looks like new archived wal files from M2 should be valid without any changes)
5. What happens on host 1 (former master) is a question. It needs to become a SB1 . Do i need to make large rsync of whole db from M2 (like during SB install) or i can just keep old data and start db in recovery mode , getting new wal files?


There is a difference between SB2 and new SB1. SB2 had data_dir - corresponding to last recovered ARCHIVED file , and SB1 - to last INCOMPLETE WAL files , so its not the same.

So , 2 questions on points 4 and 5.

Any comments?


------=_Part_62291_20068726.1186043207137--