博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
PostgreSQL wal receiver 统计信息 patch
阅读量：6232 次
发布时间：2019-06-22

本文共 6444 字，大约阅读时间需要 21 分钟。

之前写过一篇文档，关于如何编写一个C函数，在PostgreSQL hot standby中获取wal receiver的统计信息，以及上游节点的连接信息（conninfo）。
PostgreSQL 9.6 把这个功能合到内核了。
patch地址如下：
主要新增代码：
为了保证取出状态的一致性，需要对&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)));
+}
用法参考：
可以获取到的信息，
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.

转载地址：http://fyena.baihongyu.com/

你可能感兴趣的文章
VIM复制指令yank
查看>>
【网络编程6】Java与C语言套接字Socket通信的例子
查看>>
Linux常用开发服务器的代码[Linux zhoulifa ]
查看>>
通过反射克隆对象，对象复制（克隆），对象合并工具类 升级版
查看>>
企业网络安全浅析
查看>>
Oracle常用sql语句(三）之子查询
查看>>
搞定IT基础设施方案 云计算先行
查看>>
Improving (network) I/O performance ...
查看>>
sql server 2008 不允许保存更改，您所做的更改要求删除并重新创建以下表 的解决办法...
查看>>
innodb的文件组成
查看>>
云计算信任危机下的不安
查看>>
罗永浩：锤子起死回生在 2017，现在是抢手“香饽饽儿”
查看>>
MHA failover GTID 专题
查看>>
如何在windows中使用cmd命令去编译，运行C++程序
查看>>
《机器人自动化：建模、仿真与控制》——导读
查看>>
BitTorrent可被用来放大拒绝服务攻击
查看>>
Web 缓存欺骗攻击技术详解
查看>>
容器网络方面的挑战和要求
查看>>
晶澳与马士基强强联合 全球业务进一步升级
查看>>
希捷固态硬盘：另类主控重出江湖
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2025-01-08 21:34:34   当前IP: 18.216.186.244   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我