On Fri, Apr 30, 2004 at 01:17:16PM -0400, CS Wagner wrote:
> I assume this issue has been solved repeatedly, but I cannot find any
> information on it...
>
> I have a website that runs reports on data from a postgres database. An
> average report takes about 10 minutes. I'd like to load balance it so I
> can have multiple people run reports without causing the login request
> to take forever. Also, it is rather important that data updated in the
> database be updated on all mirrors of it immediately - not with an
> hourly or daily update.
>
> What I would most desire is a program that pretends to be a postgres
> server. I can log into it with psql, a jdbc driver, or php's
> pg_connect. When I do a select (no update) command, it will send that
> off to the least loaded mirror. When I do an update/insert/delete, it
> hits all the mirrors. Also, it can designate one database (most likely
> local to this pretend server) as the master so I can easily clone it to
> make more mirrors.
>
> Does anything remotely similar to that exist?
Sounds like dbbalancer, but I've no idea whether that's ready for
production use. I supect not.
http://sqlrelay.sourceforge.net/sqlrelay/ may be worth a look for the
"select" side of things. It's high performance and lightweight, and
supports most database access APIs.
To keep the databases in sync you'll need some form of replication to
keep the data consistent on all the mirrors. There are several
master-slave replication solutions for postgresql. Slony is one I've
seen mentioned a lot recently. If you're careful about the update
queries you make you could also replicate at the application level,
which has some different tradeoffs compared to master-slave database
replication.
Cheers,
Steve