Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb1a9bad

Browse files
committed
pgstat: add WAL receiver status view & SRF
This new view provides insight into the state of a running WAL receiverin 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, lastreceived LSN and TLI, timestamp of last message send and receipt, latestend-of-WAL LSN and time, and the name of the slot (if any).Access to the detailed data is only granted to superusers; others onlyget the PID.Author: Michael PaquierReviewer: Haribabu Kommi
1 parent6b1a837 commitb1a9bad

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
WHERES.usesysid=U.oidAND
663663
S.pid=W.pid;
664664

665+
CREATEVIEWpg_stat_wal_receiverAS
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+
WHEREs.pidIS NOT NULL;
680+
665681
CREATEVIEWpg_stat_sslAS
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+
staticconstchar*
1229+
WalRcvGetStateString(WalRcvStatestate)
1230+
{
1231+
switch (state)
1232+
{
1233+
caseWALRCV_STOPPED:
1234+
return"stopped";
1235+
caseWALRCV_STARTING:
1236+
return"starting";
1237+
caseWALRCV_STREAMING:
1238+
return"streaming";
1239+
caseWALRCV_WAITING:
1240+
return"waiting";
1241+
caseWALRCV_RESTARTING:
1242+
return"restarting";
1243+
caseWALRCV_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+
#definePG_STAT_GET_WAL_RECEIVER_COLS11
1257+
TupleDesctupdesc;
1258+
Datumvalues[PG_STAT_GET_WAL_RECEIVER_COLS];
1259+
boolnulls[PG_STAT_GET_WAL_RECEIVER_COLS];
1260+
WalRcvData*walrcv=WalRcv;
1261+
WalRcvStatestate;
1262+
XLogRecPtrreceive_start_lsn;
1263+
TimeLineIDreceive_start_tli;
1264+
XLogRecPtrreceived_lsn;
1265+
TimeLineIDreceived_tli;
1266+
TimestampTzlast_send_time;
1267+
TimestampTzlast_receipt_time;
1268+
XLogRecPtrlatest_end_lsn;
1269+
TimestampTzlatest_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-
#defineCATALOG_VERSION_NO201601052
56+
#defineCATALOG_VERSION_NO201601071
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_activityPGNSP 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_sendersPGNSP 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_receiverPGNSP 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_pidPGNSP 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_pidPGNSP 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
externvoidWalReceiverMain(void)pg_attribute_noreturn();
152+
externDatumpg_stat_get_wal_receiver(PG_FUNCTION_ARGS);
151153

152154
/* prototypes for functions in walreceiverfuncs.c */
153155
externSizeWalRcvShmemSize(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)
[8]ページ先頭
©2009-2025 Movatter.jp