Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id B5C961337BB7 for ; Thu, 7 Apr 2011 06:40:14 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 81595-07 for ; Thu, 7 Apr 2011 09:40:05 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ey0-f174.google.com (mail-ey0-f174.google.com [209.85.215.174]) by mail.postgresql.org (Postfix) with ESMTP id CC4BC1337B2F for ; Thu, 7 Apr 2011 06:40:03 -0300 (ADT) Received: by eyx24 with SMTP id 24so724925eyx.19 for ; Thu, 07 Apr 2011 02:40:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:date:message-id:subject:from:to :content-type; bh=rRN/7XEdpVEbKK5zsvDnuToIg8HSicvXROcZFWosErY=; b=iBbBKbYuZ2HB3h1SGI1R8gkDqQwhlw9PrtyD8vCOu1DWgEBsPOVktm4hmkZj2hbIKO V7VqzuIEpgAusD40mWNrptt6tiBK8Qb9qZpq+0pI43NQKnZq+kPkSF2bnhLQRB/hQKTx wto+6Vs9HfRW7OipV6ejIntfMRkT2cetlbrCk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=NPwIVeYxRZBjXjDa4O9B13Fgm6o100g+VyQd55lrCmWszwB+USNldk3/uQM3M00Zdf vFYOI+j3JmkL5GHT2TFb5CYJUMROpksaRC+uf6tLnvZjdkFcVvkUEVJJNPHP5hZ7yRFI EEuPpIBI3IBCje0AmWSzWLWu3FwB6wB8UcWuQ= MIME-Version: 1.0 Received: by 10.213.112.195 with SMTP id x3mr226985ebp.146.1302169202621; Thu, 07 Apr 2011 02:40:02 -0700 (PDT) Received: by 10.213.14.210 with HTTP; Thu, 7 Apr 2011 02:40:02 -0700 (PDT) Date: Thu, 7 Apr 2011 11:40:02 +0200 Message-ID: Subject: multiple hot standby streaming replication scenario with "rotating" the primary server From: Gerhard Hintermayer To: pgsql-admin Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.42 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, FS_REPLICA=3.599, SARE_SPEC_REPLICA=0.72 X-Spam-Level: ** X-Archive-Number: 201104/54 X-Sequence-Number: 37235 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 These actions should all be automated, since I have no redundancy between step 1 and 6, so in case something happens to the just switched over new primary, I have no up to date server to bring up from standby. Making base backup takes ~10 mins, installing probably also about 10 mins, so I have 20 minutes, where nothing should happen to B :-( But maybe I'm also missing something. regards Gerhard