From: Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: multiple hot standby streaming replication scenario with "rotating" the primary server
Date: Mon, 11 Apr 2011 17:56:23 +0200
Message-ID: <BANLkTiksRW05KZtJDs6Wf75wKD2R+bjGUg@mail.gmail.com> (raw)
In-Reply-To: <BANLkTinvBfV3MuL6k3PJWxbhTg2qwW4KQg@mail.gmail.com>
References: <BANLkTinvBfV3MuL6k3PJWxbhTg2qwW4KQg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gerhard.hintermayer@gmail.com
Subject: Re: multiple hot standby streaming replication scenario with "rotating" the primary server
In-Reply-To: <BANLkTiksRW05KZtJDs6Wf75wKD2R+bjGUg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox