PostgreSQL 9.2引入同步复制后, pg_stat_replication的sync_state列有3种状态.
sync
async
potential
分别代表同步standby, 异步standby, 可升级为同步的standby.
状态来自以下函数 : pg_stat_get_wal_senders
详见参考部分.
[测试]
环境:
1个 primary, 3个 standby.
第一种配置 :
primary配置
standby1配置
standby2配置
standby3配置
primary查询
如果sync节点挂掉, 按synchronous_standby_names的顺序, 第一个potential节点会变成sync状态.
当test1重新起来后又会变成sync状态.
第二种配置 :
postgresql.conf
synchronous_standby_names = 'test1,test2,test3'standby1配置
primary_conninfo = 'application_name=test1 host=127.0.0.1 port=1999 user=postgres keepalives_idle=60'standby2配置
primary_conninfo = 'application_name=test2 host=127.0.0.1 port=1999 user=postgres keepalives_idle=60'standby3配置
primary_conninfo = 'application_name=test3 host=127.0.0.1 port=1999 user=postgres keepalives_idle=60'primary查询
digoal=# select pid,application_name,client_addr,sync_state from pg_stat_replication;
pid | application_name | client_addr | sync_state
------+------------------+-------------+------------
6311 | test1 | 127.0.0.1 | sync
6321 | test2 | 127.0.0.1 | potential
6391 | test3 | 127.0.0.1 | potential
(3 rows)如果sync节点挂掉, 按synchronous_standby_names的顺序, 第一个potential节点会变成sync状态.
pg_ctl stop -m fast -D /pgdata11999
digoal=# select pid,application_name,client_addr,sync_state from pg_stat_replication;
pid | application_name | client_addr | sync_state
------+------------------+-------------+------------
6564 | test2 | 127.0.0.1 | sync
6568 | test3 | 127.0.0.1 | potential
(2 rows)当test1重新起来后又会变成sync状态.
pg93@db-172-16-3-33-> pg_ctl start -D /pgdata11999
server starting
digoal=# select pid,application_name,client_addr,sync_state from pg_stat_replication;
pid | application_name | client_addr | sync_state
------+------------------+-------------+------------
6564 | test2 | 127.0.0.1 | potential
6605 | test1 | 127.0.0.1 | sync
6568 | test3 | 127.0.0.1 | potential
(3 rows)
第二种配置 :
primary配置
synchronous_standby_names = 'test1,test2'
standby1配置不变
standby2配置不变
standby3配置不变
primary查询
digoal=# select pid,application_name,client_addr,sync_state from pg_stat_replication;
pid | application_name | client_addr | sync_state
------+------------------+-------------+------------
6470 | test1 | 127.0.0.1 | sync
6472 | test3 | 127.0.0.1 | async
6474 | test2 | 127.0.0.1 | potential
(3 rows)
test3变成异步了. 因为test3没有配置在primary的synchronous_standby_names 中.
第三种配置 :
primary配置
synchronous_standby_names = 'test1'
standby1配置不变
standby2配置不变
standby3配置不变
primary查询
digoal=# select pid,application_name,client_addr,sync_state from pg_stat_replication;
pid | application_name | client_addr | sync_state
------+------------------+-------------+------------
6519 | test2 | 127.0.0.1 | async
6521 | test3 | 127.0.0.1 | async
6523 | test1 | 127.0.0.1 | sync
(3 rows)
test2,test3变成异步了. 因为test2,test3没有配置在primary的synchronous_standby_names 中.
[参考]
1. src/backend/replication/walsender.c
/*
* Returns activity of walsenders, including pids and xlog locations sent to
* standby servers.
*/
Datum
pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
{
...略
/*
* More easily understood version of standby state. This is purely
* informational, not different from priority.
*/
if (sync_priority[i] == 0)
values[7] = CStringGetTextDatum("async");
else if (i == sync_standby)
values[7] = CStringGetTextDatum("sync");
else
values[7] = CStringGetTextDatum("potential");
...略
