Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VxJ3X-0000gW-1D for pgsql-general@arkaria.postgresql.org; Sun, 29 Dec 2013 16:17:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VxJ3V-0005g1-Gv for pgsql-general@arkaria.postgresql.org; Sun, 29 Dec 2013 16:17:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VxJ3T-0005fq-1X for pgsql-general@postgresql.org; Sun, 29 Dec 2013 16:17:31 +0000 Received: from mail-qe0-f47.google.com ([209.85.128.47]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VxJ2J-0000of-IW for pgsql-general@postgresql.org; Sun, 29 Dec 2013 16:17:28 +0000 Received: by mail-qe0-f47.google.com with SMTP id t7so10609795qeb.20 for ; Sun, 29 Dec 2013 08:16:17 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc:content-type; bh=gxqUH/Ysv+sUP520u1+6EhluyhyyqK8fDzmjukUbH00=; b=Fw0n+caKEPAxgZQjzfNorhDLksV7s0kBvdSUPowe0YexlVnrwDqDgvOFPNI51MWVzK vP4F+LskfNkWgDfsQGIHmMpkDLY+swddhSPSePvrni04gr863q1iwoOxHzBhrH4FO55e GPLz1cTwu6Igq+wdz6La61lTpseqXX2MySm9ZUtQeldulN+2I3ohtbXt35uuw5QCJDnj mzdFZcn0aOjU3+5Yu97/SsVkZ40wj/VZiV/ob1ORa2SsCuhMUjOIB62yvqLZFGLrCK2o 6a95/oCFiclbAq6N3pkdCXuDBP3qGdmdIca9K2527l1yEb0vMCWH200jlQLZ+AZgTb4l M8/g== X-Gm-Message-State: ALoCoQlfznEai6otjS4egxHPOLCiLSF1ebI+ubairq7yPGgagzINkc6s1D5bx3RveY0gAF3JARh5 X-Received: by 10.224.79.74 with SMTP id o10mr100712056qak.6.1388333777744; Sun, 29 Dec 2013 08:16:17 -0800 (PST) MIME-Version: 1.0 Received: by 10.96.191.134 with HTTP; Sun, 29 Dec 2013 08:15:37 -0800 (PST) X-Originating-IP: [124.197.103.118] In-Reply-To: <20131229081927.ee3d1638e65f0f8e1d35b509@potentialtech.com> References: <52A5EB31.7070505@nybeta.com> <20131210063742.86b78bf34f9fbb968454fed3@potentialtech.com> <20131229081927.ee3d1638e65f0f8e1d35b509@potentialtech.com> From: Sameer Kumar Date: Mon, 30 Dec 2013 00:15:37 +0800 Message-ID: Subject: Re: PG replication across DataCenters To: Bill Moran Cc: Thomas Harold , Albe Laurenz , "pgsql-general@postgresql.org" Content-Type: multipart/alternative; boundary=047d7bf0e20ca7064f04eeaea5bd X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org --047d7bf0e20ca7064f04eeaea5bd Content-Type: text/plain; charset=ISO-8859-1 >> > * Quick and easy movement of the master to any of the database in >> > >> > the cluster without destroying replication. >> > >> > Again, which version? Re-mastering is made simple in v9.3. >> I'm not seeing that in the documentation. In fact, what I'm finding >> seems to suggest the opposite: that each node's master is configured >> in a config file, so in the case of a complicated replication setup, >> I would have to run around editing config files on multiple servers >> to move the master ... unless I'm missing something in the documentation. Well, the pain can be minimized if you can write some simple shell scripts for this. Or if you can have a floating/virtual IP. >>> * Seeding of new slaves without interrupting existing nodes (assuming >>> >>> your hardware has a little free capacity) >>> >>> AFAIK, streaming replication does not cause any interruption while you add >>> a new node. >>The process is still significantly more involved than Slony's subscription >>commands. In our lab setups, I've watched junior DBA's fail time and time >>again to get a proper seed with streaming replication. Try the pg_basebackup options in v9.3. Creating a streaming replica has been made easy. It's still a little painful if you want to move your WALs to a different LUW/HDD on your replica >>> I think if it's slony or streaming replication will depend on below factors: >>> >>> 1) The change-set that you want to replicate contributes how much of your >>> total change set? e.g. on a per minute basis if it's 70% or above, I will >>> recommend you to go for streaming replication >>While this is a strong argument in favor of streaming over Slony, the >>70% number seems rather arbitrary, and you're advocating that this point >>alone is enough to outweight the other advantages of Slony, which may be >>more important in a particular case. I gave an example. It will definately vary from case to case and implementation to implementation. >>> 4) To some extent your choice will be influenced by the motivation behind >>> replication, DR, HA, reporting application (esp if you are particular about >>> replicating only selective tables for reports) >>In my experience, this is usually the largest factor. >>>To some extent your choice will be influenced <<< Let me correct myself: To a large extent your choice will be influnced :) >>Once argument in favor of streaming that you missed is when you have no >>control over the schema (for example, when it's 3rd party, like an openfire >>database). In those cases, the application frequently omits things like >>primary keys (which are required for slony) and has an upgrade process that >>assumes it can change database tables without impacting anything else. That's a good one and quite apt too! Regards Sameer Ashnik Pte. Ltd. Singapore --047d7bf0e20ca7064f04eeaea5bd Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
= >> >= * Quick and easy movement of the master to any of the database in
>> >=
>> > =A0 the cluster without destroyi= ng replication.
>> >
>> > Aga= in, which version? Re-mastering is made simple in v9.3.

>> I'm not see= ing that in the documentation. =A0In fact, what I'm finding
>> seems to suggest the opposite: that each node'= s master is configured
>> in a config fil= e, so in the case of a complicated replication setup,
>> I would have to run around editing config files on= multiple servers
>> to move the master .= .. unless I'm missing something in the documentation.

Well, the pain can be mi= nimized if you can write some simple shell scripts for this. Or if you can = have a floating/virtual IP.


>>> = * Seeding of new slaves without interrupting existing nodes (assuming
>>>
>>> =A0 your hardware has a little free capacity)<= /font>
>>>
>>> AFAIK, streaming replication does not cause an= y interruption while you add
>>> a new= node.

>>The process is s= till significantly more involved than Slony's subscription
>>commands. =A0In our lab setups, I've watched ju= nior DBA's fail time and time
= >>again= to get a proper seed with streaming replication.

Try the pg_basebackup op= tions in v9.3. Creating a streaming replica has been made easy. It's st= ill a little painful if you want to move your WALs to a different LUW/HDD o= n your replica

>>> I think if = it's slony or streaming replication will depend on below factors:
>>>
>>> 1) Th= e change-set that you want to replicate contributes how much of your=
>>> total change set? e.g. on a per minute basis i= f it's 70% or above, I will
>>> re= commend you to go for streaming replication

>>While this is a = strong argument in favor of streaming over Slony, the
>>70% number seems rather arbitrary, and you're a= dvocating that this point
>>alone is enou= gh to outweight the other advantages of Slony, which may be
>>more important in a particular case.

I gave an example. It will definately vary fro= m case to case and implementation to implementation.


>>> 4) To som= e extent your choice will be influenced by the motivation behind
>>> replication, DR, HA, reporting application (es= p if you are particular about
>>> repl= icating only selective tables for reports)

>>In my experience= , this is usually the largest factor.


>>>T= o some extent your choice will be influenced <<<
Let me correct myself:
To a large extent your choice will be influnced :)


= >>Once = argument in favor of streaming that you missed is when you have no
>>control over the schema (for example, when it's= 3rd party, like an openfire
>>database).= =A0In those cases, the application frequently omits things like
>>primary keys (which are required for slony) and has= an upgrade process that
>>assumes it can= change database tables without impacting anything else.

That's a good one an= d quite apt too!


Regards
Sameer
Ashnik Pte. Ltd.<= /font>
Singapore

--047d7bf0e20ca7064f04eeaea5bd--