Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 09E019FB589 for ; Fri, 3 Aug 2007 21:17:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 34773-04 for ; Fri, 3 Aug 2007 21:17:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id C32F39FB600 for ; Fri, 3 Aug 2007 21:17:20 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 3C83F56453; Fri, 3 Aug 2007 19:17:19 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Fri, 3 Aug 2007 19:17:17 -0500 Date: Fri, 3 Aug 2007 19:17:17 -0500 From: Decibel! To: Dimitri Gritsenko Cc: pgsql-admin@postgresql.org Subject: Re: Wal standbies switchover Message-ID: <20070804001717.GB25704@nasby.net> References: <79eb93740708020126i5ac550bdw7a42cec1930793e7@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="/cFG30PKHi9lwv6o" Content-Disposition: inline In-Reply-To: <79eb93740708020126i5ac550bdw7a42cec1930793e7@mail.gmail.com> X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:070804:dev789@gmail.com::Xb0uic/1Wbsp75JQ:00ZnJ X-Hashcash: 1:20:070804:pgsql-admin@postgresql.org::hCdB2o3g82KtRBlc:00000000000 0000000000000000000000000IyR X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/26 X-Sequence-Number: 26357 --/cFG30PKHi9lwv6o Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Thu, Aug 02, 2007 at 12:26:47PM +0400, Dimitri Gritsenko wrote: > 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. >=20 > 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= =2E, > 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) Hrm... I'm not sure if that's OK... I seem to recall something about a difference in WAL file numbering when a PITR recovery is done (promoting slave to master in your case). I suggest trying it; if it works I don't expect there to be any hidden issues. > 5. What happens on host 1 (former master) is a question. It needs to beco= me > 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 , gett= ing > new wal files? No, you can't take a data directory that's been run outside of recovery mode and push it back into a recovery mode (which is essentially what you're asking for). You'll need to rsync from host 2 to host 1 and start recovery from there. There's a very critical thing you've overlooked, too. You have to ensure that slaves will *only* get archived logs from the master. This means that if you switch masters, you have to guarantee that the old master won't be archiving any logfiles anymore. Another possibility would be to ensure that things are configured so that each machine will archive to a different location. --=20 Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) --/cFG30PKHi9lwv6o Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.3 (FreeBSD) iD8DBQFGs8WNdO30qud8SkgRAsIdAKCpUrETMuMKRVOYU0ae7HRMNKA5bQCgjDzU SLw5qW0mLhMspNyqeQWSJ64= =7I5s -----END PGP SIGNATURE----- --/cFG30PKHi9lwv6o--