Ok, so the scenario is this: there's about 17000 lines of php code driving
this killer-app I'm working on for work, and they want to add database
replication. The database is postgres 7.1.2. Furthermore, they want to do
load-balancing between the replicated servers. So first of all, my question
- has anyone setup any form of replication on postgres? which method did you
use and how well does it work?
I've spent a bit of time looking into this today and discovered that I have
a couple of options available to me. I'll go through them, any feedback is
appreciated.
1. Be hardcore - this involves writing a series of triggers for postgres in
PL/Perl or something to do destructive queries on both servers and use my
"Amortised Random Round Robin" scheduler algorithm to select a server for
each query to go to inside the php code (ie randomly select a server to
send each query to when that query is considered "safe" - ie modifies
nothing). I really really really don't want to do this, because it's
going to be a real pain for stuff like transactions, it also means
classifying each query as safe or not in my php code.
2. As above but using rserv - this is probably easier, but not as good in my
mind because rserv requires me to manually force data to propagate to the
secondary server. not cool at all. It also has a drawback in that if the
primary system fails (pretty unlikely, it's RAIDed and all, but still),
then any transactions not yet comitted to the secondary server are lost.
This is bad news. (I've read through the docs for rserv and it seems taht
this is how it works. If I'm missing something let me know).
3. The best option I've found so far is a program called DBBalancer. This
thing does some seriously cool stuff. It basically works as a proxy to
the database, but it queues connections up to be fed into a connection
pool. It apparently also replicates. If I could get this working (and it
does all it claims) then this would be an ideal solution. Unfortunately
I'm getting linking errors on some libraries it has as dependancies. Has
anyone got this working? Does it work?
4. Finally, I can implement the replication stuff in php. This would really
suck because it would slow down the killer app. It would also mean doing
crazy stuff like having transaction objects and call-backs and stuff. Not
especially elegant. It would also mean re-writing a whole lot of code.
I'm kinda stuck for ideas after that. So, if anyone has got some form of
replication working I'd love to hear about it.
Thanks all,
James.
--
"A one-time pad isn't a cryptosystem: it's a state of mind."
--
SLUG - Sydney Linux User Group Mailing List - http://slug.org.au/
More Info: http://lists.slug.org.au/listinfo/slug
