Database Mirroring / Postgres Streaming Replication

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 receiv开发者_运维问答es 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.

pgpool-II can handle this for your:

Check this example.

The PostgreSQL 9 documentation gives a nice overview of the options available.

http://www.postgresql.org/docs/9.0/static/different-replication-solutions.html

You might want to look at Slony if you do not mind having to write to the master and not the slave, or if you want, check out any of the multi-master replication technologies available.