pg_stat_wal_receiver-to-display-connected-host_v4.patch

From 8afe279c9bcbebaa85ac18298fb9ef852f3decd7 Mon Sep 17 00:00:00 2001
From: Hari Babu <kommi.haribabu@gmail.com>
Date: Thu, 21 Dec 2017 16:59:25 +1100
Subject: [PATCH] pg_stat_wal_receiver to display connected host

With the support of multi host connection string support
in PostgreSQL, it is possible for the user to specify the
multi host connection string in recovery.conf to avoid
breakdown of streaming replication.

The pg_stat_wal_receiver view is enhanced to support display
of remote host information from which the replication is
streaming currently.
---
 doc/src/sgml/monitoring.sgml                       | 24 ++++++++++++++
 src/backend/catalog/system_views.sql               |  3 ++
 .../libpqwalreceiver/libpqwalreceiver.c            |  8 +++--
 src/backend/replication/walreceiver.c              | 38 ++++++++++++++++++++--
 src/include/catalog/pg_proc.h                      |  2 +-
 src/include/replication/walreceiver.h              | 12 +++++--
 src/interfaces/libpq/exports.txt                   |  1 +
 src/interfaces/libpq/fe-connect.c                  | 17 ++++++++++
 src/interfaces/libpq/libpq-fe.h                    |  1 +
 src/test/regress/expected/rules.out                |  5 ++-
 10 files changed, 101 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 8a9793644f..3e37b800f6 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2022,6 +2022,30 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
      <entry><type>text</type></entry>
      <entry>Replication slot name used by this WAL receiver</entry>
     </row>
+    <row>
+     <entry><structfield>remote_hostname</structfield></entry>
+     <entry><type>text</type></entry>
+     <entry>
+      Host name of the <productname>PostgreSQL</productname> instance
+      this WAL receiver is connected to.
+     </entry>
+    </row>
+    <row>
+     <entry><structfield>remote_hostaddr</structfield></entry>
+     <entry><type>text</type></entry>
+     <entry>
+      Host address of the <productname>PostgreSQL</productname> instance
+      this WAL receiver is connected to.
+     </entry>
+    </row>
+    <row>
+     <entry><structfield>remote_port</structfield></entry>
+     <entry><type>integer</type></entry>
+     <entry>
+      port number of the <productname>PostgreSQL</productname> instance
+      this WAL receiver is connected to.
+     </entry>
+    </row>
     <row>
      <entry><structfield>conninfo</structfield></entry>
      <entry><type>text</type></entry>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 5652e9ee6d..57d1f90e21 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -750,6 +750,9 @@ CREATE VIEW pg_stat_wal_receiver AS
             s.latest_end_lsn,
             s.latest_end_time,
             s.slot_name,
+            s.remote_hostname,
+            s.remote_hostaddr,
+            s.remote_port,
             s.conninfo
     FROM pg_stat_get_wal_receiver() s
     WHERE s.pid IS NOT NULL;
diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
index f9aec0531a..d1ff2fd035 100644
--- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
+++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
@@ -52,7 +52,8 @@ static WalReceiverConn *libpqrcv_connect(const char *conninfo,
 				 bool logical, const char *appname,
 				 char **err);
 static void libpqrcv_check_conninfo(const char *conninfo);
-static char *libpqrcv_get_conninfo(WalReceiverConn *conn);
+static char *libpqrcv_get_conninfo(WalReceiverConn *conn, char **host,
+		  char **hostaddr, int *port);
 static char *libpqrcv_identify_system(WalReceiverConn *conn,
 						 TimeLineID *primary_tli,
 						 int *server_version);
@@ -238,7 +239,8 @@ libpqrcv_check_conninfo(const char *conninfo)
  * are obfuscated.
  */
 static char *
-libpqrcv_get_conninfo(WalReceiverConn *conn)
+libpqrcv_get_conninfo(WalReceiverConn *conn, char **host,
+					  char **hostaddr, int *port)
 {
 	PQconninfoOption *conn_opts;
 	PQconninfoOption *conn_opt;
@@ -277,6 +279,8 @@ libpqrcv_get_conninfo(WalReceiverConn *conn)
 
 	PQconninfoFree(conn_opts);
 
+	PQconnectedhostinfo(conn->streamConn, host, hostaddr, port);
+
 	retval = PQExpBufferDataBroken(buf) ? NULL : pstrdup(buf.data);
 	termPQExpBuffer(&buf);
 	return retval;
diff --git a/src/backend/replication/walreceiver.c b/src/backend/replication/walreceiver.c
index a7fc67153a..d03aac6211 100644
--- a/src/backend/replication/walreceiver.c
+++ b/src/backend/replication/walreceiver.c
@@ -52,6 +52,7 @@
 #include "access/xlog_internal.h"
 #include "catalog/pg_authid.h"
 #include "catalog/pg_type.h"
+#include "common/ip.h"
 #include "funcapi.h"
 #include "libpq/pqformat.h"
 #include "libpq/pqsignal.h"
@@ -199,6 +200,9 @@ WalReceiverMain(void)
 	TimestampTz now;
 	bool		ping_sent;
 	char	   *err;
+	char	   *host = NULL;
+	char	   *hostaddr = NULL;
+	int			port = 0;
 
 	/*
 	 * WalRcv should be set up already (if we are a backend, we inherit this
@@ -310,11 +314,21 @@ WalReceiverMain(void)
 	 * Save user-visible connection string.  This clobbers the original
 	 * conninfo, for security.
 	 */
-	tmp_conninfo = walrcv_get_conninfo(wrconn);
+	tmp_conninfo = walrcv_get_conninfo(wrconn, &host, &hostaddr, &port);
 	SpinLockAcquire(&walrcv->mutex);
 	memset(walrcv->conninfo, 0, MAXCONNINFO);
 	if (tmp_conninfo)
 		strlcpy((char *) walrcv->conninfo, tmp_conninfo, MAXCONNINFO);
+
+	memset(walrcv->host, 0, NI_MAXHOST);
+	if (host)
+		strlcpy((char *) walrcv->host, host, NI_MAXHOST);
+
+	memset(walrcv->hostaddr, 0, NI_MAXHOST);
+	if (hostaddr)
+		strlcpy((char *) walrcv->hostaddr, hostaddr, NI_MAXHOST);
+
+	walrcv->port = port;
 	walrcv->ready_to_display = true;
 	SpinLockRelease(&walrcv->mutex);
 
@@ -1402,6 +1416,9 @@ pg_stat_get_wal_receiver(PG_FUNCTION_ARGS)
 	TimestampTz last_receipt_time;
 	XLogRecPtr	latest_end_lsn;
 	TimestampTz latest_end_time;
+	char		host[NI_MAXHOST];
+	char		hostaddr[NI_MAXHOST];
+	int			port = 0;
 	char		slotname[NAMEDATALEN];
 	char		conninfo[MAXCONNINFO];
 
@@ -1419,6 +1436,9 @@ pg_stat_get_wal_receiver(PG_FUNCTION_ARGS)
 	latest_end_lsn = WalRcv->latestWalEnd;
 	latest_end_time = WalRcv->latestWalEndTime;
 	strlcpy(slotname, (char *) WalRcv->slotname, sizeof(slotname));
+	strlcpy(host, (char *) WalRcv->host, sizeof(host));
+	strlcpy(hostaddr, (char *) WalRcv->hostaddr, sizeof(hostaddr));
+	port = WalRcv->port;
 	strlcpy(conninfo, (char *) WalRcv->conninfo, sizeof(conninfo));
 	SpinLockRelease(&WalRcv->mutex);
 
@@ -1481,10 +1501,22 @@ pg_stat_get_wal_receiver(PG_FUNCTION_ARGS)
 			nulls[10] = true;
 		else
 			values[10] = CStringGetTextDatum(slotname);
-		if (*conninfo == '\0')
+		if (*host == '\0')
 			nulls[11] = true;
 		else
-			values[11] = CStringGetTextDatum(conninfo);
+			values[11] = CStringGetTextDatum(host);
+		if (*hostaddr == '\0')
+			nulls[12] = true;
+		else
+			values[12] = CStringGetTextDatum(hostaddr);
+		if (port == 0)
+			nulls[13] = true;
+		else
+			values[13] = Int32GetDatum(port);
+		if (*conninfo == '\0')
+			nulls[14] = true;
+		else
+			values[14] = CStringGetTextDatum(conninfo);
 	}
 
 	/* Returns the record as Datum */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 298e0ae2f0..59c45d8adc 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2883,7 +2883,7 @@ DATA(insert OID = 3318 (  pg_stat_get_progress_info			  PGNSP PGUID 12 1 100 0 0
 DESCR("statistics: information about progress of backends running maintenance command");
 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,1186,1186,1186,23,25}" "{o,o,o,o,o,o,o,o,o,o,o}" "{pid,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,write_lag,flush_lag,replay_lag,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,25}" "{o,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,conninfo}" _null_ _null_ pg_stat_get_wal_receiver _null_ _null_ _null_ ));
+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,25,25,23,25}" "{o,o,o,o,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,remote_hostname,remote_hostaddr,remote_port,conninfo}" _null_ _null_ pg_stat_get_wal_receiver _null_ _null_ _null_ ));
 DESCR("statistics: information about WAL receiver");
 DATA(insert OID = 6118 (  pg_stat_get_subscription	PGNSP PGUID 12 1 0 0 0 f f f f f f s r 1 0 2249 "26" "{26,26,26,23,3220,1184,1184,3220,1184}" "{i,o,o,o,o,o,o,o,o}" "{subid,subid,relid,pid,received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time}" _null_ _null_ pg_stat_get_subscription _null_ _null_ _null_ ));
 DESCR("statistics: information about subscription");
diff --git a/src/include/replication/walreceiver.h b/src/include/replication/walreceiver.h
index ea7967f6fc..07b9604760 100644
--- a/src/include/replication/walreceiver.h
+++ b/src/include/replication/walreceiver.h
@@ -107,6 +107,9 @@ typedef struct
 	 * clobbered to hide security-sensitive fields.
 	 */
 	char		conninfo[MAXCONNINFO];
+	char		host[NI_MAXHOST];
+	char		hostaddr[NI_MAXHOST];
+	int			port;
 
 	/*
 	 * replication slot name; is also used for walreceiver to connect with the
@@ -196,7 +199,10 @@ typedef WalReceiverConn *(*walrcv_connect_fn) (const char *conninfo, bool logica
 											   const char *appname,
 											   char **err);
 typedef void (*walrcv_check_conninfo_fn) (const char *conninfo);
-typedef char *(*walrcv_get_conninfo_fn) (WalReceiverConn *conn);
+typedef char *(*walrcv_get_conninfo_fn) (WalReceiverConn *conn,
+										 char **host,
+										 char **hostaddr,
+										 int *port);
 typedef char *(*walrcv_identify_system_fn) (WalReceiverConn *conn,
 											TimeLineID *primary_tli,
 											int *server_version);
@@ -244,8 +250,8 @@ extern PGDLLIMPORT WalReceiverFunctionsType *WalReceiverFunctions;
 	WalReceiverFunctions->walrcv_connect(conninfo, logical, appname, err)
 #define walrcv_check_conninfo(conninfo) \
 	WalReceiverFunctions->walrcv_check_conninfo(conninfo)
-#define walrcv_get_conninfo(conn) \
-	WalReceiverFunctions->walrcv_get_conninfo(conn)
+#define walrcv_get_conninfo(conn, host, hostaddr, port) \
+	WalReceiverFunctions->walrcv_get_conninfo(conn, host, hostaddr, port)
 #define walrcv_identify_system(conn, primary_tli, server_version) \
 	WalReceiverFunctions->walrcv_identify_system(conn, primary_tli, server_version)
 #define walrcv_readtimelinehistoryfile(conn, tli, filename, content, size) \
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
index d6a38d0df8..97ad8c9594 100644
--- a/src/interfaces/libpq/exports.txt
+++ b/src/interfaces/libpq/exports.txt
@@ -172,3 +172,4 @@ PQsslAttribute            169
 PQsetErrorContextVisibility 170
 PQresultVerboseErrorMessage 171
 PQencryptPasswordConn     172
+PQconnectedhostinfo			173
\ No newline at end of file
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 8d543334ae..5152dbbd16 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -6032,6 +6032,23 @@ PQhost(const PGconn *conn)
 	}
 }
 
+/* Provides connected host info details */
+void
+PQconnectedhostinfo(const PGconn *conn, char **host, char **hostaddr, int *port)
+{
+	if (!conn || !conn->connhost)
+		return;
+
+	if (conn->connhost[conn->whichhost].host)
+		*host = conn->connhost[conn->whichhost].host;
+
+	if (conn->connhost[conn->whichhost].hostaddr)
+		*hostaddr = conn->connhost[conn->whichhost].hostaddr;
+
+	if (conn->connhost[conn->whichhost].port)
+		*port = atoi(conn->connhost[conn->whichhost].port);
+}
+
 char *
 PQport(const PGconn *conn)
 {
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
index ed9c806861..c196b04baa 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -312,6 +312,7 @@ extern char *PQdb(const PGconn *conn);
 extern char *PQuser(const PGconn *conn);
 extern char *PQpass(const PGconn *conn);
 extern char *PQhost(const PGconn *conn);
+extern void PQconnectedhostinfo(const PGconn *conn, char **host, char **hostaddr, int *port);
 extern char *PQport(const PGconn *conn);
 extern char *PQtty(const PGconn *conn);
 extern char *PQoptions(const PGconn *conn);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f1c1b44d6f..f216bd33de 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1970,8 +1970,11 @@ pg_stat_wal_receiver| SELECT s.pid,
     s.latest_end_lsn,
     s.latest_end_time,
     s.slot_name,
+    s.remote_hostname,
+    s.remote_hostaddr,
+    s.remote_port,
     s.conninfo
-   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, conninfo)
+   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, remote_hostname, remote_hostaddr, remote_port, conninfo)
   WHERE (s.pid IS NOT NULL);
 pg_stat_xact_all_tables| SELECT c.oid AS relid,
     n.nspname AS schemaname,
-- 
2.15.0.windows.1