Nuxeo with a fault-tolerant PostgreSQL configuration
With the recent release of PostgreSQL 9.2, repmgr 2.0-beta (http://www.postgresql.org/about/news/1411/), and JDBC4 Postgresql Driver Version 9.2-1000 (with basic failover support), does Nuxeo have plans to test and/or document a fault-tolerant Nuxeo/PostgreSQL configuration with master/hot-standby PostgreSQL servers and automatic failover.
We'd love to but the problem is that the hot standby is still read-only, which prevents even a transaction
BEGIN from being executed on it. As the Nuxeo framework is pluggable, we don't have any way of knowing for a given HTTP request if the whole work to be executed will be read-only w.r.t. the database, or if somewhere a listener will want to write data. So in all cases we have to start a transaction very early, which prevents a hot standby from being used.
A way to improve this would be to have at the URL level some way of discriminating between requests that are going to be purely read-only and those who don't, but that's not something we have today.
Postgres-XC may be a better bet, but we haven't tested it yet.
When the master fails, the goal would be to promote the hot-standby to become the new master and have Nuxeo seemlessly failover to the new master. I believe this is doable with the latest releases of these PostgreSQL components/addons. It would be great if Nuxeo tested and documented a high-availability/failover configuration using these components or others.