Binary Replication and Slony

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?"

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. +

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.

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

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. +