Database Mirroring / Postgres Streaming Replication

Question

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 1

pgpool-II can handle this for your:

Check this example.