Failover replication building a new master

From: Tom Loder <tom(dot)loder(at)servelec-technologies(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Failover replication building a new master
Date: 2018-06-05 12:43:10
Message-ID: ED127BEF191D73498D32229AD28B3554BF60764F@GSH-EXD-02.cse-servelec.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am using Postgres 10.4, and using replication, I have managed to set up four servers, with one running as a master and the other three running with streaming replication from the master.

I have used the command:
psql -c "ALTER SYSTEM SET synchronous_standby_names TO 'FIRST 1(S2,S3,S4)';" so that at least one of the three slave clusters are SYNCED to the master.

If I run the following command I get the following back:

SELECT pid, usename, application_name as name,state, client_addr, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_priority, sync_state FROM pg_stat_replication;

pid | usename | name | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | sync_priority | sync_state
-------+---------+-------+-----------+-------------+-----------+-----------+-----------+------------+---------------+------------
26215 | replica | S3 | streaming | 0/6000000 | 0/6000000 | 0/6000000 | 0/6000000 | 2 | potential
26200 | replica | S4 | streaming | 0/6000000 | 0/6000000 | 0/6000000 | 0/6000000 | 3 | potential
26186 | replica | S2 | streaming | 0/6000000 | 0/6000000 | 0/6000000 | 0/6000000 | 1 | sync

When I take out my master server (S1) I setup S2 as the master using a bash script and I update the recovery.conf files on the other two servers to change the replication to run from S2.
I have changed the setting for synchronouse_standby_names using:

psql -c "ALTER SYSTEM SET synchronous_standby_names TO 'FIRST 1(S1,S3,S4)';" hoping that one of the remaining two or possibly the old master will SYNC to the new master (S2).

However when I check on S2 I get the following output from pg_stat_replication:

pid | usename | name | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | sync_priority | sync_state
------+---------+-------+---------+-------------+-----------+-----------+-----------+------------+---------------+------------
6418 | replica | S4 | startup | 0/D000098 | 0/D000098 | 0/D000098 | 0/D000098 | 3 | potential
6417 | replica | S3 | startup | 0/D000098 | 0/D000098 | 0/D000098 | 0/D000098 | 2 | potential

If I also get the original master (S1) running and set it up as a Slave this also will not "SYNC". How can I get the standby servers to SYNC to the new Master (S2) without doing a new BaseBackup from S2 to the other servers?

Thanks

Tom

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2018-06-05 13:08:22 Re: Postgres 11 beta - no JITing
Previous Message Andres Freund 2018-06-05 12:34:53 Re: Postgres 11 beta - no JITing