Re: Question on Load balancing

Hello,
I am having a concept question about how the Read/Write process transaction
works within the DB network structure.
Assume I have an environment of one master DB with three slaves running
Slony-I replication server.
As you all know Slony is a Single master to multiple slaves replication
system supporting cascading and slave promotion. Master DB can do both read
and write, but the slaves can only do read and for reporting purpose.

Here is the question of concept that I don't understand, if you have answer
or provide me a link to referral is appreciated.

Q: For application Developer write codes to accessing the DB server to
getting/modify datas. Since there is only one server that can handle both
Read/Write which is the Master and slave is only to retrive data.
How should the developer know which DB server should they connecting
to for either to read or to write? If developer is using the same fixed
connection strings to a fix IP to Master server for Read/Write and another
connection string to a fix IP to (for example) my SLAVE1 server to retrive
datas.
How and what would happen if my SLAVE1 failed or due to maintaince that I
need to replace it?

Also what would happen for Master DB if issues such as failover or switch
over that I need to replacing the master server due to failure or
for maintaince, or to promote one of the slave to master?

I just need answer that tells me how PostgreSQL and Slony would react if
such issues happens. Answer can simply just to provide me a web link for me
to read further. Thanks!

"Net Tree Inc." <nettreeinc(at)gmail(dot)com> writes:
> I just need answer that tells me how PostgreSQL and Slony would
> react if such issues happens. Answer can simply just to provide me a
> web link for me to read further. Thanks!

These aren't notably PostgreSQL or Slony-I questions; they are
questions about how you choose to manage the configuration for your
applications.

That doesn't make them dumb questions, just things for which I don't
believe we can provide any prescriptive answers.

Historically, I know that we ("Afilias folk") have faced exactly these
issues as questions of how to deploy already-designed applications
that weren't designed with these questions in mind.

It's something of a struggle: you need to design the ability to
configure these things into applications in order for it NOT to be a
fairly horrible process to do various forms of failover.

Web applications frequently are pretty "horrible" in this fashion,
particularly when they try to be "user friendly" by putting a GUI in
front of the configuration such that you pretty much have to log into
the application in order to reconfigure it.

The thing I'd counsel is to make sure that the configuration used to
control how your apps access databases is centralized in such a way
that you can change it without having to "jump through burning hoops."

Plausible options include:

- DB connectivity might be defined in a file that can be easily
rewritten by a script. ("XML document containing 18 other
things" is not an example of such a thing...)

- App configuration never changes; it points to a proxy (e.g. -
pgbouncer, pgpool) which redirects it to the "real" database

You can reconfigure pgbouncer/pgpool without "burning hoops" :-)

- Standardize on a host name and port number; app configuration
never changes... But this points to a CNAME so that you may
readily repoint to an alternate server via changing the CNAME
in your DNS configuration.

There are probably other options; these three are reasonable ones off
the top of my head.
--
select 'cbbrowne' || '@' || 'ca.afilias.info';
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)
"Bother," said Pooh, "Eeyore, ready two photon torpedoes and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"

2009/7/23 Net Tree Inc. <nettreeinc(at)gmail(dot)com>:
> Hello,
> I am having a concept question about how the Read/Write process transaction
> works within the DB network structure.
> Assume I have an environment of one master DB with three slaves running
> Slony-I replication server.
> As you all know Slony is a Single master to multiple slaves replication
> system supporting cascading and slave promotion. Master DB can do both read
> and write, but the slaves can only do read and for reporting purpose.
>
> Here is the question of concept that I don't understand, if you have answer
> or provide me a link to referral is appreciated.
>
> Q: For application Developer write codes to accessing the DB server to
> getting/modify datas. Since there is only one server that can handle both
> Read/Write which is the Master and slave is only to retrive data.
> How should the developer know which DB server should they connecting
> to for either to read or to write? If developer is using the same fixed
> connection strings to a fix IP to Master server for Read/Write and another
> connection string to a fix IP to (for example) my SLAVE1 server to retrive
> datas.
> How and what would happen if my SLAVE1 failed or due to maintaince that I
> need to replace it?
>
> Also what would happen for Master DB if issues such as failover or switch
> over that I need to replacing the master server due to failure or
> for maintaince, or to promote one of the slave to master?
>
> I just need answer that tells me how PostgreSQL and Slony would react if
> such issues happens. Answer can simply just to provide me a web link for me
> to read further. Thanks!

In addition to what Christopher said, you might want to look at
http://linux-ha.net/

--
Michael Wood <esiotrot(at)gmail(dot)com>