DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:content-type;
	b=xId28aryrpXzJC/ggqHI0IA8z/z0BsJHw5tHgsYfeCAykkMOp1ZvGHcdSJtKIlh/qJ
	3skLpjqrf4AZRM870Mh+Ov+PixMI3zWYt4n5Ur/nRqkQch8cls7s5e31M8dvW0I+c8zj
	k1w4KH4zzp4l+cQHmKF+eW4lq9hscNqml3k0I=
MIME-Version: 1.0
In-Reply-To: <BANLkTinvBfV3MuL6k3PJWxbhTg2qwW4KQg@mail.gmail.com>
References: <BANLkTinvBfV3MuL6k3PJWxbhTg2qwW4KQg@mail.gmail.com>
Date: Mon, 11 Apr 2011 17:56:23 +0200
Message-ID: <BANLkTiksRW05KZtJDs6Wf75wKD2R+bjGUg@mail.gmail.com>
Subject: Re: multiple hot standby streaming replication scenario with
	"rotating" the primary server
From: Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Apr 7, 2011 at 11:40 AM, Gerhard Hintermayer
<gerhard.hintermayer@gmail.com> wrote:
> Hi,
> I'm trying to set up at least 3 servers using hot standby streaming
> replication. I'd like to have one primary and 2 secondary (on 2
> different locations in case of a desaster in the server room).
> A primary
> B secondary 1
> C secondary 2 (on a different location that A and B)
>
> Are the following actions in case of recovering to any of the standby
> servers (B or C) correct ?
>
> 1. primary A crashes/maintenance or whatever
> 2. creating the trigger file on B brings this server to life.
> 3. stop server on C
> 4. make base backup on B and install it on C and A (if already available)
> 5. change primary_conninfo in recovery.conf on A,C to reflect new primary B
> 6. start server on A,C
>
I managed to change 4. to use rsync, which is really faster that
zip/transfer/extract, even though index files have a large impact on
the transfered data volume. (my db is ~ 7GB on disk, and even though I
made minimal changes, approx 2.5 GB of data is transfered when making
a new base backup with rsyncing over the existing data dir. tI roughly
takes 10 min to rsync (over 100Mbit LAN).

Unfortunately I had to insert
2.1 reindex database [for all databases] after creating the trigger
file on the new dedicated primary, which takes another 10-20 minutes
to make any queries to the DB working (since I heavyly depend on
indices. So I have ~ 30 mins to get my new primary up and ready for
production :-(
Is there anything how I can speedup things.

I know that I have some large tables, where mos't of the reindex time
is, spent, so reindex these few tables later would be an option, but
that special treatment has to be maintained for new tables, so I'd
prefer a more generic way.

regards
Gerhard