Thread: Cluster/redundancy question

All,

Forgive me if this has been answered before, but I've searched the archives and the net extensively and have come up mostly empty so far.

I'm working at convincing my firm to implement a postgresql database cluster.
Specifically, we'd like to get a few machines running to be more available and to protect against any kind of failure.

Right now people are leaning toward MySQL and Emic, but I've been evangelizing for postgres for the standard reasons.
How does one go about setting this kind of thing up?
I've looked at Bizgres and Pervasive but I'm not sure if that's necessarily what we want.

thank you,

Travis

Hello Travis,

     I don't know if there are a Oracle RAC style cluster system for
PGSQL but this software that can do something similar,
http://pgcluster.projects.postgresql.org/feature.html may help.

     You can always use Slony for replication services.

     Cheers,

     Aly.

On Mon, 10 Oct 2005, Travis Brady wrote:

> All,
>
> Forgive me if this has been answered before, but I've searched the archives
> and the net extensively and have come up mostly empty so far.
>
> I'm working at convincing my firm to implement a postgresql database
> cluster.
> Specifically, we'd like to get a few machines running to be more available
> and to protect against any kind of failure.
>
> Right now people are leaning toward MySQL and Emic, but I've been
> evangelizing for postgres for the standard reasons.
> How does one go about setting this kind of thing up?
> I've looked at Bizgres and Pervasive but I'm not sure if that's necessarily
> what we want.
>
> thank you,
>
> Travis
>

--
Aly S.P Dharshi
aly.dharshi@telus.net

      "A good speech is like a good dress
       that's short enough to be interesting
       and long enough to cover the subject"

On Mon, 2005-10-10 at 15:16, Travis Brady wrote:
> All,
>
> Forgive me if this has been answered before, but I've searched the
> archives and the net extensively and have come up mostly empty so far.
>
> I'm working at convincing my firm to implement a postgresql database
> cluster.
> Specifically, we'd like to get a few machines running to be more
> available and to protect against any kind of failure.
>
> Right now people are leaning toward MySQL and Emic, but I've been
> evangelizing for postgres for the standard reasons.
> How does one go about setting this kind of thing up?
> I've looked at Bizgres and Pervasive but I'm not sure if that's
> necessarily what we want.

If you're looking at the replication systems FIRST, then you've missed a
few steps.  Look at what the databases, sans replication, can bring to
the table, and what they lack.

If you MUST have a database that checks input against check constraints,
then MySQL is out.  It simply doesn't support them.

If you need a database that converts the number 234987234987234987234
into 2147483647 when you insert it into an int field and doesn't throw
an error or warning, then MySQL should be your choice.

How important is good transactional performance?  MySQL has serious
limitations for certain storage engines and you need to compare those
things before looking at replication.

Don't get me wrong, if replication is one of the things you need, then
consider it, but if you're putting bad data into your database, what
good is replicating it gonna do ya?

Don't forget that MySQL replication also has a habit of silently
failing on you and in my experience needs continuous monitoring to
make sure it actually keeps reasonably up to date (e.g. not days of
data behind on the slaves.)

That was a while ago though, maybe they fixed it?
British Telecom use Slony as well, so I guess it can't be *that* bad?

On 11 Oct 2005, at 17:38, Scott Marlowe wrote:

> On Mon, 2005-10-10 at 15:16, Travis Brady wrote:
>
>> All,
>>
>> Forgive me if this has been answered before, but I've searched the
>> archives and the net extensively and have come up mostly empty so
>> far.
>>
>> I'm working at convincing my firm to implement a postgresql database
>> cluster.
>> Specifically, we'd like to get a few machines running to be more
>> available and to protect against any kind of failure.
>>
>> Right now people are leaning toward MySQL and Emic, but I've been
>> evangelizing for postgres for the standard reasons.
>> How does one go about setting this kind of thing up?
>> I've looked at Bizgres and Pervasive but I'm not sure if that's
>> necessarily what we want.
>>
>
> If you're looking at the replication systems FIRST, then you've
> missed a
> few steps.  Look at what the databases, sans replication, can bring to
> the table, and what they lack.
>
> If you MUST have a database that checks input against check
> constraints,
> then MySQL is out.  It simply doesn't support them.
>
> If you need a database that converts the number 234987234987234987234
> into 2147483647 when you insert it into an int field and doesn't throw
> an error or warning, then MySQL should be your choice.
>
> How important is good transactional performance?  MySQL has serious
> limitations for certain storage engines and you need to compare those
> things before looking at replication.
>
> Don't get me wrong, if replication is one of the things you need, then
> consider it, but if you're putting bad data into your database, what
> good is replicating it gonna do ya?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Note that pgcluster is statement-based, which has some drawbacks. AFAIK
MySQL's 'clustering' is as well.

Many people use Slony to replicate to many slaves and use pgpool to hit
them. But remember if you do that you need to make sure any statement
that changes data hits your master and not the slaves. Command Prompt's
replication solution might also be useful.

On Tue, Oct 11, 2005 at 10:23:32AM -0600, Aly S.P Dharshi wrote:
> Hello Travis,
>
>     I don't know if there are a Oracle RAC style cluster system for
> PGSQL but this software that can do something similar,
> http://pgcluster.projects.postgresql.org/feature.html may help.
>
>     You can always use Slony for replication services.
>
>     Cheers,
>
>     Aly.
>
> On Mon, 10 Oct 2005, Travis Brady wrote:
>
> >All,
> >
> >Forgive me if this has been answered before, but I've searched the archives
> >and the net extensively and have come up mostly empty so far.
> >
> >I'm working at convincing my firm to implement a postgresql database
> >cluster.
> >Specifically, we'd like to get a few machines running to be more available
> >and to protect against any kind of failure.
> >
> >Right now people are leaning toward MySQL and Emic, but I've been
> >evangelizing for postgres for the standard reasons.
> >How does one go about setting this kind of thing up?
> >I've looked at Bizgres and Pervasive but I'm not sure if that's necessarily
> >what we want.
> >
> >thank you,
> >
> >Travis
> >
>
> --
> Aly S.P Dharshi
> aly.dharshi@telus.net
>
>      "A good speech is like a good dress
>       that's short enough to be interesting
>       and long enough to cover the subject"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Tue, Oct 11, 2005 at 11:38:22AM -0500, Scott Marlowe wrote:
>
> Don't get me wrong, if replication is one of the things you need, then
> consider it, but if you're putting bad data into your database, what
> good is replicating it gonna do ya?

But if real, ORAC-style clustering is what you need, buy Oracle.  The
limitations on MySQL's implementation give me the willies.  As Tom
Waits said, "The large print giveth, and the small print taketh
away."

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
        --Philip Greenspun

Andrew,

    I disagree, I wouldn't want to contend with all the complexities
and kludge of Oracle thank you very much. If there was a way to get
PostgreSQL to do better than the current clustering methods, then why not, it would be a
big win for us.

    PostgreSQL *is* an enterprise class DB after all, and we should be
improving upon it to do so and remain so.

    Cheers,

    Aly.

On Thu, 13 Oct 2005, Andrew Sullivan wrote:

>On Tue, Oct 11, 2005 at 11:38:22AM -0500, Scott Marlowe wrote:
>>
>> Don't get me wrong, if replication is one of the things you need, then
>> consider it, but if you're putting bad data into your database, what
>> good is replicating it gonna do ya?
>
>But if real, ORAC-style clustering is what you need, buy Oracle.  The
>limitations on MySQL's implementation give me the willies.  As Tom
>Waits said, "The large print giveth, and the small print taketh
>away."
>
>A
>
>

--
Aly S.P Dharshi
aly.dharshi@telus.net

     "A good speech is like a good dress
      that's short enough to be interesting
      and long enough to cover the subject"

On Thu, Oct 13, 2005 at 10:06:51AM -0600, Aly S.P Dharshi wrote:
> Andrew,
>
>     I disagree, I wouldn't want to contend with all the complexities
> and kludge of Oracle thank you very much. If there was a way to get
> PostgreSQL to do better than the current clustering methods, then why not, it would be a
> big win for us.

I'm not saying we _shouldn't_ go after such functionality (I have
someone reporting to me at work who is in fact doing so).  I'm saying
that if you want that functionality today, you can buy it from one
place, and that's Oracle.  Answers that rely on pretty-good, mostly
works, most of the time, if you use the right table handlers always
and make sure that nobody inserts dates like '2005-02-30', do not
qualify as "a place to buy it from", for the record.  And if "pretty
close" is a good enough answer for you, you don't need this complex
technology at all.  You can use async systems in most cases.

A

PS --  I think MySQL has plenty of good features.  It's a fine
product, loads better than it was in the old days. But the misfeature
of different storage engines, none of which actually achieves all the
other features, is an administration mistake waiting to happen.  It's
what really bothers me about their clustered offering.  Others might
make a different trade-off.  Me, I don't like to be in water over my
head when I'm awakened in the middle of the night.

--
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
        --Dennis Ritchie