I am not a DBA; I'm the primary developer of an enterprise database based application.
I'm currently specing out some new machines to upgrade our existing
enterprise database. Currently we're running Postgres 8.4 with a
database at our DR site that receives updates at intervals via some
custom rsync work a former employee performed.
One of the major issues we're trying to tackle is latency between two
global offices. We have staff in NY and staff in London. Currently, the
London staffers suffer the fate of our VPN pipe. I'm not really in a
position to change that infrastructure.
What I'd like to do is move to Postgres 9 and setup streaming
replication. The slave would be in the London office, which would
alleviate read problems for users. The problem I foresee is writes to
the slave (not sure how PG handles this, my understanding is the slave
is in read-only mode). Ideally, writes would be sent to the master in NY
(writes are very infrequent from London, but necessary) by the database
itself. Furthermore, I can setup fallover to have it act as a hot
swappable backup (replacing the DR). Again, all contained within
Postgres configuration with no additional code.
That's my idealized solution. How far off am I? Is this at all possible?
I'm a little overwhelmed by the breadth of this topic and Google is
not really helping me. I would appreciate any advice from some
experienced DBAs, with anecdotes, relevant documentation or examples.
Currently we're using SQLAlchemy as the primary interface to the
database, if that's relevant. It does mean we're not tied to Postgres.
Thanks everyone.
Answer:
No comments:
Post a Comment