Re: Few questions on postgresql (dblink, 2pc, clustering)

Jim Worke wrote:
> Hi list,
>
> My boss would like to convert from MySQL to PostgreSQL, but there are a few
> things that hold us back.
>
> We'd like to have the databases in several physical servers, with referential
> integrity between the tables. Reading from Oracle articles, their
> referential integrity can be done using their dblink and triggers. What I'd
> like to ask is whether contrib/dblink is stable? Seeing dblink in a contrib

It is certainly stable, but it does not have transaction semantics like
local table queries.

> directory does bring a doubt (though we've read many success stories with
> dblink). And can the Oracle's way of doing referential integrity be used in
> PostgreSQL? Is it recommended to do that?

Again, no persistent locking.,

> I've read the PostgreSQL's TODO list, and I can't find anything about 2-phase
> commit being implemented in 8.0. Is this correct? Because I also read
> there's a 2PC patch being implemented (http://snaga.org/pgsql/). But
> progress seems to have stopped (correct me if I'm wrong).

2-phase isn't in 8.0 but I expect it in 8.1.

> Basically, our concern is that dblink, 2PC implementation are there, but not
> in the PostgreSQL mainstream.

You need to understand the limitations of dblink and see if it will work
for you. I can't imagine MySQl is allowing you to do this cleanly so I
don't see why it would hold up a MySQL -> PostgreSQL migration.

> Another thing that bothers us is that we can't find any multi-master
> clustering solution in PostgreSQL. We're actually evaluating MySQL's own
> clustering solution, but it's production quality release is still slated for
> MySQL 5.0.

The only multi-master I know of is pgcluster. There is talking of
moving Slony from master/slave to multi-master but work has not started
yet.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073