PostgreSQL wal receiver 統計資訊 patch

之前寫過一篇文檔，關于如何編寫一個c函數，在postgresql hot standby中擷取wal receiver的統計資訊，以及上遊節點的連接配接資訊（conninfo）。http://blog.163.com/digoal@126/blog/static/1638770402015744524871/

postgresql 9.6 把這個功能合到核心了。

patch位址如下：

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b1a9bad9e744857291c7d5516080527da8219854

https://commitfest.postgresql.org/8/447/

主要新增代碼：

為了保證取出狀态的一緻性，需要對&walrcv->mutex加鎖。

+

+/*

+ * return a string constant representing the state. this is used

+ * in system functions and views, and should *not* be translated.

+ */

+static const char *

+walrcvgetstatestring(walrcvstate state)

+{

+   switch (state)

+   {

+       case walrcv_stopped:

+           return "stopped";

+       case walrcv_starting:

+           return "starting";

+       case walrcv_streaming:

+           return "streaming";

+       case walrcv_waiting:

+           return "waiting";

+       case walrcv_restarting:

+           return "restarting";

+       case walrcv_stopping:

+           return "stopping";

+   }

+   return "unknown";

+}

+ * returns activity of wal receiver, including pid, state and xlog locations

+ * received from the wal sender of another server.

+datum

+pg_stat_get_wal_receiver(pg_function_args)

+#define pg_stat_get_wal_receiver_cols  11

+   tupledesc   tupdesc;

+   datum       values[pg_stat_get_wal_receiver_cols];

+   bool        nulls[pg_stat_get_wal_receiver_cols];

+   walrcvdata *walrcv = walrcv;

+   walrcvstate state;

+   xlogrecptr  receive_start_lsn;

+   timelineid  receive_start_tli;

+   xlogrecptr  received_lsn;

+   timelineid  received_tli;

+   timestamptz last_send_time;

+   timestamptz last_receipt_time;

+   xlogrecptr  latest_end_lsn;

+   timestamptz latest_end_time;

+   char       *slotname;

+   /* no wal receiver, just return a tuple with null values */

+   if (walrcv->pid == 0)

+       pg_return_null();

+   /* initialise values and null flags arrays */

+   memset(values, 0, sizeof(values));

+   memset(nulls, 0, sizeof(nulls));

+   /* initialise attributes information in the tuple descriptor */

+   tupdesc = createtemplatetupledesc(pg_stat_get_wal_receiver_cols, false);

+   tupledescinitentry(tupdesc, (attrnumber) 1, "pid",

+                      int4oid, -1, 0);

+   tupledescinitentry(tupdesc, (attrnumber) 2, "status",

+                      textoid, -1, 0);

+   tupledescinitentry(tupdesc, (attrnumber) 3, "receive_start_lsn",

+                      lsnoid, -1, 0);

+   tupledescinitentry(tupdesc, (attrnumber) 4, "receive_start_tli",

+   tupledescinitentry(tupdesc, (attrnumber) 5, "received_lsn",

+   tupledescinitentry(tupdesc, (attrnumber) 6, "received_tli",

+   tupledescinitentry(tupdesc, (attrnumber) 7, "last_msg_send_time",

+                      timestamptzoid, -1, 0);

+   tupledescinitentry(tupdesc, (attrnumber) 8, "last_msg_receipt_time",

+   tupledescinitentry(tupdesc, (attrnumber) 9, "latest_end_lsn",

+   tupledescinitentry(tupdesc, (attrnumber) 10, "latest_end_time",

+   tupledescinitentry(tupdesc, (attrnumber) 11, "slot_name",

+   blesstupledesc(tupdesc);

+   /* take a lock to ensure value consistency */

+   spinlockacquire(&walrcv->mutex);

+   state = walrcv->walrcvstate;

+   receive_start_lsn = walrcv->receivestart;

+   receive_start_tli = walrcv->receivestarttli;

+   received_lsn = walrcv->receivedupto;

+   received_tli = walrcv->receivedtli;

+   last_send_time = walrcv->lastmsgsendtime;

+   last_receipt_time = walrcv->lastmsgreceipttime;

+   latest_end_lsn = walrcv->latestwalend;

+   latest_end_time = walrcv->latestwalendtime;

+   slotname = pstrdup(walrcv->slotname);

+   spinlockrelease(&walrcv->mutex);

+   /* fetch values */

+   values[0] = int32getdatum(walrcv->pid);

+   if (!superuser())

+       /*

+        * only superusers can see details. other users only get the pid

+        * value to know whether it is a wal receiver, but no details.

+        */

+       memset(&nulls[1], true, pg_stat_get_wal_receiver_cols - 1);

+   else

+       values[1] = cstringgettextdatum(walrcvgetstatestring(state));

+       if (xlogrecptrisinvalid(receive_start_lsn))

+           nulls[2] = true;

+       else

+           values[2] = lsngetdatum(receive_start_lsn);

+       values[3] = int32getdatum(receive_start_tli);

+       if (xlogrecptrisinvalid(received_lsn))

+           nulls[4] = true;

+           values[4] = lsngetdatum(received_lsn);

+       values[5] = int32getdatum(received_tli);

+       if (last_send_time == 0)

+           nulls[6] = true;

+           values[6] = timestamptzgetdatum(last_send_time);

+       if (last_receipt_time == 0)

+           nulls[7] = true;

+           values[7] = timestamptzgetdatum(last_receipt_time);

+       if (xlogrecptrisinvalid(latest_end_lsn))

+           nulls[8] = true;

+           values[8] = lsngetdatum(latest_end_lsn);

+       if (latest_end_time == 0)

+           nulls[9] = true;

+           values[9] = timestamptzgetdatum(latest_end_time);

+       if (*slotname == '\0')

+           nulls[10] = true;

+           values[10] = cstringgettextdatum(slotname);

+   /* returns the record as datum */

+   pg_return_datum(heaptuplegetdatum(

+                         heap_form_tuple(tupdesc, values, nulls)));

用法參考：

http://www.postgresql.org/docs/devel/static/monitoring-stats.html#pg-stat-wal-receiver-view

可以擷取到的資訊，

wal receiver的pid。

目前的狀态（停止，正在啟動，正在接收，正在等待，重新開機中，停止中，未知）。

wal receiver程序啟動時的wal需接收位置receive_start_lsn，時間線receive_start_tli。

目前wal receiver已接收并flush到磁盤的wal 位置received_lsn，時間線received_tli。

最後一次接收到 wal sender 發送過來的消息的 wal sender  端攜帶的發消息時的時間。last_msg_send_time

最後一次接收到 wal sender 發送過來的消息的 wal receiver 端的目前時間。last_msg_receipt_time

(last_msg_receipt_time - last_msg_send_time)就是網絡延遲。（當然前提是兩個伺服器的時鐘一緻。）

最後一次feed back給wal sender的 wal 已接收位置 latest_end_lsn

最後一次feed back給wal sender的 wal 已接收時間戳 latest_end_time

上遊節點的slot name。

table 27-5. pg_stat_wal_receiver view

column

type

description

pid

integer

process id of the wal receiver process

status

text

activity status of the wal receiver process

receive_start_lsn

pg_lsn

first transaction log position used when wal receiver is started

receive_start_tli

first timeline number used when wal receiver is started

received_lsn

last transaction log position already received and flushed to disk, the initial value of this field being the first log position used when wal receiver is started

received_tli

timeline number of last transaction log position received and flushed to disk, the initial value of this field being the timeline number of the first log position used when wal receiver is started

last_msg_send_time

timestamp with time zone

send time of last message received from origin wal sender

last_msg_receipt_time

receipt time of last message received from origin wal sender

latest_end_lsn

last transaction log position reported to origin wal sender

latest_end_time

time of last transaction log position reported to origin wal sender

slot_name

replication slot name used by this wal receiver

the pg_stat_wal_receiver view will contain only one row, showing statistics about the wal receiver from that receiver's connected server.