標籤:
一、 備用同步複製節點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 |
在mastrer上查看是正常工作的,現在把172.17.5.47節點停機
pg_ctl -D /data/pg940_data/ -mf stop
在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節點和同步複製slave節點可以正常工作
二、 同步複製slave節點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 |
在mastrer上查看是正常工作的,現在把172.17.5.46節點停機
pg_ctl -D /data/pg940_data/ -mf stop
在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 |
可以看出同步複製節點切換到了node3,並且是可以正常工作的
三、 slave節點都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 |
在mastrer上查看是正常工作的,現在把172.17.5.47節點也停機
pg_ctl -D /data/pg940_data/ -mf stop
在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上不能更新了,會一直等待,直到synchronous_standby_names中的一個節點串連上master節點,但是可以ctrl+c取消,那樣會進行本地提交
^CCancel request sentWARNING: canceling wait for synchronous replication due to user requestDETAIL: The transaction has already committed locally, but might not have been replicated to the standby.CREATE TABLEpostgres=# \d test6 Table "public.test6" Column | Type | Modifiers --------+--------+----------- id | bigint |
四、 結論
在同步複製中:
同步的slave節點down掉會將後面的節點切換為同步節點;
非同步slave節點down掉不會進行切換;
如果slave節點沒有全部down掉,不會影響master的使用,如果都down掉的話,master則不能進行提交。
PostgreSQL同步複製故障測試