Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 71695133616B for ; Wed, 9 Feb 2011 14:00:16 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 24159-01 for ; Wed, 9 Feb 2011 18:00:08 +0000 (UTC) X-Greylist: delayed 01:06:36.407266 by SQLgrey-1.7.6 Received: from mail-pz0-f46.google.com (mail-pz0-f46.google.com [209.85.210.46]) by mail.postgresql.org (Postfix) with ESMTP id C1E801337B5D for ; Wed, 9 Feb 2011 14:00:07 -0400 (AST) Received: by pzk35 with SMTP id 35so76989pzk.19 for ; Wed, 09 Feb 2011 10:00:06 -0800 (PST) Received: by 10.142.141.13 with SMTP id o13mr2951014wfd.20.1297270410061; Wed, 09 Feb 2011 08:53:30 -0800 (PST) Received: from Issac.consistentstate.com (c-67-165-192-103.hsd1.co.comcast.net [67.165.192.103]) by mx.google.com with ESMTPS id e14sm620409wfg.20.2011.02.09.08.53.28 (version=SSLv3 cipher=RC4-MD5); Wed, 09 Feb 2011 08:53:29 -0800 (PST) Message-ID: <4D52C684.1070808@consistentstate.com> Date: Wed, 09 Feb 2011 09:53:24 -0700 From: CS DBA User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.13) Gecko/20101208 Red Hat/3.1.7-3.el6_0 Lightning/1.0b2 Thunderbird/3.1.7 MIME-Version: 1.0 To: Gabriele Bartolini CC: "Rangi, Jai" , pgsql-admin@postgresql.org Subject: Re: Postgres Replication Options References: <11F808DFED68574E8357241F5B39CAF5B96A24@mail-001.corp.automotive.com> <4D523E22.5000106@2ndQuadrant.it> In-Reply-To: <4D523E22.5000106@2ndQuadrant.it> Content-Type: multipart/alternative; boundary="------------090901020100070701010308" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.72 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FS_REPLICA=3.599, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7, SARE_SPEC_REPLICA=0.72 X-Spam-Level: * X-Archive-Number: 201102/56 X-Sequence-Number: 36695 This is a multi-part message in MIME format. --------------090901020100070701010308 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit One of the main considerations per Hot Standby vs SLONY is replication scope. With Hot Standby you get everything that occurs in the cluster, across all databases. With SLONY you are limited to at most a single database per "SLONY Cluster", and you can define replication sets which only contain a sub-set of the tables in the database. So, IMHO I'd go with Hot Standby if I wanted to replicate the full cluster and SLONY if I wanted to "slice & dice" tables and target slaves (i.e. replicate all tables for a single db to slave 1, only 50 tables to slave 2, etc...) --hope this helps /Kevin > Hi, > > Il 09/02/11 01:34, Rangi, Jai ha scritto: >> >> Hello, >> >> I am looking for a replication solution for PG 9.x. Idea is to have >> one master replication server and multiple (around 20) slave servers >> read only. I see PG 9 has inbuilt Streaming replication. Is this the >> best replication solution. How about slony? Which option will keep >> the slave nodes in closest sync to master and which is more stable. >> > With PostgreSQL 9.0 in terms of builtin replication you can take > advantage of Hot Standby based on either WAL shipping or on Streaming > Replication.I would personally stick with builtin solutions if you > plan to have full replicas of your databases, even in terms of > maintenance and upgrades later on (in the long term). Some tools, > including replication manager (repmgr) are coming out in order to > manage HA clusters (for more info: > http://blog.2ndquadrant.com/en/2011/01/easier-postgresql-90-clusters.html). > > Cheers, > Gabriele > -- > Gabriele Bartolini - 2ndQuadrant Italia > PostgreSQL Training, Services and Support > gabriele.bartolini@2ndQuadrant.it |www.2ndQuadrant.it --------------090901020100070701010308 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit One of the main considerations per Hot Standby vs SLONY is replication scope. With Hot Standby you get everything that occurs in the cluster, across all databases. With SLONY you are limited to at most a single database per "SLONY Cluster", and you can define replication sets which only contain a sub-set of the tables in the database.  So, IMHO I'd go with Hot Standby if I wanted to replicate the full cluster and SLONY if I wanted to "slice & dice" tables and target slaves (i.e. replicate all tables for a single db to slave 1, only 50 tables to slave 2, etc...)


--hope this helps

/Kevin


Hi,

Il 09/02/11 01:34, Rangi, Jai ha scritto:

Hello,

I am looking for a replication solution for PG 9.x. Idea is to have one master replication server and multiple (around 20) slave servers read only. I see PG 9 has inbuilt Streaming replication. Is this the best replication solution. How about slony? Which option will keep the slave nodes in closest sync to master and which is more stable. 

With PostgreSQL 9.0 in terms of builtin replication you can take advantage of Hot Standby based on either WAL shipping or on Streaming Replication. I would personally stick with builtin solutions if you plan to have full replicas of your databases, even in terms of maintenance and upgrades later on (in the long term). Some tools, including replication manager (repmgr) are coming out in order to manage HA clusters (for more info: http://blog.2ndquadrant.com/en/2011/01/easier-postgresql-90-clusters.html).

Cheers,
Gabriele
 
-- 
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

--------------090901020100070701010308--