之前写过一篇文档，是关于如何编写一个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",

　　+ INT4OID, -1, 0);

　　+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "received_lsn",

　　+ LSNOID, -1, 0);

　　+ TupleDescInitEntry(tupdesc, (AttrNumber) 6, "received_tli",

　　+ INT4OID, -1, 0);

　　+ TupleDescInitEntry(tupdesc, (AttrNumber) 7, "last_msg_send_time",

　　+ TIMESTAMPTZOID, -1, 0);

　　+ TupleDescInitEntry(tupdesc, (AttrNumber) 8, "last_msg_receipt_time",

　　+ TIMESTAMPTZOID, -1, 0);

　　+ TupleDescInitEntry(tupdesc, (AttrNumber) 9, "latest_end_lsn",

　　+ LSNOID, -1, 0);

　　+ TupleDescInitEntry(tupdesc, (AttrNumber) 10, "latest_end_time",

　　+ TIMESTAMPTZOID, -1, 0);

　　+ TupleDescInitEntry(tupdesc, (AttrNumber) 11, "slot_name",

　　+ TEXTOID, -1, 0);

　　+

　　+ 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;

　　+ else

　　+ values[4] = LSNGetDatum(received_lsn);

　　+ values[5] = Int32GetDatum(received_tli);

　　+ if (last_send_time == 0)

　　+ nulls[6] = true;

　　+ else

　　+ values[6] = TimestampTzGetDatum(last_send_time);

　　+ if (last_receipt_time == 0)

　　+ nulls[7] = true;

　　+ else

　　+ values[7] = TimestampTzGetDatum(last_receipt_time);

　　+ if (XLogRecPtrIsInvalid(latest_end_lsn))

　　+ nulls[8] = true;

　　+ else

　　+ values[8] = LSNGetDatum(latest_end_lsn);

　　+ if (latest_end_time == 0)

　　+ nulls[9] = true;

　　+ else

　　+ values[9] = TimestampTzGetDatum(latest_end_time);

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

　　+ nulls[10] = true;

　　+ else

　　+ 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 receiver 端的当前时间。last_msg_receipt_time

　　最后一次接收到 wal sender 发送过来的消息的 wal sender 端携带的发消息时的时间。last_msg_send_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