PostgreSQL replication

You are right, PostgreSQL's built-in replication (aka Hot Standby) replicates whole cluster - so it's not suitable in your case.

You will need some trigger-based solution. For example,

• Slony-I - most mature, written in C, flexible, good for complex setups
• SkyTools pgq+londiste - C+Python, more lighweight than Slony-I
• RubyRep - Ruby/JRuby, simple, easy to setup, not so mature

Please note that triggers always cause some extra load, and also initial sync will be equivalent of dumping all replicated tables.

Hope this helps, I'd propose to come back with more detailed questions if you have problems.

Good luck!

1. slaves wont understand new master. you should manually do that.

2. yes they are different and you should create new ones for old master.however old standby will continue to work as a master but you should set max_wal_senders on that node. you should also set pg_hba.conf of the new master after failover. after failover (when nodes changes roles master->slave slave->master), you should transfer new wal files to new standby folders data directory which you set in recovery.conf file. or simply you can use rsync.

3. may be you can use pgbouncer. this way you will just change the pgbouncer server adres to new master.

4. EnterpriseDB has some commercial tools. may be you can check them.

and finally yes you are right. there is no single fully automatic solution to solve these questions.