pgsql之pg_stat_replication的使用詳解

pg_stat_replication是一個(gè)視圖，主要用于監(jiān)控一個(gè)基于流的設(shè)置，建議您 注意系統(tǒng)上稱(chēng)作pg_stat_replication的視圖。（注：當(dāng)前版本為pg 10.0,10.0以下版本，字段名會(huì)有差異）此視圖包含以下信息：

\d pg_stat_replication

每個(gè)字段代碼的含義：

• pid 這代表負(fù)責(zé)流連接的wal_sender進(jìn)程的進(jìn)程ID。如果您在您的操作系統(tǒng)上檢查您進(jìn)程表，您應(yīng)該會(huì)找到一個(gè)帶有那個(gè)號(hào)碼的PostgreSQL進(jìn)程。

• usesysid 每個(gè)內(nèi)部用戶(hù)都有一個(gè)獨(dú)一無(wú)二的編號(hào)。該系統(tǒng)的工作原理很像UNIX。 usesysid 是 (PostgreSQL) 用戶(hù)連接到系統(tǒng)的唯一標(biāo)識(shí)符。

• usename  (不是用戶(hù)名, 注意少了 r)它存儲(chǔ)與用戶(hù)相關(guān)的 usesysid 的名字。這是客戶(hù)端放入到連接字符串中的東西。

• application_name這是同步復(fù)制的通常設(shè)置。它可以通過(guò)連接字符串傳遞到master。

• client_addr它會(huì)告訴您流連接從何而來(lái)。它擁有客戶(hù)端的IP地址。

• client_hostname除了客戶(hù)端的IP，您還可以這樣做，通過(guò)它的主機(jī)名來(lái)標(biāo)識(shí)客戶(hù)端。您可以通過(guò)master上的postgresql.conf中的log_hostname啟用DNS反向查找。

• client_port這是客戶(hù)端用來(lái)和WALsender進(jìn)行通信使用的TPC端口號(hào)。 如果不本地UNIX套接字被使用了將顯示-1。

• backend_start它告訴我們slave什么時(shí)間創(chuàng)建了流連接。

• state此列告訴我們數(shù)據(jù)的連接狀態(tài)。如果事情按計(jì)劃進(jìn)行，它應(yīng)該包含流信息。

• sent_lsn這代表發(fā)送到連接的最后的事務(wù)日志的位置。

• write_lsn這是寫(xiě)到standby系統(tǒng)磁盤(pán)上最后的事務(wù)日志位置。

• flush_lsn這是被刷新到standby系統(tǒng)的最后位置。（這里注意寫(xiě)和刷新之間的區(qū)別。寫(xiě)并不意味著刷新 。）

• replay_lsn這是slave上重放的最后的事務(wù)日志位置。

• sync_priority這個(gè)字段是唯一和同步復(fù)制相關(guān)的。每次同步復(fù)制將會(huì)選擇一個(gè)優(yōu)先權(quán) —sync_priority—會(huì)告訴您選擇了那個(gè)優(yōu)先權(quán)。

• sync_state最后您會(huì)看到slave在哪個(gè)狀態(tài)。這個(gè)狀態(tài)可以是

async, sync, or potential。當(dāng)有一個(gè)帶有較高優(yōu)先權(quán)的同步slave時(shí)，PostgreSQL會(huì)把slave 標(biāo)記為 potential。

在這個(gè)系統(tǒng)視圖中每個(gè)記錄只代表一個(gè)slave。因此，可以看到誰(shuí)處于連接狀態(tài)，在做什么任務(wù)。pg_stat_replication也是檢查slave是否處于連接狀態(tài)的一個(gè)好方法。

上面說(shuō)到pid代表負(fù)責(zé)流連接的wal_sender進(jìn)程的進(jìn)程ID，我們?cè)跈C(jī)器上通過(guò)ps命令查看該進(jìn)程的狀態(tài):

ps -aux|grep 8225

在Linux上我們可以看到那個(gè)進(jìn)程不僅有自己的作用 (在這種情況下, wal_sender)，而且還帶有終端用戶(hù)的名字以及相關(guān)的網(wǎng)絡(luò)連接信息。在上圖中我們可以看到已經(jīng)有人從192.168.47.127（對(duì)應(yīng)pg_stat_replication的client_addr字段）通過(guò)51519(對(duì)應(yīng)pg_stat_replication的client_port字段）)端口連接到了master。

bonus：

上面我們提到replay_lsn是slave上重放的最后的事務(wù)日志位置。

pg_current_wal_lsn（）函數(shù)的作用是獲取當(dāng)前的wal log的寫(xiě)位置。

pg_wal_lsn_diff()函數(shù)的作用是計(jì)算兩個(gè)wal日志之間的差距。

所以我們可以通過(guò)下面的方法獲取高可用架構(gòu)下從庫(kù)的復(fù)制延遲情況：

 SELECT
   pg_wal_lsn_diff(A .c1, replay_lsn) /(1024 * 1024) AS slave_latency_MB
  FROM
   pg_stat_replication,
   pg_current_wal_lsn() AS A(c1)
  WHERE client_addr='%s' and application_name = '%s'
  ORDER BY
   slave_latency_MB
  LIMIT 1;

補(bǔ)充：PostgreSQL pg_stat_replication sync_state introduce

PostgreSQL 9.2引入同步復(fù)制后, pg_stat_replication的sync_state列有3種狀態(tài).

sync

async

potential

分別代表同步standby, 異步standby, 可升級(jí)為同步的standby.

狀態(tài)來(lái)自以下函數(shù) : pg_stat_get_wal_senders

[測(cè)試]

環(huán)境:

1個(gè) primary, 3個(gè) standby.

第一種配置 :

primary配置

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查詢(xún)

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節(jié)點(diǎn)掛掉, 按synchronous_standby_names的順序, 第一個(gè)potential節(jié)點(diǎn)會(huì)變成sync狀態(tài).

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)

當(dāng)test1重新起來(lái)后又會(huì)變成sync狀態(tài).

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查詢(xún)

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變成異步了. 因?yàn)閠est3沒(méi)有配置在primary的synchronous_standby_names 中.

第三種配置 :

primary配置

synchronous_standby_names = 'test1'

standby1配置不變

standby2配置不變

standby3配置不變

primary查詢(xún)

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變成異步了. 因?yàn)閠est2,test3沒(méi)有配置在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");
...略

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。