PostgreSQL – Transforming Streaming Replication to Cascaded Setup

configurationpostgresqlreplication

I've setup streaming replication as part of HA solution on my production enviroment. I've two ubuntu servers PROD1 and PROD2. PROD1 is master and PROD2 is slave. In case of failover the slave database on PROD2 would be promoted to master database.

What I want is to make the slave database (on PROD2) to upstream server and setup another database on PROD1 as downstream server. The idea is to use the upstream server in case of failover and downstream server in case of fallback.

Please advise what configuration changes would be required to achive the above.

Thanks

Best Answer

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

Related Question