Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id A95651337B4E for ; Tue, 21 Sep 2010 00:44:26 -0300 (ADT) 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 99277-09 for ; Tue, 21 Sep 2010 03:44:19 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id 0F6DA133616B for ; Tue, 21 Sep 2010 00:44:18 -0300 (ADT) Received: by iwn5 with SMTP id 5so4785655iwn.19 for ; Mon, 20 Sep 2010 20:44:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:mime-version:received:in-reply-to :references:from:date:message-id:subject:to:cc:content-type; bh=gcPx+dnudaeS8RwZ6pl1ngngFdm8QNSv3CCW4D4SP1s=; b=ZwExJBOejkEv0RGSTi19UnBkD7nWS2FtMnnhbgRI0V3Cm0YNkIl6D5GduLlghWVIW/ KknUg7rNmkUh/dK9kfLbTp3yijctCcnQR5raCBUrzH7uiFbenp7OofKdCHQa65IF05py 3PH2D6XRIeJsXZSpszmZDdaS8M5BzHejhgB4E= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=hJqVgNaABynlv107cIUmTbiSwNo+/aVlVKczisb0AWt89r0En7XhNdQVIbet7pvwO0 V8gKrNpAOQVuUjufZYf5Resmbban5NnBzoGJAcCFwLsdW14V0LPBgZQ4nQBcju5UY4ke pqx8jKyCAOaFV/T1BOLnVbSn2qV9yL0Lflei4= Received: by 10.231.13.139 with SMTP id c11mr11177876iba.188.1285040658266; Mon, 20 Sep 2010 20:44:18 -0700 (PDT) MIME-Version: 1.0 Received: by 10.231.12.216 with HTTP; Mon, 20 Sep 2010 20:43:58 -0700 (PDT) In-Reply-To: <4C979AEF.9030402@ca.afilias.info> References: <201009201649.o8KGnAn00996@momjian.us> <4C979AEF.9030402@ca.afilias.info> From: John Cheng Date: Mon, 20 Sep 2010 20:43:58 -0700 Message-ID: Subject: Re: Binary Replication and Slony To: Brad Nicholson Cc: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary=002215046afff53a420490bcd735 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.42 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FS_REPLICA=3.599, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, SARE_SPEC_REPLICA=0.72 X-Spam-Level: ** X-Archive-Number: 201009/641 X-Sequence-Number: 167443 --002215046afff53a420490bcd735 Content-Type: text/plain; charset=ISO-8859-1 Much thanks to everyone! The mailing list, as usual, has been extremely helpful. On Mon, Sep 20, 2010 at 10:33 AM, Brad Nicholson wrote: > On 10-09-20 12:49 PM, Bruce Momjian wrote: > >> John Cheng wrote: >> >>> Congrats on the 9.0 release of PostgreSQL. One of the features I am >>> really >>> interested in is the built-in binary replication. >>> >>> Our production environment has been using PostgreSQL for more than 5 >>> years >>> (since this project started). We have been using Slony-I as our >>> replication >>> mechanism. I am interested to find out the pros and cons of Slony vs the >>> built-in replication in 9.0. Based on what I understand: >>> >>> * Slony has a higher overhead than the binary replication in 9.0 >>> * When using Slony, schema change must be applied via slonik (in most >>> cases) >>> * Unfortunately, IMO it is easy to make a mistake when applying schema >>> changes in Slony, fortunately, it is easy to drop and recreate the >>> replication sets >>> * Slony is an asynchronous replication mechanism >>> * Slony allows you to replication some tables, while ignoring others >>> >>> * PostgreSQL 9.0 with hot standby& streaming replication is an >>> asynchronous >>> replication mechanism >>> * Overhead is low compared to Slony >>> >>> Are there some cases where it is better to use Slony, for example, when >>> you >>> must specifically exclude tables from replication? I believe our system >>> will >>> be better off using the built-in replication mechanism of 9.0, and I am >>> guessing most people will be in the same boat. >>> >> You have summarized the differences well. Streaming replication has >> lower overhread, but doesn't allow per-table granularity or allow >> replication between different versions of Postgres. >> >> > Slony will also allow you to: > > -run custom schema (like extra indexes) on replicas > -replicate between different hardware architectures and OS's > -run lengthy queries against replicas having to worry about trade offs > surrounding query cancellation vs standby lagging. > -switch roles of two nodes without entering a degraded state or worrying > about STONITH. If you switch roles in a controlled manner, both nodes > remain in the cluster. Slony prevents writes against the replica. > > I do agree that for most, Slony is overkill and streaming replication and > hot standby will be the better choice. > > -- > Brad Nicholson 416-673-4106 > Database Administrator, Afilias Canada Corp. > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- --- John L Cheng --002215046afff53a420490bcd735 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Much thanks to everyone! The mailing list, as usual, has been extremely hel= pful.

On Mon, Sep 20, 2010 at 10:33 AM, Brad Nicholson <bnichols@ca.afilias.info> wrote:
=A0On 10-= 09-20 12:49 PM, Bruce Momjian wrote:
John Cheng wrote:
Congrats on the 9.0 release of PostgreSQL. One of the features I am really<= br> interested in is the built-in binary replication.

Our production environment has been using PostgreSQL for more than 5 years<= br> (since this project started). We have been using Slony-I as our replication=
mechanism. I am interested to find out the pros and cons of Slony vs the built-in replication in 9.0. Based on what I understand:

* Slony has a higher overhead than the binary replication in 9.0
* When using Slony, schema change must be applied via slonik (in most cases= )
* Unfortunately, IMO it is easy to make a mistake when applying schema
changes in Slony, fortunately, it is easy to drop and recreate the
replication sets
* Slony is an asynchronous replication mechanism
* Slony allows you to replication some tables, while ignoring others

* PostgreSQL 9.0 with hot standby& =A0streaming replication is an async= hronous
replication mechanism
* Overhead is low compared to Slony

Are there some cases where it is better to use Slony, for example, when you=
must specifically exclude tables from replication? I believe our system wil= l
be better off using the built-in replication mechanism of 9.0, and I am
guessing most people will be in the same boat.
You have summarized the differences well. =A0Streaming replication has
lower overhread, but doesn't allow per-table granularity or allow
replication between different versions of Postgres.


Slony will also allow you to:

-run custom schema (like extra indexes) on replicas
-replicate between different hardware architectures and OS's
-run lengthy queries against replicas having to worry about trade offs surr= ounding query cancellation vs standby lagging.
-switch roles of two nodes without entering a degraded state or worrying ab= out STONITH. =A0If you switch roles in a controlled manner, both nodes rema= in in the cluster. =A0Slony prevents writes against the replica.

I do agree that for most, Slony is overkill and streaming replication and h= ot standby will be the better choice.

--
Brad Nicholson =A0416-673-4106
Database Administrator, Afilias Canada Corp.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
---
John= L Cheng
--002215046afff53a420490bcd735--