Function and view to retrieve WAL receiver status
Hi all,
Currently there is no equivalent of pg_stat_get_wal_senders for the
WAL receiver on a node, and it seems that it would be useful to have
an SQL representation of what is in shared memory should a WAL
receiver be active without going through the ps display for example.
So, any opinion about having in core a function called
pg_stat_get_wal_receiver that returns a single tuple that translates
the data WalRcvData?
We could bundle on top of it a system view, say called
pg_stat_wal_receiver, with this layer:
View "public.pg_stat_wal_receiver"
Column|Type|Modifiers
pid|integer|
status|text|
receive_start_lsn|pg_lsn|
receive_start_tli|integer|
received_up_to_lsn|pg_lsn|
received_tli|integer|
latest_chunk_start_lsn|pg_lsn|
last_msg_send_time|timestamp with time zone|
last_msg_receipt_time|timestamp with time zone|
latest_end_lsn|pg_lsn|
latest_end_time|timestamp with time zone|
slot_name|text|
If the node has no WAL receiver active, a tuple with NULL values is
returned instead.
Thoughts?
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Dec 13, 2015 9:56 PM, "Michael Paquier" <michael.paquier@gmail.com>
wrote:
If the node has no WAL receiver active, a tuple with NULL values is
returned instead.
IMO, in the absence of a WAL receiver the SRF (and the view) should not
return any rows.
On Mon, Dec 14, 2015 at 3:09 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
On Dec 13, 2015 9:56 PM, "Michael Paquier" <michael.paquier@gmail.com>
wrote:If the node has no WAL receiver active, a tuple with NULL values is
returned instead.IMO, in the absence of a WAL receiver the SRF (and the view) should not
return any rows.
The whole point is to not use a SRF in this case: there is always at
most one WAL receiver.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Dec 13, 2015 at 10:15 PM, Michael Paquier <michael.paquier@gmail.com
wrote:
On Mon, Dec 14, 2015 at 3:09 PM, Gurjeet Singh <singh.gurjeet@gmail.com>
wrote:On Dec 13, 2015 9:56 PM, "Michael Paquier" <michael.paquier@gmail.com>
wrote:If the node has no WAL receiver active, a tuple with NULL values is
returned instead.IMO, in the absence of a WAL receiver the SRF (and the view) should not
return any rows.The whole point is to not use a SRF in this case: there is always at
most one WAL receiver.
The function, maybe. But emitting an all-nulls row from a view seems
counter-intuitive, at least when looking at it in context of relational
database.
--
Gurjeet Singh http://gurjeet.singh.im/
On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
The function, maybe. But emitting an all-nulls row from a view seems
counter-intuitive, at least when looking at it in context of relational
database.
OK, noted. Any other opinions?
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Dec 14, 2015 at 7:23 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
The function, maybe. But emitting an all-nulls row from a view seems
counter-intuitive, at least when looking at it in context of relational
database.OK, noted. Any other opinions?
I wouldn't bother with the view. If we're going to do it, I'd say
just provide the function and let people SELECT * from it if they want
to.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Dec 18, 2015 at 8:39 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Dec 14, 2015 at 7:23 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
The function, maybe. But emitting an all-nulls row from a view seems
counter-intuitive, at least when looking at it in context of relational
database.OK, noted. Any other opinions?
I wouldn't bother with the view. If we're going to do it, I'd say
just provide the function and let people SELECT * from it if they want
to.
OK, I took some time to write a patch for that as attached, added in
the next CF here:
https://commitfest.postgresql.org/8/447/
I am fine switching to an SRF depending on other opinions of people
here, it just seems like an overkill knowing the uniqueness of the WAL
sender in a server.
I have finished with a function and a system view, this came up more
in line with the existing things like pg_stat_archiver, and this makes
as well the documentation clearer, at least that was my feeling when
hacking that.
Regards,
--
Michael
Attachments:
0001-Add-system-view-and-function-to-report-WAL-receiver-.patchbinary/octet-stream; name=0001-Add-system-view-and-function-to-report-WAL-receiver-.patchDownload
From d09ad51cbd8e921fb9233b072e64389114734a5b Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@otacoo.com>
Date: Fri, 18 Dec 2015 22:44:21 +0900
Subject: [PATCH] Add system view and function to report WAL receiver activity
---
doc/src/sgml/monitoring.sgml | 86 +++++++++++++++++++
src/backend/catalog/system_views.sql | 15 ++++
src/backend/replication/walreceiver.c | 154 ++++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.h | 2 +
src/include/replication/walreceiver.h | 2 +
src/test/regress/expected/rules.out | 12 +++
6 files changed, 271 insertions(+)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index c503636..4b00e55 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -301,6 +301,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</row>
<row>
+ <entry><structname>pg_stat_wal_receiver</><indexterm><primary>pg_stat_wal_receiver</primary></indexterm></entry>
+ <entry>Only one row, showing statistics about the WAL receiver from
+ that receiver's connected server.
+ See <xref linkend="pg-stat-wal-receiver-view"> for details.
+ </entry>
+ </row>
+
+ <row>
<entry><structname>pg_stat_ssl</><indexterm><primary>pg_stat_ssl</primary></indexterm></entry>
<entry>One row per connection (regular and replication), showing information about
SSL used on this connection.
@@ -833,6 +841,84 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
listed; no information is available about downstream standby servers.
</para>
+ <table id="pg-stat-wal-receiver-view" xreflabel="pg_stat_wal_receiver">
+ <title><structname>pg_stat_wal_receiver</structname> View</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Column</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>pid</></entry>
+ <entry><type>integer</></entry>
+ <entry>Process ID of the WAL receiver process</entry>
+ </row>
+ <row>
+ <entry><structfield>status</></entry>
+ <entry><type>text</></entry>
+ <entry>Activity status of the WAL receiver process</entry>
+ </row>
+ <row>
+ <entry><structfield>receive_start_lsn</></entry>
+ <entry><type>pg_lsn</></entry>
+ <entry>First transaction log position used when WAL receiver is started</entry>
+ </row>
+ <row>
+ <entry><structfield>receive_start_tli</></entry>
+ <entry><type>integer</></entry>
+ <entry>First timeline number used when WAL receiver is started</entry>
+ </row>
+ <row>
+ <entry><structfield>received_up_to_lsn</></entry>
+ <entry><type>pg_lsn</></entry>
+ <entry>Last transaction log position received</entry>
+ </row>
+ <row>
+ <entry><structfield>received_tli</></entry>
+ <entry><type>integer</></entry>
+ <entry>Timeline number of last transaction log position received</entry>
+ </row>
+ <row>
+ <entry><structfield>last_msg_send_time</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Send time of last message received from origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>last_msg_receipt_time</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Receipt time of last message received from origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>latest_end_lsn</></entry>
+ <entry><type>pg_lsn</></entry>
+ <entry>Last transaction log position reported to origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>latest_end_time</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Time of last transaction log position reported to origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>slot_name</></entry>
+ <entry><type>text</></entry>
+ <entry>Replication slot name used by this WAL receiver</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_stat_wal_receiver</structname> view will contain only
+ one row, showing statistics about the WAL receiver from that receiver's
+ connected server. If no WAL receiver is present on the server queried,
+ a single tuple filled with <literal>NULL</> values is returned instead.
+ </para>
+
<table id="pg-stat-ssl-view" xreflabel="pg_stat_ssl">
<title><structname>pg_stat_ssl</structname> View</title>
<tgroup cols="3">
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 536c805..3bd7dc4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -662,6 +662,21 @@ CREATE VIEW pg_stat_replication AS
WHERE S.usesysid = U.oid AND
S.pid = W.pid;
+CREATE VIEW pg_stat_wal_receiver AS
+ SELECT
+ s.pid,
+ s.status,
+ s.receive_start_lsn,
+ s.receive_start_tli,
+ s.received_up_to_lsn,
+ s.received_tli,
+ s.last_msg_send_time,
+ s.last_msg_receipt_time,
+ s.latest_end_lsn,
+ s.latest_end_time,
+ s.slot_name
+ FROM pg_stat_get_wal_receiver() s;
+
CREATE VIEW pg_stat_ssl AS
SELECT
S.pid,
diff --git a/src/backend/replication/walreceiver.c b/src/backend/replication/walreceiver.c
index 183a3a5..e3dcb2c 100644
--- a/src/backend/replication/walreceiver.c
+++ b/src/backend/replication/walreceiver.c
@@ -46,9 +46,12 @@
#include <signal.h>
#include <unistd.h>
+#include "access/htup_details.h"
#include "access/timeline.h"
#include "access/transam.h"
#include "access/xlog_internal.h"
+#include "catalog/pg_type.h"
+#include "funcapi.h"
#include "libpq/pqformat.h"
#include "libpq/pqsignal.h"
#include "miscadmin.h"
@@ -57,7 +60,9 @@
#include "storage/ipc.h"
#include "storage/pmsignal.h"
#include "storage/procarray.h"
+#include "utils/builtins.h"
#include "utils/guc.h"
+#include "utils/pg_lsn.h"
#include "utils/ps_status.h"
#include "utils/resowner.h"
#include "utils/timestamp.h"
@@ -1215,3 +1220,152 @@ ProcessWalSndrMessage(XLogRecPtr walEnd, TimestampTz sendTime)
pfree(receipttime);
}
}
+
+/*
+ * 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_up_lsn;
+ TimeLineID received_up_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_up_to_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);
+
+ /* lock? */
+ SpinLockAcquire(&walrcv->mutex);
+ state = walrcv->walRcvState;
+ receive_start_lsn = walrcv->receiveStart;
+ receive_start_tli = walrcv->receiveStartTLI;
+ received_up_lsn = walrcv->receivedUpto;
+ received_up_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_up_lsn))
+ nulls[4] = true;
+ else
+ values[4] = LSNGetDatum(received_up_lsn);
+ values[5] = Int32GetDatum(received_up_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)));
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index d8640db..21ec591 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2785,6 +2785,8 @@ DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f
DESCR("statistics: information about currently active backends");
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_ ));
DESCR("statistics: information about currently active replication");
+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_up_to_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_ ));
+DESCR("statistics: information about WAL receiver");
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_ ));
DESCR("statistics: current backend PID");
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_ ));
diff --git a/src/include/replication/walreceiver.h b/src/include/replication/walreceiver.h
index 61255a9..ac5a188 100644
--- a/src/include/replication/walreceiver.h
+++ b/src/include/replication/walreceiver.h
@@ -14,6 +14,7 @@
#include "access/xlog.h"
#include "access/xlogdefs.h"
+#include "fmgr.h"
#include "storage/latch.h"
#include "storage/spin.h"
#include "pgtime.h"
@@ -148,6 +149,7 @@ extern PGDLLIMPORT walrcv_disconnect_type walrcv_disconnect;
/* prototypes for functions in walreceiver.c */
extern void WalReceiverMain(void) pg_attribute_noreturn();
+extern Datum pg_stat_get_wal_receiver(PG_FUNCTION_ARGS);
/* prototypes for functions in walreceiverfuncs.c */
extern Size WalRcvShmemSize(void);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 80374e4..1410f0f 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1848,6 +1848,18 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
pg_stat_all_tables.autoanalyze_count
FROM pg_stat_all_tables
WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
+pg_stat_wal_receiver| SELECT s.pid,
+ s.status,
+ s.receive_start_lsn,
+ s.receive_start_tli,
+ s.received_up_to_lsn,
+ s.received_tli,
+ s.last_msg_send_time,
+ s.last_msg_receipt_time,
+ s.latest_end_lsn,
+ s.latest_end_time,
+ s.slot_name
+ FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, received_up_to_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name);
pg_stat_xact_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
c.relname,
--
2.6.4
On Sat, Dec 19, 2015 at 12:54 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Fri, Dec 18, 2015 at 8:39 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Dec 14, 2015 at 7:23 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
The function, maybe. But emitting an all-nulls row from a view seems
counter-intuitive, at least when looking at it in context of relational
database.OK, noted. Any other opinions?
I wouldn't bother with the view. If we're going to do it, I'd say
just provide the function and let people SELECT * from it if they want
to.OK, I took some time to write a patch for that as attached, added in
the next CF here:
https://commitfest.postgresql.org/8/447/
I am fine switching to an SRF depending on other opinions of people
here, it just seems like an overkill knowing the uniqueness of the WAL
sender in a server.I have finished with a function and a system view, this came up more
in line with the existing things like pg_stat_archiver, and this makes
as well the documentation clearer, at least that was my feeling when
hacking that.
I also feel showing NULL values may not be good, when there is
no walreceiver. Instead of SRF function to avoid showing NULL vallues
how about adding "WHERE s.pid IS NOT NULL" to the system view.
pid value cannot be NULL, until unless there is no walreceiver.
Regards,
Hari Babu
Fujitsu Australia
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jan 5, 2016 at 7:49 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
On Sat, Dec 19, 2015 at 12:54 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Fri, Dec 18, 2015 at 8:39 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Dec 14, 2015 at 7:23 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
The function, maybe. But emitting an all-nulls row from a view seems
counter-intuitive, at least when looking at it in context of relational
database.OK, noted. Any other opinions?
I wouldn't bother with the view. If we're going to do it, I'd say
just provide the function and let people SELECT * from it if they want
to.OK, I took some time to write a patch for that as attached, added in
the next CF here:
https://commitfest.postgresql.org/8/447/
I am fine switching to an SRF depending on other opinions of people
here, it just seems like an overkill knowing the uniqueness of the WAL
sender in a server.I have finished with a function and a system view, this came up more
in line with the existing things like pg_stat_archiver, and this makes
as well the documentation clearer, at least that was my feeling when
hacking that.I also feel showing NULL values may not be good, when there is
no walreceiver. Instead of SRF function to avoid showing NULL vallues
how about adding "WHERE s.pid IS NOT NULL" to the system view.
pid value cannot be NULL, until unless there is no walreceiver.
Yeah, I would not mind switching it to that. A couple of other stat
catalog views do it as well.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jan 5, 2016 at 10:24 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Tue, Jan 5, 2016 at 7:49 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
On Sat, Dec 19, 2015 at 12:54 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Fri, Dec 18, 2015 at 8:39 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Dec 14, 2015 at 7:23 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
The function, maybe. But emitting an all-nulls row from a view seems
counter-intuitive, at least when looking at it in context of relational
database.OK, noted. Any other opinions?
I wouldn't bother with the view. If we're going to do it, I'd say
just provide the function and let people SELECT * from it if they want
to.OK, I took some time to write a patch for that as attached, added in
the next CF here:
https://commitfest.postgresql.org/8/447/
I am fine switching to an SRF depending on other opinions of people
here, it just seems like an overkill knowing the uniqueness of the WAL
sender in a server.I have finished with a function and a system view, this came up more
in line with the existing things like pg_stat_archiver, and this makes
as well the documentation clearer, at least that was my feeling when
hacking that.I also feel showing NULL values may not be good, when there is
no walreceiver. Instead of SRF function to avoid showing NULL vallues
how about adding "WHERE s.pid IS NOT NULL" to the system view.
pid value cannot be NULL, until unless there is no walreceiver.Yeah, I would not mind switching it to that. A couple of other stat
catalog views do it as well.
Following are my observations on the latest patch.
+ If no WAL receiver is present on the server queried,
+ a single tuple filled with <literal>NULL</> values is returned instead.
+ </para>The above documentation change is not required if we change the system
view.
+ s.received_up_to_lsn,
The column name can be changed as "received_lsn" similar to "received_tli".
up_to may not be required.
+ XLogRecPtr received_up_lsn;
+ TimeLineID received_up_tli;
same as like above comment.
+ /* lock? */
I find out that walrcv data is updated only under mutex. it is better
to take that
mutex to provide a consistent snapshot data to user.
Regards,
Hari Babu
Fujitsu Australia
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jan 6, 2016 at 8:14 AM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
Following are my observations on the latest patch.
Thanks for your review.
+ If no WAL receiver is present on the server queried, + a single tuple filled with <literal>NULL</> values is returned instead. + </para>The above documentation change is not required if we change the system
view.
Affirmative.
+ s.received_up_to_lsn,
The column name can be changed as "received_lsn" similar to "received_tli".
up_to may not be required.+ XLogRecPtr received_up_lsn;
+ TimeLineID received_up_tli;same as like above comment.
Indeed, let's make the variable names more simple and consistent by
removing this _up_ portion everywhere.
+ /* lock? */
I find out that walrcv data is updated only under mutex. it is better
to take that mutex to provide a consistent snapshot data to user.
The lock is taken, the comment is just incorrect:
+ /* lock? */
+ SpinLockAcquire(&walrcv->mutex);
[...]
+ SpinLockRelease(&walrcv->mutex);I also found out that the description of those fields was not clear
enough actually: received_tli and received _lsn are related to what
has been received *and* flushed to disk, with an initial value being
their start equivalent. This deserves a clear description with all
those things addressed.
Attached is an updated patch.
--
Michael
Attachments:
wal_receiver_view_v2.patchapplication/x-patch; name=wal_receiver_view_v2.patchDownload
From d924cb2f4f8594208ea6127b1135a213c48e0b89 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@otacoo.com>
Date: Fri, 18 Dec 2015 22:44:21 +0900
Subject: [PATCH] Add system view and function to report WAL receiver activity
---
doc/src/sgml/monitoring.sgml | 91 ++++++++++++++++++++
src/backend/catalog/system_views.sql | 16 ++++
src/backend/replication/walreceiver.c | 154 ++++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.h | 2 +
src/include/replication/walreceiver.h | 2 +
src/test/regress/expected/rules.out | 12 +++
6 files changed, 277 insertions(+)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index c503636..85459d0 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -301,6 +301,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</row>
<row>
+ <entry><structname>pg_stat_wal_receiver</><indexterm><primary>pg_stat_wal_receiver</primary></indexterm></entry>
+ <entry>Only one row, showing statistics about the WAL receiver from
+ that receiver's connected server.
+ See <xref linkend="pg-stat-wal-receiver-view"> for details.
+ </entry>
+ </row>
+
+ <row>
<entry><structname>pg_stat_ssl</><indexterm><primary>pg_stat_ssl</primary></indexterm></entry>
<entry>One row per connection (regular and replication), showing information about
SSL used on this connection.
@@ -833,6 +841,89 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
listed; no information is available about downstream standby servers.
</para>
+ <table id="pg-stat-wal-receiver-view" xreflabel="pg_stat_wal_receiver">
+ <title><structname>pg_stat_wal_receiver</structname> View</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Column</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>pid</></entry>
+ <entry><type>integer</></entry>
+ <entry>Process ID of the WAL receiver process</entry>
+ </row>
+ <row>
+ <entry><structfield>status</></entry>
+ <entry><type>text</></entry>
+ <entry>Activity status of the WAL receiver process</entry>
+ </row>
+ <row>
+ <entry><structfield>receive_start_lsn</></entry>
+ <entry><type>pg_lsn</></entry>
+ <entry>First transaction log position used when WAL receiver is
+ started</entry>
+ </row>
+ <row>
+ <entry><structfield>receive_start_tli</></entry>
+ <entry><type>integer</></entry>
+ <entry>First timeline number used when WAL receiver is started</entry>
+ </row>
+ <row>
+ <entry><structfield>received_lsn</></entry>
+ <entry><type>pg_lsn</></entry>
+ <entry>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</entry>
+ </row>
+ <row>
+ <entry><structfield>received_tli</></entry>
+ <entry><type>integer</></entry>
+ <entry>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
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>last_msg_send_time</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Send time of last message received from origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>last_msg_receipt_time</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Receipt time of last message received from origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>latest_end_lsn</></entry>
+ <entry><type>pg_lsn</></entry>
+ <entry>Last transaction log position reported to origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>latest_end_time</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Time of last transaction log position reported to origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>slot_name</></entry>
+ <entry><type>text</></entry>
+ <entry>Replication slot name used by this WAL receiver</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_stat_wal_receiver</structname> view will contain only
+ one row, showing statistics about the WAL receiver from that receiver's
+ connected server.
+ </para>
+
<table id="pg-stat-ssl-view" xreflabel="pg_stat_ssl">
<title><structname>pg_stat_ssl</structname> View</title>
<tgroup cols="3">
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 2052afd..506a884 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -662,6 +662,22 @@ CREATE VIEW pg_stat_replication AS
WHERE S.usesysid = U.oid AND
S.pid = W.pid;
+CREATE VIEW pg_stat_wal_receiver AS
+ SELECT
+ s.pid,
+ s.status,
+ s.receive_start_lsn,
+ s.receive_start_tli,
+ s.received_lsn,
+ s.received_tli,
+ s.last_msg_send_time,
+ s.last_msg_receipt_time,
+ s.latest_end_lsn,
+ s.latest_end_time,
+ s.slot_name
+ FROM pg_stat_get_wal_receiver() s
+ WHERE s.pid IS NOT NULL;
+
CREATE VIEW pg_stat_ssl AS
SELECT
S.pid,
diff --git a/src/backend/replication/walreceiver.c b/src/backend/replication/walreceiver.c
index 81f1529..7b36e02 100644
--- a/src/backend/replication/walreceiver.c
+++ b/src/backend/replication/walreceiver.c
@@ -46,9 +46,12 @@
#include <signal.h>
#include <unistd.h>
+#include "access/htup_details.h"
#include "access/timeline.h"
#include "access/transam.h"
#include "access/xlog_internal.h"
+#include "catalog/pg_type.h"
+#include "funcapi.h"
#include "libpq/pqformat.h"
#include "libpq/pqsignal.h"
#include "miscadmin.h"
@@ -57,7 +60,9 @@
#include "storage/ipc.h"
#include "storage/pmsignal.h"
#include "storage/procarray.h"
+#include "utils/builtins.h"
#include "utils/guc.h"
+#include "utils/pg_lsn.h"
#include "utils/ps_status.h"
#include "utils/resowner.h"
#include "utils/timestamp.h"
@@ -1215,3 +1220,152 @@ ProcessWalSndrMessage(XLogRecPtr walEnd, TimestampTz sendTime)
pfree(receipttime);
}
}
+
+/*
+ * 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)));
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 9250545..86b09a1 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2787,6 +2787,8 @@ DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f
DESCR("statistics: information about currently active backends");
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_ ));
DESCR("statistics: information about currently active replication");
+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_ ));
+DESCR("statistics: information about WAL receiver");
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_ ));
DESCR("statistics: current backend PID");
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_ ));
diff --git a/src/include/replication/walreceiver.h b/src/include/replication/walreceiver.h
index db40d9d..6eacb09 100644
--- a/src/include/replication/walreceiver.h
+++ b/src/include/replication/walreceiver.h
@@ -14,6 +14,7 @@
#include "access/xlog.h"
#include "access/xlogdefs.h"
+#include "fmgr.h"
#include "storage/latch.h"
#include "storage/spin.h"
#include "pgtime.h"
@@ -148,6 +149,7 @@ extern PGDLLIMPORT walrcv_disconnect_type walrcv_disconnect;
/* prototypes for functions in walreceiver.c */
extern void WalReceiverMain(void) pg_attribute_noreturn();
+extern Datum pg_stat_get_wal_receiver(PG_FUNCTION_ARGS);
/* prototypes for functions in walreceiverfuncs.c */
extern Size WalRcvShmemSize(void);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 80374e4..1410f0f 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1848,6 +1848,18 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
pg_stat_all_tables.autoanalyze_count
FROM pg_stat_all_tables
WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
+pg_stat_wal_receiver| SELECT s.pid,
+ s.status,
+ s.receive_start_lsn,
+ s.receive_start_tli,
+ s.received_up_to_lsn,
+ s.received_tli,
+ s.last_msg_send_time,
+ s.last_msg_receipt_time,
+ s.latest_end_lsn,
+ s.latest_end_time,
+ s.slot_name
+ FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, received_up_to_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name);
pg_stat_xact_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
c.relname,
--
2.6.4
On Wed, Jan 6, 2016 at 3:04 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
Attached is an updated patch.
Forgot to update rules.out...
--
Michael
Attachments:
wal_receiver_view_v3.patchapplication/x-patch; name=wal_receiver_view_v3.patchDownload
From 4bc33d1497c302b8669b1f1d9d43f2f806029693 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@otacoo.com>
Date: Fri, 18 Dec 2015 22:44:21 +0900
Subject: [PATCH] Add system view and function to report WAL receiver activity
---
doc/src/sgml/monitoring.sgml | 91 ++++++++++++++++++++
src/backend/catalog/system_views.sql | 16 ++++
src/backend/replication/walreceiver.c | 154 ++++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.h | 2 +
src/include/replication/walreceiver.h | 2 +
src/test/regress/expected/rules.out | 13 +++
6 files changed, 278 insertions(+)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index c503636..85459d0 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -301,6 +301,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</row>
<row>
+ <entry><structname>pg_stat_wal_receiver</><indexterm><primary>pg_stat_wal_receiver</primary></indexterm></entry>
+ <entry>Only one row, showing statistics about the WAL receiver from
+ that receiver's connected server.
+ See <xref linkend="pg-stat-wal-receiver-view"> for details.
+ </entry>
+ </row>
+
+ <row>
<entry><structname>pg_stat_ssl</><indexterm><primary>pg_stat_ssl</primary></indexterm></entry>
<entry>One row per connection (regular and replication), showing information about
SSL used on this connection.
@@ -833,6 +841,89 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
listed; no information is available about downstream standby servers.
</para>
+ <table id="pg-stat-wal-receiver-view" xreflabel="pg_stat_wal_receiver">
+ <title><structname>pg_stat_wal_receiver</structname> View</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Column</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>pid</></entry>
+ <entry><type>integer</></entry>
+ <entry>Process ID of the WAL receiver process</entry>
+ </row>
+ <row>
+ <entry><structfield>status</></entry>
+ <entry><type>text</></entry>
+ <entry>Activity status of the WAL receiver process</entry>
+ </row>
+ <row>
+ <entry><structfield>receive_start_lsn</></entry>
+ <entry><type>pg_lsn</></entry>
+ <entry>First transaction log position used when WAL receiver is
+ started</entry>
+ </row>
+ <row>
+ <entry><structfield>receive_start_tli</></entry>
+ <entry><type>integer</></entry>
+ <entry>First timeline number used when WAL receiver is started</entry>
+ </row>
+ <row>
+ <entry><structfield>received_lsn</></entry>
+ <entry><type>pg_lsn</></entry>
+ <entry>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</entry>
+ </row>
+ <row>
+ <entry><structfield>received_tli</></entry>
+ <entry><type>integer</></entry>
+ <entry>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
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>last_msg_send_time</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Send time of last message received from origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>last_msg_receipt_time</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Receipt time of last message received from origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>latest_end_lsn</></entry>
+ <entry><type>pg_lsn</></entry>
+ <entry>Last transaction log position reported to origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>latest_end_time</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Time of last transaction log position reported to origin WAL sender</entry>
+ </row>
+ <row>
+ <entry><structfield>slot_name</></entry>
+ <entry><type>text</></entry>
+ <entry>Replication slot name used by this WAL receiver</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_stat_wal_receiver</structname> view will contain only
+ one row, showing statistics about the WAL receiver from that receiver's
+ connected server.
+ </para>
+
<table id="pg-stat-ssl-view" xreflabel="pg_stat_ssl">
<title><structname>pg_stat_ssl</structname> View</title>
<tgroup cols="3">
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 2052afd..506a884 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -662,6 +662,22 @@ CREATE VIEW pg_stat_replication AS
WHERE S.usesysid = U.oid AND
S.pid = W.pid;
+CREATE VIEW pg_stat_wal_receiver AS
+ SELECT
+ s.pid,
+ s.status,
+ s.receive_start_lsn,
+ s.receive_start_tli,
+ s.received_lsn,
+ s.received_tli,
+ s.last_msg_send_time,
+ s.last_msg_receipt_time,
+ s.latest_end_lsn,
+ s.latest_end_time,
+ s.slot_name
+ FROM pg_stat_get_wal_receiver() s
+ WHERE s.pid IS NOT NULL;
+
CREATE VIEW pg_stat_ssl AS
SELECT
S.pid,
diff --git a/src/backend/replication/walreceiver.c b/src/backend/replication/walreceiver.c
index 81f1529..7b36e02 100644
--- a/src/backend/replication/walreceiver.c
+++ b/src/backend/replication/walreceiver.c
@@ -46,9 +46,12 @@
#include <signal.h>
#include <unistd.h>
+#include "access/htup_details.h"
#include "access/timeline.h"
#include "access/transam.h"
#include "access/xlog_internal.h"
+#include "catalog/pg_type.h"
+#include "funcapi.h"
#include "libpq/pqformat.h"
#include "libpq/pqsignal.h"
#include "miscadmin.h"
@@ -57,7 +60,9 @@
#include "storage/ipc.h"
#include "storage/pmsignal.h"
#include "storage/procarray.h"
+#include "utils/builtins.h"
#include "utils/guc.h"
+#include "utils/pg_lsn.h"
#include "utils/ps_status.h"
#include "utils/resowner.h"
#include "utils/timestamp.h"
@@ -1215,3 +1220,152 @@ ProcessWalSndrMessage(XLogRecPtr walEnd, TimestampTz sendTime)
pfree(receipttime);
}
}
+
+/*
+ * 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)));
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 9250545..86b09a1 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2787,6 +2787,8 @@ DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f
DESCR("statistics: information about currently active backends");
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_ ));
DESCR("statistics: information about currently active replication");
+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_ ));
+DESCR("statistics: information about WAL receiver");
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_ ));
DESCR("statistics: current backend PID");
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_ ));
diff --git a/src/include/replication/walreceiver.h b/src/include/replication/walreceiver.h
index db40d9d..6eacb09 100644
--- a/src/include/replication/walreceiver.h
+++ b/src/include/replication/walreceiver.h
@@ -14,6 +14,7 @@
#include "access/xlog.h"
#include "access/xlogdefs.h"
+#include "fmgr.h"
#include "storage/latch.h"
#include "storage/spin.h"
#include "pgtime.h"
@@ -148,6 +149,7 @@ extern PGDLLIMPORT walrcv_disconnect_type walrcv_disconnect;
/* prototypes for functions in walreceiver.c */
extern void WalReceiverMain(void) pg_attribute_noreturn();
+extern Datum pg_stat_get_wal_receiver(PG_FUNCTION_ARGS);
/* prototypes for functions in walreceiverfuncs.c */
extern Size WalRcvShmemSize(void);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 80374e4..28b061f 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1848,6 +1848,19 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
pg_stat_all_tables.autoanalyze_count
FROM pg_stat_all_tables
WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
+pg_stat_wal_receiver| SELECT s.pid,
+ s.status,
+ s.receive_start_lsn,
+ s.receive_start_tli,
+ s.received_lsn,
+ s.received_tli,
+ s.last_msg_send_time,
+ s.last_msg_receipt_time,
+ s.latest_end_lsn,
+ s.latest_end_time,
+ s.slot_name
+ 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)
+ WHERE (s.pid IS NOT NULL);
pg_stat_xact_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
c.relname,
--
2.6.4
On Wed, Jan 6, 2016 at 8:00 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Wed, Jan 6, 2016 at 3:04 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:Attached is an updated patch.
Forgot to update rules.out...
Thanks for the update. Patch looks good to me.
I marked it as ready for committer.
Regards,
Hari Babu
Fujitsu Australia
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 7, 2016 at 1:57 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
On Wed, Jan 6, 2016 at 8:00 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Wed, Jan 6, 2016 at 3:04 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:Attached is an updated patch.
Forgot to update rules.out...
Thanks for the update. Patch looks good to me.
I marked it as ready for committer.
Thanks!
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Michael Paquier wrote:
On Thu, Jan 7, 2016 at 1:57 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
On Wed, Jan 6, 2016 at 8:00 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Wed, Jan 6, 2016 at 3:04 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:Attached is an updated patch.
Forgot to update rules.out...
Thanks for the update. Patch looks good to me.
I marked it as ready for committer.Thanks!
Messed around with it, couldn't find any fault, pushed.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jan 8, 2016 at 4:38 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Michael Paquier wrote:
On Thu, Jan 7, 2016 at 1:57 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
On Wed, Jan 6, 2016 at 8:00 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Wed, Jan 6, 2016 at 3:04 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:Attached is an updated patch.
Forgot to update rules.out...
Thanks for the update. Patch looks good to me.
I marked it as ready for committer.Thanks!
Messed around with it, couldn't find any fault, pushed.
Thanks!
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers