Postgresql – Using postgres_fdw to connect from one read replica to another

On PostgreSQL 9.3, you could use postgres_fdw to transparently query the foreign table on the other machine.

On older versions, dblink can be an option as mentioned by Andrew.

Another option is to use a tool like Londiste or Slony-I to replicate the tables you want. I recommend using Londiste for this, it's going to be much simpler. It creates triggers on the table to detect insert/update/delete, and replicates these using its own client/server and a queueing system to the other database, where it does corresponding insert/update/deletes. I use it in production on several client sites and it works very well.

A future option (hopefully in PostgreSQL 9.5) will be log streaming logical replication, logical changeset extraction and bidirectional replication, which will allow individual databases or tables to be replicated at the SQL level. Part of the work for this was committed to PostgreSQL 9.4, but not enough to make it useful for what you want to do.

AFAIK, you're right that AWS RDS Aurora (a MySQL 5.6 fork) does not support automatic or transparent read/write splitting: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Aurora.html

In order to do that in a way that's completely transparent to the application, you would need an intermediate proxy. Your application would then always connect to the proxy, the proxy would then have to do packet inspection to examine each incoming query to determine if it's read-write, which then gets forwarded on to the master, or read-only, which can then get forwarded to any of N replicas.

Be aware that this has some notable implications: 1. This means that the proxy needs to understand the MySQL protocol 2. It needs to inspect each packet (query) and determine if it's RW or RO 3. It then needs to forward the query to the appropriate backend MySQL instance 4. It likely needs to keep track of each connection, maintaining a map of front-end connections between your app and the proxy, and backend connections to the mysqld instances. The front-end connection would remain stable, but the backend connection could change for each query. 5. You can potentially have some state issues as a result. For example, when you start an explicit transaction, create temporary tables, or set session variables in your connection... those could get lost when (transparently) switching backends. 6. This will have an impact on SSL and other security measures, as you are explicitly using a MITM 7. All of this typically adds quite a bit of overhead, and you will typically see noticeable query latency because of it.

This is a feature that we hope to have in the MySQL Router (the replacement for the old MySQL Proxy: http://mysqlhighavailability.com/mysql-router-on-labs-the-newest-member-of-the-mysql-family/), but we do not yet. It takes a lot of time and effort to do it properly, so as to minimize the effects noted above. One such proxy that does support that today is ProxySQL: http://www.proxysql.com (See the "Read write split" section)

You can grab the source and start playing with it here: https://github.com/renecannao/proxysql

Good luck!