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

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

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

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

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.

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

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

Jim Worke wrote:[color=blue]
> 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[/color]

It is certainly stable, but it does not have transaction semantics like
local table queries.
[color=blue]
> 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?[/color]

Again, no persistent locking.,
[color=blue]
> 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).[/color]

2-phase isn't in 8.0 but I expect it in 8.1.
[color=blue]
> Basically, our concern is that dblink, 2PC implementation are there, but not
> in the PostgreSQL mainstream.[/color]

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.
[color=blue]
> 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.[/color]

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

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

http://www.postgresql.org/docs/faqs/FAQ.html

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

On 8/21/2004 10:34 PM, Jim Worke wrote:
[color=blue]
> 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.[/color]

And with all the different table types available, MySQL has successfully
created a lot of confusion. Note that all the latest and greatest
features that became available with InnoDB tables will again be on your
wishlist when using NDB Cluster. InnoDB and NDB Cluster transactions are
not ACID together, only each of them inside of its table handler is. NDB
Cluster tables will not support foreign keys and the last I heard was
that unique constraints are pretty expensive because of their "funky"
implementation as separate tables.

Another little detail is that NDB Cluster is an in-memory table type.
And to get your failover capabilities, the data must be in memory at
least twice. So you need 2x your entire DB size plus overhead like OS
and a little room to breathe in RAM. With a 100GB database, this will
get funny on the network side too because you will want to have better
than gigabit ethernet.


Jan

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = JanWieck@Yahoo.com #

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