Looking for replication solutions, I find:

Slony-I
Seems good, single master only, master is a single point of failure,
no good failover system for electing a new master or having a failed
master rejoin the cluster. Slave databases are mostly for safety or
for parallelizing queries for performance. Suffers from O(N^2)
communications (N = cluster size).

Slony-II
Seems brilliant, a solid theoretical foundation, at the forefront of
computer science. But can't find project status -- when will it be
available? Is it a pipe dream, or a nearly-ready reality?

PGReplication
Appears to be a page that someone forgot to erase from the old GBorg site.

PGCluster
Seems pretty good, but web site is not current, there are releases in use
that are not on the web site, and also seems to always be a couple steps
behind the current release of Postgres. Two single-points failure spots,
load balancer and the data replicator.

Is this a good summary of the status of replication? Have I missed any important solutions or mischaracterized anything?

Thanks!
Craig


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Craig James wrote:
> Looking for replication solutions, I find:
>
> Slony-I
> Seems good, single master only, master is a single point of failure,
> no good failover system for electing a new master or having a failed
> master rejoin the cluster. Slave databases are mostly for safety or
> for parallelizing queries for performance. Suffers from O(N^2)
> communications (N = cluster size).

Yep

>
> Slony-II
> Seems brilliant, a solid theoretical foundation, at the forefront of
> computer science. But can't find project status -- when will it be
> available? Is it a pipe dream, or a nearly-ready reality?
>

Dead


> PGReplication
> Appears to be a page that someone forgot to erase from the old GBorg site.
>

Dead


> PGCluster
> Seems pretty good, but web site is not current, there are releases in use
> that are not on the web site, and also seems to always be a couple steps
> behind the current release of Postgres. Two single-points failure spots,
> load balancer and the data replicator.
>

Slow as all get out for writes but cool idea

> Is this a good summary of the status of replication? Have I missed any
> important solutions or mischaracterized anything?
>

log shipping, closed source solutions


> Thanks!
> Craig
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Which replication problem are you trying to solve?

On Thu, 14 Jun 2007, Craig James wrote:

> Looking for replication solutions, I find:
>
> Slony-I
> Seems good, single master only, master is a single point of failure,
> no good failover system for electing a new master or having a failed
> master rejoin the cluster. Slave databases are mostly for safety or
> for parallelizing queries for performance. Suffers from O(N^2)
> communications (N = cluster size).
>
> Slony-II
> Seems brilliant, a solid theoretical foundation, at the forefront of
> computer science. But can't find project status -- when will it be
> available? Is it a pipe dream, or a nearly-ready reality?
>
> PGReplication
> Appears to be a page that someone forgot to erase from the old GBorg site.
>
> PGCluster
> Seems pretty good, but web site is not current, there are releases in use
> that are not on the web site, and also seems to always be a couple steps
> behind the current release of Postgres. Two single-points failure spots,
> load balancer and the data replicator.
>
> Is this a good summary of the status of replication? Have I missed any
> important solutions or mischaracterized anything?
>
> Thanks!
> Craig
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On 6/15/07, Craig James <craig_james@emolecules.com> wrote:
[snip]
> Is this a good summary of the status of replication? Have I missed any important solutions or mischaracterized anything?

* Mammoth Replicator, commercial.

* Continuent uni/cluster, commercial
(http://www.continuent.com/index.php?...&Item id=169).

* pgpool-II. Supports load-balancing and replication by implementing a
proxy that duplicates all updates to all slaves. It can partition data
by doing this, and it can semi-intelligently route queries to the
appropriate servers.

* Cybertec. This is a commercial packaging of PGCluster-II from an
Austrian company.

* Greenplum Database (formerly Bizgres MPP), commercial. Not so much a
replication solution as a way to parallelize queries, and targeted at
the data warehousing crowd. Similar to ExtenDB, but tightly integrated
with PostgreSQL.

* DRDB (http://www.drbd.org/), a device driver that replicates disk
blocks to other nodes. This works for failover only, not for scaling
reads. Easy migration of devices if combined with an NFS export.

* Skytools (https://developer.skype.com/SkypeGar...ects/SkyTools),
a collection of replication tools from the Skype people. Purports to
be simpler to use than Slony.

Lastly, and perhaps most promisingly, there's the Google Summer of
Code effort by Florian Pflug
(http://code.google.com/soc/postgres/...45828A8197EBC6)
to implement true log-based replication, where PostgreSQL's
transaction logs are used to keep live slave servers up to date with a
master. In theory, such a system would be extremely simple to set up
and use, especially since it should, as far as I can see, also
transparently replicate the schema for you.

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

>>> On Thu, Jun 14, 2007 at 6:14 PM, in message <4671CBBA.6010104@emolecules.com>,
Craig James <craig_james@emolecules.com> wrote:
> Looking for replication solutions, I find:
>
> Slony-I
> Slony-II
> PGReplication
> PGCluster

You wouldn't guess it from the name, but pgpool actually supports replication:

http://pgpool.projects.postgresql.org/




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Thanks to all who replied and filled in the blanks. The problem with the web is you never know if you've missed something.

Joshua D. Drake wrote:
>> Looking for replication solutions, I find...
>> Slony-II
> Dead

Wow, I'm surprised. Is it dead for lack of need, lack of resources, too complex, or all of the above? It sounded like such a promising theoretical foundation.

Ben wrote:
> Which replication problem are you trying to solve?

Most of our data is replicated offline using custom tools tailored to our loading pattern, but we have a small amount of "global" information, such as user signups, system configuration, advertisements, and such, that go into a single small (~5-10 MB) "global database" used by all servers.

We need "nearly-real-time replication," and instant failover. That is, it's far more important for the system to keep working than it is to lose a little data. Transactional integrity is not important. Actual hardware failures are rare, and if a user just happens to sign up, or do "save preferences", at the instant the global-database server goes down, it's not a tragedy. But it's not OK for the entire web site to go down when the one global-database server fails.

Slony-I can keep several slave databases up to date, which is nice. And I think I can combine it with a PGPool instance on each server, with the master as primary and few Slony-copies as secondary. That way, if the master goes down, the PGPool servers all switch to their secondary Slony slaves, and read-only access can continue. If the master crashes, users will be able to do most activities, but new users can't sign up, and existing users can't change their preferences, until either the master server comes back, or one of the slaves is promoted to master.

The problem is, there don't seem to be any "vote a new master" type of tools for Slony-I, and also, if the original master comes back online, it has no way to know that a new master has been elected. So I'd have to write a bunch of SOAP services or something to do all of this.

I would consider PGCluster, but it seems to be a patch to Postgres itself. I'm reluctant to introduce such a major piece of technology into our entire system, when only one tiny part of it needs the replication service.

Thanks,
Craig

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> Most of our data is replicated offline using custom tools tailored to
> our loading pattern, but we have a small amount of "global" information,
> such as user signups, system configuration, advertisements, and such,
> that go into a single small (~5-10 MB) "global database" used by all
> servers.

Slony provides near instantaneous failovers (in the single digit seconds
range). You can script an automatic failover if the master server
becomes unreachable. That leaves you the problem of restarting your app
(or making it reconnect) to the new master.

5-10MB data implies such a fast initial replication, that making the
server rejoin the cluster by setting it up from scratch is not an issue.


> The problem is, there don't seem to be any "vote a new master" type of
> tools for Slony-I, and also, if the original master comes back online,
> it has no way to know that a new master has been elected. So I'd have
> to write a bunch of SOAP services or something to do all of this.

You don't need SOAP services, and you do not need to elect a new master.
if dbX goes down, dbY takes over, you should be able to decide on a
static takeover pattern easily enough.

The point here is, that the servers need to react to a problem, but you
probably want to get the admin on duty to look at the situation as
quickly as possible anyway. With 5-10MB of data in the database, a
complete rejoin from scratch to the cluster is measured in minutes.

Furthermore, you need to checkout pgpool, I seem to remember that it has
some bad habits in routing queries. (E.g. it wants to apply write
queries to all nodes, but slony makes the other nodes readonly.
Furthermore, anything inside a BEGIN is sent to the master node, which
is bad with some ORMs, that by default wrap any access into a transaction)

Andreas
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGceUXHJdudm4KnO0RAgh/AJ4kXFpzoQAEnn1B7K6pzoCxk0wFxQCggGF1
mA1KWvcKtfJ6ZcPiajJK1i4=
=eoNN
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Andreas Kostyrka wrote:
> Slony provides near instantaneous failovers (in the single digit seconds
> range). You can script an automatic failover if the master server
> becomes unreachable.

But Slony slaves are read-only, correct? So the system isn't fully functional once the master goes down.

> That leaves you the problem of restarting your app
> (or making it reconnect) to the new master.

Don't you have to run a Slony app to convert one of the slaves into the master?

> 5-10MB data implies such a fast initial replication, that making the
> server rejoin the cluster by setting it up from scratch is not an issue.

The problem is to PREVENT it from rejoining the cluster. If you have some semi-automatic process that detects the dead server and converts a slave to the master, and in the mean time the dead server manages to reboot itself (or its network gets fixed, or whatever the problem was), then you have two masters sending out updates, and you're screwed.

>> The problem is, there don't seem to be any "vote a new master" type of
>> tools for Slony-I, and also, if the original master comes back online,
>> it has no way to know that a new master has been elected. So I'd have
>> to write a bunch of SOAP services or something to do all of this.
>
> You don't need SOAP services, and you do not need to elect a new master.
> if dbX goes down, dbY takes over, you should be able to decide on a
> static takeover pattern easily enough.

I can't see how that is true. Any self-healing distributed system needs something like the following:

- A distributed system of nodes that check each other's health
- A way to detect that a node is down and to transmit that
information across the nodes
- An election mechanism that nominates a new master if the
master fails
- A way for a node coming online to determine if it is a master
or a slave

Any solution less than this can cause corruption because you can have two nodes that both think they're master, or end up with no master and no process for electing a master. As far as I can tell, Slony doesn't do any of this. Is there a simpler solution? I've never heard of one.

> The point here is, that the servers need to react to a problem, but you
> probably want to get the admin on duty to look at the situation as
> quickly as possible anyway.

No, our requirement is no administrator interaction. We need instant, automatic recovery from failure so that the system stays online.

> Furthermore, you need to checkout pgpool, I seem to remember that it has
> some bad habits in routing queries. (E.g. it wants to apply write
> queries to all nodes, but slony makes the other nodes readonly.
> Furthermore, anything inside a BEGIN is sent to the master node, which
> is bad with some ORMs, that by default wrap any access into a transaction)

I should have been more clear about this. I was planning to use PGPool in the PGPool-1 mode (not the new PGPool-2 features that allow replication). So it would only be acting as a failover mechanism. Slony would be used as the replication mechanism.

I don't think I can use PGPool as the replicator, because then it becomes a new single point of failure that could bring the whole system down. If you're using it for INSERT/UPDATE, then there can only be one PGPool server.

I was thinking I'd put a PGPool server on every machine in failover mode only. It would have the Slony master as the primary connection, and a Slony slave as the failover connection. The applications would route all INSERT/UPDATE statements directly to the Slony master, and all SELECT statements to the PGPool on localhost. When the master failed, all of the PGPool servers would automatically switch to one of the Slony slaves.

This way, the system would keep running on the Slony slaves (so it would be read-only), until a sysadmin could get the master Slony back online. And when the master came online, the PGPool servers would automatically reconnect and write-access would be restored.

Does this make sense?

Craig

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Craig James wrote:
> Andreas Kostyrka wrote:
>> Slony provides near instantaneous failovers (in the single digit seconds
>> range). You can script an automatic failover if the master server
>> becomes unreachable.
>
> But Slony slaves are read-only, correct? So the system isn't fully
> functional once the master goes down.

That is what promotion is for.

Joshua D. Drake


> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On 6/15/07, Craig James <craig_james@emolecules.com> wrote:
> I don't think I can use PGPool as the replicator, because then it becomes a new single point of failure that could bring the whole system down. If you're using it for INSERT/UPDATE, then there can only be one PGPool server.

Are you sure? I have been considering this possibility, too, but I
didn't find anything in the documentation. The main mechanism of the
proxy is taking received updates and playing them one multiple servers
with 2PC, and the proxies should not need to keep any state about
this, so why couldn't you install multiple proxies?

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match