Postgresql – Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency

I'm going to highlight one of the products listed in the wiki link provided by Jack Douglas.

Galera is a product I've been hearing a lot about recently, but have not had a cause to implement it yet into a production environment.

Back in July, Percona wrote a brief blog about implementing it, but mentioned there was no 'production' release at that time.

They've since released the first major version, v1.0. And Vladimir has been shedding more light on it this month. And with some benchmarks

Due to your write-heavy environment, I highlight this use-case:

Distributing writes across the cluster will harness the CPU power in slave nodes for better use to process client write transactions. Due to the row based replication method, only changes made during a client transaction will be replicated and applying such a transaction in slave applier is much faster than the processing of the original transaction. Therefore the cluster can distribute the heavy client transaction processing across many master nodes and this yields in better write transaction throughput overall.

_{Disclaimer: I'm not affiliate with Galeria or Percona, and have not used it personally...yet.}

This is an old question but figured I would provide some pointers here. The first place to start, naturally, is with the documentation, which is excellent. It will work exactly as written on any supported OS.

This being said, if you are on Windows, some of the specifics of PostgreSQL are sufficiently UNIXy to be confusing. Its thus helpful to remember what streaming replication is in PostgreSQL, which is a continuous disaster recovery which never completes. So in addition to the replication docs you want to read all about point in time recovery and incremental backups.

The key aspects of replication is the recovery.conf which configures the PostgreSQL system to basically continually recover to now() with logs provided by another server. If you think about it from that perspective, the docs and tutorials make more sense. You do need to know where the data directory of Postgresql is however.