之前写过一篇文档，关于如何编写一个C函数，在PostgreSQL hot standby中获取wal receiver的统计信息，以及上游节点的连接信息（conninfo）。
http://blog.163.com/digoal@126/blog/static/1638770402015744524871/
PostgreSQL 9.6 把这个功能合到内核了。
patch地址如下：
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 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 | integer | First timeline number used when WAL receiver is started |
| received_lsn | pg_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 | integer | 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 | timestamp with time zone | Receipt time of last message received from origin WAL sender |
| latest_end_lsn | pg_lsn | Last transaction log position reported to origin WAL sender |
| latest_end_time | timestamp with time zone | Time of last transaction log position reported to origin WAL sender |
| slot_name | text | 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.