MySQL Master to Master Replication Setup with Automatic Failover

I'm thinking of doing exactly as you describe using Amazon's Route 53 DNS Failover. So the idea is to setup 2 databases as master-master

db1.domain.com db2.domain.com

Then do a Route53 "failover" set on

db.domain.com -> primary is db1.domain.com => TCP-based health check on port 3306 -> secondary is db2.domain.com => TCP-based health check on port 3306

I have not gotten to this point yet but that is my plan

For a standard cascading replication setup, you need to be on at least PostgreSQL 9.2.

You will also need to set the listen_addresses to something other than localhost, adjust the max_wal_senders parameter in your postgresql.conf to accommodate the additional connection(s), and allow replication connections in your pg_hba.conf.

You'll also want to have WAL archiving set up on your primary, moving the WAL segments to somewhere accessible to the other replicas. This is to ensure that your replicas don't get so far out of sync with your primary that they can no longer catch up due to WAL segment recycling. Also, with WAL archiving, versions before 9.3 can follow the timeline switches without issue.

From there, in your recovery.conf, change the primary_conninfo parameter to point at the cascaded replica.

standby_mode      = 'on'
primary_conninfo  = 'host=<parent_streaming_replica_ip> port=5432 user=replication password=********* sslmode=require' # remove sslmode if you're not enabling ssl
trigger_file      = '/tmp/failover.postgresql.5432'
# To keep in sync with the primary in case of network issues
restore_command   = 'cp /path/to/my/wal/segment/archive/%p %f'
# Follow the latest timeline in the WAL archive in case of a switch.
recovery_target_timeline = 'latest'

As I understand your question, you're proposing a replication topology that looks like this:

Server 1                                                      Server 2    
-------------------------------------                         _______________________________________
|PROD1 (primary)                    |------------------------>| PROD2 (secondary streaming replica) |
|                                   |                         |                                     |
|PROD3 (cascaded streaming replica) |<------------------------|                                     |
-------------------------------------                         ---------------------------------------              

This would create extra contention for resources between the cascaded replica and the primary, and not give you any extra guaratees of high availability, since two of your three PostgreSQL instances are sharing the same hardware.

It would be better to have a third server, and have that follow the second server's timeline, like this:

Server 1 -----> Server 2 ----> Server 3

So that when you fail over from Server 1 to Server 2, you still have a streaming replica available via Server 3. Once you've fixed the issues with Server 1, you can set it up as a cascading replica of Server 3, as you had when you originally set up Server 3, so that your topology now looks like this:

Server 2 -----> Server 3 ----> Server 1

Alternately, if you don't have a third server available, you can take a new basebackup of the primary PROD2, and set up the former primary as a new secondary PROD1. You could also use pg_rewind, depending on your version of PostgreSQL (9.3+), which would save needing to run a basebackup if that is available to you.

You can find more information out about following timeline switches and why having a WAL archive with streaming replication is a great idea:

Waiting for 9.3: Allow a streaming replication standby to follow a timeline switch

Offsite replication problems and how to solve them -- Streaming replication with WAL shipping example, showing failure modes.

And basics on setup of replication (both streaming and log shipping) are listed on the PostgreSQL Wiki: Binary Replication Tutorial