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

On Sunday 22 August 2004 21:44, you wrote:
> After a long battle with technology, mendola(at)bigfoot(dot)com (Gaetano Mendola),
an earthling, wrote:
> > | MySQL does not allow cross-server database connection such as dblink.
> > | So, we're thinking of 3 alternatives:
> > |
> > | 1) Wait for MySQL clustering to be stable and put all our databases in
> > | the cluster
> > | 2) Migrate to PostgreSQL and use dblink to solve the referential
> > | integrity 3) Migrate to PostgreSQL clustering solution
> >
> > May I know why are you sticky on the idea of spread your database
> > among various servers ? Free your mysql-minded. If you idea is an
> > horizontal scale solution then open your wallet and buy Oracle.
> > Postgresql scale very well vertically.
>
> Indeed.
>
> It seems quite unclear what the proposed merit of "clustering" is
> _supposed_ to be, and I think it bears asking Jim Worke to explain in
> more detail what they're trying to accomplish by it.

Sorry. Been rather confused with so many things that need to be done,
sometimes I focused on what I want rather than what I need.

>
> - If the goal is improved redundancy, then I'd argue that doing all
> the updates on the "master" and having several "hot replicas" being
> maintained using Slony-I would accomplish that without technology
> changes.

Yes indeed it would and I'm going to try it out soon :)

Actually we're also planning an exercise to replicate the database to a data
recovery site. When everything is ready, we'd like the new site to be the
primary site (the clients will be switched to the new site at an instance by
my ISP). That's why I was thinking that, if there is a multi-master
replication, the clients can be switched to the new site and the old site
still have the new data. So any transaction consolidation would not be
disrupted.

I guess I should've said the above earlier rather than ask ABOUT the
solution :)

>
> - If the goal is to somehow get better performance by partitioning
> work across multiple servers, then this can be attained by having
> those several "hot replicas," and directing as many read-only
> operations to replicas as possible. Updates have to go to the
> "master;" by dropping out query load, that lets the "master" be
> occupied primarily with write operations.

I was hoping not having to change the application code by changing queries to
one server and updates to another server.

>
> - System reliability does NOT improve if write operations are
> spread across several servers. We have been setting up extra
> replicas of some databases on some new servers lately, and people
> that _haven't_ thought it out have briefly imagined it a good idea
> to spread the 'masters' across more servers, which would _hurt_
> reliability, in fact.
>
> The situation we have is that our app needs access to two
> databases: one that stores "state," and another that logs activity.
>
> The theory that people come up with is that when we have 3 main "big
> servers," we should write the "state" to one, and logs to another.
>
> Reality rears its ugly head: Doing that makes the system more
> vulnerable, as if _either_ of those servers goes down, that will
> cause the application to go into convulsions. Putting "state" and
> "logs" on the same server, and replicating everywhere else is, in
> fact, the more reliable choice. If the ONE server that's "master"
> goes down, the application won't be happy, but that was always going
> to be the case.
>
> The point: Distributing writes across many hosts makes the system
> vulnerable to the possibility of _any_ of them going down.

Yes, that is true. So separating databases across servers shouldn't be the
way to go. Thanks.