Postgresql – How to set up an Amazon RDS for PostgreSQL database to act as a replica to a non-RDS PostgreSQL database

After a week of research I believe Streaming Log Shipping and Hot Standby configuration of PostgreSQL instances serves my needs of instant replication (minimum window of data loss), yet low network traffic. I've written a detailed blog post on how I set it up.

There might be other solutions too, using 3rd party tools like pgpool, but I didn't have much success with them.

That all depends on what your application(s) are and how they go about connecting to the database and running their queries.

Most applications I have seen setup two database connection pools/strings. One for reading and one for writing and the application code directs it's queries to whichever pool suits the query. Most applications are not written like this until they need failover though so this is probably going to need some coding.

You might be able to use mysql-proxy to filter particular queries to a read only back end.

You also might have more immediate gains by running a separate database instance for each of the applications rather than splitting the load via reads, if your load profile allows it.