On 06/05/2018 05:43 AM, Tom Loder wrote:
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 am not sure what is going on here as I am not that familiar with
synchronous replication. I do think it would aid those that can help if
you provided the contents of :
1) The Bash script
2) The recovery.conf file.
Also are there relevant log entries for S2, S3 and S4?
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
--
Adrian Klaver
adrian.kla...@aklaver.com
