pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedBinary Replication and Slony
6+ messages / 4 participants
[nested] [flat]
* Binary Replication and Slony
@ 2010-09-20 15:21 John Cheng <johnlicheng@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: John Cheng @ 2010-09-20 15:21 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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.
--
---
John L Cheng
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Binary Replication and Slony
@ 2010-09-20 16:22 Chris Browne <cbbrowne@acm.org>
parent: John Cheng <johnlicheng@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Chris Browne @ 2010-09-20 16:22 UTC (permalink / raw)
To: pgsql-general@postgresql.org
johnlicheng@gmail.com (John Cheng) writes:
> 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.
There are three characteristic kinds of cases where you'll need
something like Slony-I, where the built-in WAL-based replication won't
work:
a) You need to interact between PostgreSQL versions. Slony (and similar
systems like Londiste and Bucardo) can cope with having nodes running
different versions of PostgreSQL.
WAL-based replication requires that all databases use *identical*
versions of PostgreSQL, running on identical architectures.
b) You only want to replicate parts of the changes that are going on.
WAL-based replication duplicates *absolutely everything*.
c) You need for there to be extra behaviours taking place on
subscribers, for instance, populating cache management information.
WAL-based replication duplicates *absolutely everything*, and nothing
extra that changes data can run on a WAL-based replica.
If you don't need any of those things, then, yes, I'd think the built-in
replication is a good choice, quite likely preferable to using the
trigger-based replication systems like Slony.
--
output = reverse("ofni.secnanifxunil" "@" "enworbbc")
"What you said you want to do is roughly equivalent to nailing
horseshoes to the tires of your Buick." -- danceswithcrows@usa.net on
the question "Why can't Linux use Windows Drivers?"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Binary Replication and Slony
@ 2010-09-20 16:49 Bruce Momjian <bruce@momjian.us>
parent: John Cheng <johnlicheng@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2010-09-20 16:49 UTC (permalink / raw)
To: John Cheng <johnlicheng@gmail.com>; +Cc: pgsql-general@postgresql.org
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.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Binary Replication and Slony
@ 2010-09-20 17:33 Brad Nicholson <bnichols@ca.afilias.info>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 6+ messages in thread
From: Brad Nicholson @ 2010-09-20 17:33 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Binary Replication and Slony
@ 2010-09-21 03:43 John Cheng <johnlicheng@gmail.com>
parent: Brad Nicholson <bnichols@ca.afilias.info>
1 sibling, 0 replies; 6+ messages in thread
From: John Cheng @ 2010-09-21 03:43 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: pgsql-general@postgresql.org
Much thanks to everyone! The mailing list, as usual, has been extremely
helpful.
On Mon, Sep 20, 2010 at 10:33 AM, Brad Nicholson
<bnichols@ca.afilias.info>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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] Binary Replication and Slony
@ 2011-02-01 20:21 Bruce Momjian <bruce@momjian.us>
parent: Brad Nicholson <bnichols@ca.afilias.info>
1 sibling, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2011-02-01 20:21 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: pgsql-docs
I have applied the attached documentation improvement to better
highlight Slony's abilities.
---------------------------------------------------------------------------
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
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:
/rtmp/Slony.diff (text/x-diff, 775B) download
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 4f59fd1..226d616 100644
*** a/doc/src/sgml/runtime.sgml
--- b/doc/src/sgml/runtime.sgml
*************** pg_dumpall -p 5432 | psql -d postgres -p
*** 1704,1709 ****
--- 1704,1711 ----
<para>
It is also possible to use certain replication methods, such as
<productname>Slony</>, to create a standby server with the updated version of
+ <productname>PostgreSQL</>. This is possible because Slony supports
+ replication between different major versions of
<productname>PostgreSQL</>. The standby can be on the same computer or
a different computer. Once it has synced up with the master server
(running the older version of <productname>PostgreSQL</>), you can
end of thread, other threads:[~2011-02-01 20:21 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-20 15:21 Binary Replication and Slony John Cheng <johnlicheng@gmail.com>
2010-09-20 16:22 ` Chris Browne <cbbrowne@acm.org>
2010-09-20 16:49 ` Bruce Momjian <bruce@momjian.us>
2010-09-20 17:33 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-09-21 03:43 ` John Cheng <johnlicheng@gmail.com>
2011-02-01 20:21 ` Bruce Momjian <bruce@momjian.us>
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