PostgreSQL Synchronous replication Failure test

One, Standby synchronous replication node down

postgres=# select * from pg_stat_replication ;  pid  |  Usesysid | usename  | application_name | client_addr | client_ Hostname | client_port |         backend_start          | backend_xmin |   state    | sent_location | write_location | flush_location | replay_ location | sync_priority | sync_state -------+----------+----------+----------------- -+-------------+-----------------+-------------+-------------------------------+--------------+-----------+---- -----------+----------------+----------------+-----------------+---------------+------------ 12262 |        10 | postgres | node3             | 172.17.5.47 |                  |       48691 | 2015-09-06  14:44:52.674976+08 |       400592 | streaming |  1/740002E8    | 1/740002E8     | 1/740002E8      | 1/740002E8      |              2 | potential 12263 |        10 | postgres | node2             | 172.17.5.46 |                  |       35271 |  2015-09-06 14:44:52.677004+08 |       400592 | streaming | 1/740002e8     | 1/740002E8     | 1/740002E8      | 1/740002E8      |              1 | sync (2 rows) postgres=# create table  test1  ( id bigint); create tablepostgres=# \d test1    table  "Public.test1"  Column  |  Type  | Modifiers --------+--------+----------- id      | bigint |

Viewing on Mastrer is working properly, now put 172.17.5.47 node Down

pg_ctl-d/data/pg940_data/-MF Stop

Verify on Master

postgres=# select * from pg_stat_replication ;  pid  |  Usesysid | usename  | application_name | client_addr | client_ Hostname | client_port |         backend_start          | backend_xmin |   state    | sent_location | write_location | flush_location | replay_ location | sync_priority | sync_state -------+----------+----------+----------------- -+-------------+-----------------+-------------+-------------------------------+--------------+-----------+---- -----------+----------------+----------------+-----------------+---------------+------------ 12263 |        10 | postgres | node2             | 172.17.5.46 |                  |       35271 | 2015-09-06  14:44:52.677004+08 |       400593 | streaming |  1/74001678    | 1/74001678     | 1/74001678      | 1/74001678      |              1 | sync (1 row) postgres=# create  table test2  ( id bigint); create tablepostgres=# \d test2     Table  "Public.test2"  Column |  Type  | Modifiers ---- ----+--------+----------- id     | bigint |

Master node and synchronous replication slave node can work correctly

Second, synchronous replication slave node down

postgres=# select * from pg_stat_replication ;  pid  |  Usesysid | usename  | application_name | client_addr | client_ Hostname | client_port |         backend_start          | backend_xmin |   state    | sent_location | write_location | flush_location | replay_ location | sync_priority | sync_state -------+----------+----------+----------------- -+-------------+-----------------+-------------+-------------------------------+--------------+-----------+---- -----------+----------------+----------------+-----------------+---------------+------------ 12377 |        10 | postgres | node3             | 172.17.5.47 |                  |       55722 | 2015-09-06  15:10:02.882202+08 |       400594 | streaming |  1/740029F0    | 1/740029F0     | 1/740029F0      | 1/740029F0      |              2 | potential 12263 |        10 | postgres | node2             | 172.17.5.46 |                  |       35271 |  2015-09-06 14:44:52.677004+08 |       400594 | streaming | 1/740029f0     | 1/740029F0     | 1/740029F0      | 1/740029F0      |              1 | sync (2 rows) postgres=# create table  test3  ( id bigint ); create tablepostgres=# \d test3    table  "Public.test3"  Column  |  Type  | Modifiers --------+--------+----------- id      | bigint |

Viewing on Mastrer is working properly, now put 172.17.5.46 node Down

pg_ctl-d/data/pg940_data/-MF Stop

Verify on Master

postgres=# select * from pg_stat_replication ;  pid  |  Usesysid | usename  | application_name | client_addr | client_ Hostname | client_port |         backend_start          | backend_xmin |   state    | sent_location | write_location | flush_location | replay_ location | sync_priority | sync_state -------+----------+----------+----------------- -+-------------+-----------------+-------------+-------------------------------+--------------+-----------+---- -----------+----------------+----------------+-----------------+---------------+------------ 12377 |        10 | postgres | node3             | 172.17.5.47 |                  |       55722 | 2015-09-06  15:10:02.882202+08 |       400595 | streaming |  1/74004C10    | 1/74004C10     | 1/74004C10      | 1/74004C10      |              2 | sync (1 row) postgres=# create  table test4  ( id bigint );   create tablepostgres=# \d test4     Table  "Public.test4"  Column |  Type  |  modifiers --------+--------+----------- id     | bigint |

You can see that the synchronous replication node has switched to NODE3 and is working properly.

Three, the slave node is down

postgres=# select * from pg_stat_replication ;  pid  |  Usesysid | usename  | application_name | client_addr | client_ Hostname | client_port |         backend_start          | backend_xmin |   state    | sent_location | write_location | flush_location | replay_ location | sync_priority | sync_state -------+----------+----------+----------------- -+-------------+-----------------+-------------+-------------------------------+--------------+-----------+---- -----------+----------------+----------------+-----------------+---------------+------------ 12377 |        10 | postgres | node3             | 172.17.5.47 |                  |       55722 | 2015-09-06  15:10:02.882202+08 |       400596 | streaming |  1/74005F38    | 1/74005F38     | 1/74005F38      | 1/74005F38      |              2 | sync (1 row) postgres=# create  table test5  ( id bigint ); create tablepostgres=# \d test5    table  "Public.test5"  Column  |  Type  | Modifiers --------+--------+----------- id      | bigint |

Viewing on Mastrer is working properly, now put 172.17.5.47 nodes also down

pg_ctl-d/data/pg940_data/-MF Stop

Verify on Master

postgres=# select * from Pg_stat_replication; PID | Usesysid | Usename | Application_name | client_addr | Client_hostname | Client_port | Backend_start | Backend_xmin | State | sent_location | write_location | flush_location | replay_location | sync_priority | Sync_state-----+----------+---------+------------------+-------------+-----------------+-------------+-------- -------+--------------+-------+---------------+----------------+----------------+-----------------+------------ ---+------------(0 rows) postgres=# CREATE TABLE Test6 (id bigint);

Master cannot be updated, it waits until one node in Synchronous_standby_names connects to the master node, but can be canceled by CTRL + C, which commits locally

^ccancel request sentwarning:canceling Wait for synchronous replication due to user requestdetail:the transaction have Already committed locally, but might not has been replicated to the standby.  CREATE tablepostgres=# \d test6 Table "Public.test6" Column | Type | Modifiers--------+--------+-----------ID | bigint |

Iv. Conclusion

In synchronous replication:

1. The synchronization of the slave node will switch the latter node to the synchronization node;

2. The non-synchronous slave node is down and does not switch;

3. If the slave node is not all down, it will not affect the use of master, if all down, master will not be able to commit.

PostgreSQL Synchronous replication Failure test