I have been looking at various systems that hook into PostgreSQL for
replication, and I am rather confused by the many systems and what they
do/how they work. So, let me begin by describing the current working
setup, and hopefully that will reveal the details behind what it is I am
looking for.

Currently, I am working with a project that employs two servers,
geographically distributed, running an application that uses PHP+MySQL
for its work. The two servers each run their own instances of MySQL and
Apache, and use failover at the DNS level should one of them fail, which
is monitored by a third server dedicated to the purpose of monitoring
the application servers.

MySQL is configured in a special-case ring replication configuration
between these two servers, where INSERT and UPDATE queries can take
place on either, and they are propegated to the next. The MySQL server
handles things like dividing the primary key space (currently an
auto-incrementing integer value) by having each of the servers use an
assigned increment offset and increment value, to avoid conflicts
between the two.

However, this application is out-growing MySQL's capabilities, and
PostgreSQL is the likely candidate for replacement---it has all the
necessary functionality as well as the ability to ensure data integrity,
but what I can't sort out is the replication piece. I would need to be
able to put forth a replication solution that is somewhat simple to
maintain, as well as able to deal with unexpected failovers and have
what amounts to essentially a dual-master server configuration, where
the servers are each masters and slaves of each other in terms of
replication. So, what I need to know is whether or not there is a
replication system for PostgreSQL that would match what the MySQL
server's replication system provides at present (including the ability
to add new application servers).

TIA,
Mike Trausch

--
Michael B. Trausch http://www.trausch.us/
Pidgin 2.1.1 and plugins for Ubuntu Feisty!
(And Thunderbird 2.0.0.6, too!) http://www.trausch.us/pidgin

Michael Trausch <"mike|s/\\x40/\\./g;s/|.*|/\\x40/g;|trausch"@us> wrote:
As far as I know, there is no Free Software solution for this.

The Austrian Company Cybertec advertises a solution that you could buy:
http://www.postgresql.at/en/index.html
but I have never seen it - you'd have to investigate.

Another option I can think of is to write your poor man's implementation
yourself using the dblink contrib module:

When an insert, update or delete is performed against the table on one of
the servers, a trigger modifies to table on the other server via dblink.
Of course you'd also have to worry about what to do when one of the
servers is not available, how to reconcile afterwards, etc.
And adding a new server would not be straightforward.

Yours,
Laurenz Albe

Laurenz Albe schrieb:
Isn't pgpool suitable for this?

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

Stefan Braumeister <sbraumi (AT) gmx (DOT) net> wrote:
You are right, that looks good.

It's not directly what I'd call replication, but it seems to be a good
idea for this task.

Yours,
Laurenz Albe

Stefan Braumeister, on 09/10/2007 05:23 AM said:
It doesn't look like it would be; diaster recovery is manual and
requires downtime from the live database, which isn't an option in this
situation.

Other than this one blockage, PostgreSQL is perfect. I've used it many
times in the past on smaller projects that didn't need to be distributed
geographically, and everything was happy.

Perhaps something will come out of the woodwork on this at some point?
Are there any projects in progress that seek to achieve this, or is
PostgreSQL itself looking at adding replication to the database server
itself maybe in the future?

-- Mike

--
Michael B. Trausch http://www.trausch.us/
Pidgin 2.1.1 and plugins for Ubuntu Feisty!
(And Thunderbird 2.0.0.6, too!) http://www.trausch.us/pidgin

Michael Trausch schrieb:
Then you may want to checkout:

http://pgcluster.projects.postgresql.org/feature.html

Last time I took a look, it wasn't ready for production but things may
have changed.

Stefan Braumeister wrote:
I thought Slony was the standard replication product for PG:
<http://main.slony.info/>

It's mentioned all over the PG boards.

--
Lew

Lew, on 09/11/2007 10:31 AM said:
I might be illiterate, but it seems that Slony doesn't support the setup
that would be necesary in my situation; it says that it will happily
have and manage multiple slaves, but it doesn't say anything about
replication rings or "dual master" configurations.

I have been looking at it, because I keep running into it over and over
again, but either I am an idiot, or I simply can't find anything that
says that it'd be good for having a dual-master or ring-master type
replication scenario. I am still looking into it via the documentation,
of course.

Ultimately, though, it looks like the documentation isn't all that clear
and I am going to have to set up some testing servers and see what I can
manage to make happen and what is actually impossible.

-- Mike

--
Michael B. Trausch http://www.trausch.us/
Pidgin 2.1.1 and plugins for Ubuntu Feisty!
(And Thunderbird 2.0.0.6, too!) http://www.trausch.us/pidgin

Lew schrieb:
Slony is no master to master replication system.
Only master to slave, big difference :-)