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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073