Skip to content

Commit b1a9bad

Browse files
alvherrealvherre
committed
pgstat: add WAL receiver status view & SRF
This new view provides insight into the state of a running WAL receiver in a HOT standby node. The information returned includes the PID of the WAL receiver process, its status (stopped, starting, streaming, etc), start LSN and TLI, last received LSN and TLI, timestamp of last message send and receipt, latest end-of-WAL LSN and time, and the name of the slot (if any). Access to the detailed data is only granted to superusers; others only get the PID. Author: Michael Paquier Reviewer: Haribabu Kommi
1 parent 6b1a837 commit b1a9bad

File tree

7 files changed

+279
-1
lines changed

7 files changed

+279
-1
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 91 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -300,6 +300,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
300300
</entry>
301301
</row>
302302

303+
<row>
304+
<entry><structname>pg_stat_wal_receiver</><indexterm><primary>pg_stat_wal_receiver</primary></indexterm></entry>
305+
<entry>Only one row, showing statistics about the WAL receiver from
306+
that receiver's connected server.
307+
See <xref linkend="pg-stat-wal-receiver-view"> for details.
308+
</entry>
309+
</row>
310+
303311
<row>
304312
<entry><structname>pg_stat_ssl</><indexterm><primary>pg_stat_ssl</primary></indexterm></entry>
305313
<entry>One row per connection (regular and replication), showing information about
@@ -833,6 +841,89 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
833841
listed; no information is available about downstream standby servers.
834842
</para>
835843

844+
<table id="pg-stat-wal-receiver-view" xreflabel="pg_stat_wal_receiver">
845+
<title><structname>pg_stat_wal_receiver</structname> View</title>
846+
<tgroup cols="3">
847+
<thead>
848+
<row>
849+
<entry>Column</entry>
850+
<entry>Type</entry>
851+
<entry>Description</entry>
852+
</row>
853+
</thead>
854+
855+
<tbody>
856+
<row>
857+
<entry><structfield>pid</></entry>
858+
<entry><type>integer</></entry>
859+
<entry>Process ID of the WAL receiver process</entry>
860+
</row>
861+
<row>
862+
<entry><structfield>status</></entry>
863+
<entry><type>text</></entry>
864+
<entry>Activity status of the WAL receiver process</entry>
865+
</row>
866+
<row>
867+
<entry><structfield>receive_start_lsn</></entry>
868+
<entry><type>pg_lsn</></entry>
869+
<entry>First transaction log position used when WAL receiver is
870+
started</entry>
871+
</row>
872+
<row>
873+
<entry><structfield>receive_start_tli</></entry>
874+
<entry><type>integer</></entry>
875+
<entry>First timeline number used when WAL receiver is started</entry>
876+
</row>
877+
<row>
878+
<entry><structfield>received_lsn</></entry>
879+
<entry><type>pg_lsn</></entry>
880+
<entry>Last transaction log position already received and flushed to
881+
disk, the initial value of this field being the first log position used
882+
when WAL receiver is started</entry>
883+
</row>
884+
<row>
885+
<entry><structfield>received_tli</></entry>
886+
<entry><type>integer</></entry>
887+
<entry>Timeline number of last transaction log position received and
888+
flushed to disk, the initial value of this field being the timeline
889+
number of the first log position used when WAL receiver is started
890+
</entry>
891+
</row>
892+
<row>
893+
<entry><structfield>last_msg_send_time</></entry>
894+
<entry><type>timestamp with time zone</></entry>
895+
<entry>Send time of last message received from origin WAL sender</entry>
896+
</row>
897+
<row>
898+
<entry><structfield>last_msg_receipt_time</></entry>
899+
<entry><type>timestamp with time zone</></entry>
900+
<entry>Receipt time of last message received from origin WAL sender</entry>
901+
</row>
902+
<row>
903+
<entry><structfield>latest_end_lsn</></entry>
904+
<entry><type>pg_lsn</></entry>
905+
<entry>Last transaction log position reported to origin WAL sender</entry>
906+
</row>
907+
<row>
908+
<entry><structfield>latest_end_time</></entry>
909+
<entry><type>timestamp with time zone</></entry>
910+
<entry>Time of last transaction log position reported to origin WAL sender</entry>
911+
</row>
912+
<row>
913+
<entry><structfield>slot_name</></entry>
914+
<entry><type>text</></entry>
915+
<entry>Replication slot name used by this WAL receiver</entry>
916+
</row>
917+
</tbody>
918+
</tgroup>
919+
</table>
920+
921+
<para>
922+
The <structname>pg_stat_wal_receiver</structname> view will contain only
923+
one row, showing statistics about the WAL receiver from that receiver's
924+
connected server.
925+
</para>
926+
836927
<table id="pg-stat-ssl-view" xreflabel="pg_stat_ssl">
837928
<title><structname>pg_stat_ssl</structname> View</title>
838929
<tgroup cols="3">

‎src/backend/catalog/system_views.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -662,6 +662,22 @@ CREATE VIEW pg_stat_replication AS
662662
WHERE S.usesysid = U.oid AND
663663
S.pid = W.pid;
664664

665+
CREATE VIEW pg_stat_wal_receiver AS
666+
SELECT
667+
s.pid,
668+
s.status,
669+
s.receive_start_lsn,
670+
s.receive_start_tli,
671+
s.received_lsn,
672+
s.received_tli,
673+
s.last_msg_send_time,
674+
s.last_msg_receipt_time,
675+
s.latest_end_lsn,
676+
s.latest_end_time,
677+
s.slot_name
678+
FROM pg_stat_get_wal_receiver() s
679+
WHERE s.pid IS NOT NULL;
680+
665681
CREATE VIEW pg_stat_ssl AS
666682
SELECT
667683
S.pid,

‎src/backend/replication/walreceiver.c

Lines changed: 154 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,9 +46,12 @@
4646
#include <signal.h>
4747
#include <unistd.h>
4848

49+
#include "access/htup_details.h"
4950
#include "access/timeline.h"
5051
#include "access/transam.h"
5152
#include "access/xlog_internal.h"
53+
#include "catalog/pg_type.h"
54+
#include "funcapi.h"
5255
#include "libpq/pqformat.h"
5356
#include "libpq/pqsignal.h"
5457
#include "miscadmin.h"
@@ -57,7 +60,9 @@
5760
#include "storage/ipc.h"
5861
#include "storage/pmsignal.h"
5962
#include "storage/procarray.h"
63+
#include "utils/builtins.h"
6064
#include "utils/guc.h"
65+
#include "utils/pg_lsn.h"
6166
#include "utils/ps_status.h"
6267
#include "utils/resowner.h"
6368
#include "utils/timestamp.h"
@@ -1215,3 +1220,152 @@ ProcessWalSndrMessage(XLogRecPtr walEnd, TimestampTz sendTime)
12151220
pfree(receipttime);
12161221
}
12171222
}
1223+
1224+
/*
1225+
* Return a string constant representing the state. This is used
1226+
* in system functions and views, and should *not* be translated.
1227+
*/
1228+
static const char *
1229+
WalRcvGetStateString(WalRcvState state)
1230+
{
1231+
switch (state)
1232+
{
1233+
case WALRCV_STOPPED:
1234+
return "stopped";
1235+
case WALRCV_STARTING:
1236+
return "starting";
1237+
case WALRCV_STREAMING:
1238+
return "streaming";
1239+
case WALRCV_WAITING:
1240+
return "waiting";
1241+
case WALRCV_RESTARTING:
1242+
return "restarting";
1243+
case WALRCV_STOPPING:
1244+
return "stopping";
1245+
}
1246+
return "UNKNOWN";
1247+
}
1248+
1249+
/*
1250+
* Returns activity of WAL receiver, including pid, state and xlog locations
1251+
* received from the WAL sender of another server.
1252+
*/
1253+
Datum
1254+
pg_stat_get_wal_receiver(PG_FUNCTION_ARGS)
1255+
{
1256+
#define PG_STAT_GET_WAL_RECEIVER_COLS 11
1257+
TupleDesc tupdesc;
1258+
Datum values[PG_STAT_GET_WAL_RECEIVER_COLS];
1259+
bool nulls[PG_STAT_GET_WAL_RECEIVER_COLS];
1260+
WalRcvData *walrcv = WalRcv;
1261+
WalRcvState state;
1262+
XLogRecPtr receive_start_lsn;
1263+
TimeLineID receive_start_tli;
1264+
XLogRecPtr received_lsn;
1265+
TimeLineID received_tli;
1266+
TimestampTz last_send_time;
1267+
TimestampTz last_receipt_time;
1268+
XLogRecPtr latest_end_lsn;
1269+
TimestampTz latest_end_time;
1270+
char *slotname;
1271+
1272+
/* No WAL receiver, just return a tuple with NULL values */
1273+
if (walrcv->pid == 0)
1274+
PG_RETURN_NULL();
1275+
1276+
/* Initialise values and NULL flags arrays */
1277+
MemSet(values, 0, sizeof(values));
1278+
MemSet(nulls, 0, sizeof(nulls));
1279+
1280+
/* Initialise attributes information in the tuple descriptor */
1281+
tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_WAL_RECEIVER_COLS, false);
1282+
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "pid",
1283+
INT4OID, -1, 0);
1284+
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "status",
1285+
TEXTOID, -1, 0);
1286+
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "receive_start_lsn",
1287+
LSNOID, -1, 0);
1288+
TupleDescInitEntry(tupdesc, (AttrNumber) 4, "receive_start_tli",
1289+
INT4OID, -1, 0);
1290+
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "received_lsn",
1291+
LSNOID, -1, 0);
1292+
TupleDescInitEntry(tupdesc, (AttrNumber) 6, "received_tli",
1293+
INT4OID, -1, 0);
1294+
TupleDescInitEntry(tupdesc, (AttrNumber) 7, "last_msg_send_time",
1295+
TIMESTAMPTZOID, -1, 0);
1296+
TupleDescInitEntry(tupdesc, (AttrNumber) 8, "last_msg_receipt_time",
1297+
TIMESTAMPTZOID, -1, 0);
1298+
TupleDescInitEntry(tupdesc, (AttrNumber) 9, "latest_end_lsn",
1299+
LSNOID, -1, 0);
1300+
TupleDescInitEntry(tupdesc, (AttrNumber) 10, "latest_end_time",
1301+
TIMESTAMPTZOID, -1, 0);
1302+
TupleDescInitEntry(tupdesc, (AttrNumber) 11, "slot_name",
1303+
TEXTOID, -1, 0);
1304+
1305+
BlessTupleDesc(tupdesc);
1306+
1307+
/* Take a lock to ensure value consistency */
1308+
SpinLockAcquire(&walrcv->mutex);
1309+
state = walrcv->walRcvState;
1310+
receive_start_lsn = walrcv->receiveStart;
1311+
receive_start_tli = walrcv->receiveStartTLI;
1312+
received_lsn = walrcv->receivedUpto;
1313+
received_tli = walrcv->receivedTLI;
1314+
last_send_time = walrcv->lastMsgSendTime;
1315+
last_receipt_time = walrcv->lastMsgReceiptTime;
1316+
latest_end_lsn = walrcv->latestWalEnd;
1317+
latest_end_time = walrcv->latestWalEndTime;
1318+
slotname = pstrdup(walrcv->slotname);
1319+
SpinLockRelease(&walrcv->mutex);
1320+
1321+
/* Fetch values */
1322+
values[0] = Int32GetDatum(walrcv->pid);
1323+
1324+
if (!superuser())
1325+
{
1326+
/*
1327+
* Only superusers can see details. Other users only get the pid
1328+
* value to know whether it is a WAL receiver, but no details.
1329+
*/
1330+
MemSet(&nulls[1], true, PG_STAT_GET_WAL_RECEIVER_COLS - 1);
1331+
}
1332+
else
1333+
{
1334+
values[1] = CStringGetTextDatum(WalRcvGetStateString(state));
1335+
1336+
if (XLogRecPtrIsInvalid(receive_start_lsn))
1337+
nulls[2] = true;
1338+
else
1339+
values[2] = LSNGetDatum(receive_start_lsn);
1340+
values[3] = Int32GetDatum(receive_start_tli);
1341+
if (XLogRecPtrIsInvalid(received_lsn))
1342+
nulls[4] = true;
1343+
else
1344+
values[4] = LSNGetDatum(received_lsn);
1345+
values[5] = Int32GetDatum(received_tli);
1346+
if (last_send_time == 0)
1347+
nulls[6] = true;
1348+
else
1349+
values[6] = TimestampTzGetDatum(last_send_time);
1350+
if (last_receipt_time == 0)
1351+
nulls[7] = true;
1352+
else
1353+
values[7] = TimestampTzGetDatum(last_receipt_time);
1354+
if (XLogRecPtrIsInvalid(latest_end_lsn))
1355+
nulls[8] = true;
1356+
else
1357+
values[8] = LSNGetDatum(latest_end_lsn);
1358+
if (latest_end_time == 0)
1359+
nulls[9] = true;
1360+
else
1361+
values[9] = TimestampTzGetDatum(latest_end_time);
1362+
if (*slotname == '\0')
1363+
nulls[10] = true;
1364+
else
1365+
values[10] = CStringGetTextDatum(slotname);
1366+
}
1367+
1368+
/* Returns the record as Datum */
1369+
PG_RETURN_DATUM(HeapTupleGetDatum(
1370+
heap_form_tuple(tupdesc, values, nulls)));
1371+
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201601052
56+
#define CATALOG_VERSION_NO 201601071
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2787,6 +2787,8 @@ DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f
27872787
DESCR("statistics: information about currently active backends");
27882788
DATA(insert OID = 3099 ( pg_stat_get_wal_senders PGNSP PGUID 12 1 10 0 0 f f f f f t s r 0 0 2249 "" "{23,25,3220,3220,3220,3220,23,25}" "{o,o,o,o,o,o,o,o}" "{pid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}" _null_ _null_ pg_stat_get_wal_senders _null_ _null_ _null_ ));
27892789
DESCR("statistics: information about currently active replication");
2790+
DATA(insert OID = 3317 ( pg_stat_get_wal_receiver PGNSP PGUID 12 1 0 0 0 f f f f f f s r 0 0 2249 "" "{23,25,3220,23,3220,23,1184,1184,3220,1184,25}" "{o,o,o,o,o,o,o,o,o,o,o}" "{pid,status,receive_start_lsn,receive_start_tli,received_lsn,received_tli,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time,slot_name}" _null_ _null_ pg_stat_get_wal_receiver _null_ _null_ _null_ ));
2791+
DESCR("statistics: information about WAL receiver");
27902792
DATA(insert OID = 2026 ( pg_backend_pid PGNSP PGUID 12 1 0 0 0 f f f f t f s r 0 0 23 "" _null_ _null_ _null_ _null_ _null_ pg_backend_pid _null_ _null_ _null_ ));
27912793
DESCR("statistics: current backend PID");
27922794
DATA(insert OID = 1937 ( pg_stat_get_backend_pid PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 23 "23" _null_ _null_ _null_ _null_ _null_ pg_stat_get_backend_pid _null_ _null_ _null_ ));

‎src/include/replication/walreceiver.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@
1414

1515
#include "access/xlog.h"
1616
#include "access/xlogdefs.h"
17+
#include "fmgr.h"
1718
#include "storage/latch.h"
1819
#include "storage/spin.h"
1920
#include "pgtime.h"
@@ -148,6 +149,7 @@ extern PGDLLIMPORT walrcv_disconnect_type walrcv_disconnect;
148149

149150
/* prototypes for functions in walreceiver.c */
150151
extern void WalReceiverMain(void) pg_attribute_noreturn();
152+
extern Datum pg_stat_get_wal_receiver(PG_FUNCTION_ARGS);
151153

152154
/* prototypes for functions in walreceiverfuncs.c */
153155
extern Size WalRcvShmemSize(void);

‎src/test/regress/expected/rules.out

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1848,6 +1848,19 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
18481848
pg_stat_all_tables.autoanalyze_count
18491849
FROM pg_stat_all_tables
18501850
WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
1851+
pg_stat_wal_receiver| SELECT s.pid,
1852+
s.status,
1853+
s.receive_start_lsn,
1854+
s.receive_start_tli,
1855+
s.received_lsn,
1856+
s.received_tli,
1857+
s.last_msg_send_time,
1858+
s.last_msg_receipt_time,
1859+
s.latest_end_lsn,
1860+
s.latest_end_time,
1861+
s.slot_name
1862+
FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, received_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name)
1863+
WHERE (s.pid IS NOT NULL);
18511864
pg_stat_xact_all_tables| SELECT c.oid AS relid,
18521865
n.nspname AS schemaname,
18531866
c.relname,

0 commit comments

Comments
 (0)