[PATCH] Support for pg_stat_archiver view

Started by Gabriele Bartoliniabout 12 years ago29 messages
#1Gabriele Bartolini
gabriele.bartolini@2ndQuadrant.it
1 attachment(s)

Hello,

please find attached the patch that adds basic support for the
pg_stat_archiver system view, which allows users that have continuous
archiving procedures in place to keep track of some important metrics
and information.

Currently, pg_stat_archiver displays:

* archived_wals: number of successfully archived WAL files since start
(or the last reset)
* last_archived_wal: last successfully archived WAL file
* last_archived_wal_time: timestamp of the latest successful WAL archival
* stats_reset: time of last stats reset

This is an example of output:

postgres=# select * from pg_stat_archiver ;
-[ RECORD 1 ]----------+------------------------------
archived_wals | 1
last_archived_wal | 000000010000000000000001
last_archived_wal_time | 2014-01-04 01:01:08.858648+01
stats_reset | 2014-01-04 00:59:25.895034+01

Similarly to pg_stat_bgwriter, it is possible to reset statistics just
for this context, calling the pg_stat_reset_shared('archiver') function.

The patch is here for discussion and has been prepared against HEAD.
It includes also changes in the documentation and the rules.out test.

I plan to add further information to the pg_stat_archiver view,
including the number of failed attempts of archival and the WAL and
timestamp of the latest failure. However, before proceeding, I'd like to
get some feedback on this small patch as well as advice on possible
regression tests to be added.

Thank you.

Cheers,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

Attachments:

pg_stat_archiver.patch.v1text/plain; charset=UTF-8; name=pg_stat_archiver.patch.v1; x-mac-creator=0; x-mac-type=0Download
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4ec6981..6d45972 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -270,6 +270,14 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
      </row>
 
      <row>
+      <entry><structname>pg_stat_archiver</><indexterm><primary>pg_stat_archiver</primary></indexterm></entry>
+      <entry>One row only, showing statistics about the
+       WAL archiver process's activity. See
+       <xref linkend="pg-stat-archiver-view"> for details.
+     </entry>
+     </row>
+
+     <row>
       <entry><structname>pg_stat_bgwriter</><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
       <entry>One row only, showing statistics about the
        background writer process's activity. See
@@ -648,6 +656,49 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
    </para>
   </note>
 
+  <table id="pg-stat-archiver-view" xreflabel="pg_stat_archiver">
+   <title><structname>pg_stat_archiver</structname> View</title>
+
+   <tgroup cols="3">
+    <thead>
+    <row>
+      <entry>Column</entry>
+      <entry>Type</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><structfield>archived_wals</></entry>
+      <entry><type>bigint</type></entry>
+      <entry>Number of WAL files that have been successfully archived</entry>
+     </row>
+     <row>
+      <entry><structfield>last_archived_wal</></entry>
+      <entry><type>text</type></entry>
+      <entry>Name of the last successfully archived WAL file</entry>
+     </row>
+     <row>
+      <entry><structfield>last_archived_wal_time</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time of the last successful archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>stats_reset</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time at which these statistics were last reset</entry>
+     </row>
+    </tbody>
+    </tgroup>
+  </table>
+
+  <para>
+   The <structname>pg_stat_archiver</structname> view will always have a
+   single row, containing data about the archiver process of the cluster.
+  </para>
+
+
   <table id="pg-stat-bgwriter-view" xreflabel="pg_stat_bgwriter">
    <title><structname>pg_stat_bgwriter</structname> View</title>
 
@@ -1613,6 +1664,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
        argument (requires superuser privileges).
        Calling <literal>pg_stat_reset_shared('bgwriter')</> will zero all the
        counters shown in the <structname>pg_stat_bgwriter</> view.
+       Calling <literal>pg_stat_reset_shared('archiver')</> will zero all the
+       counters shown in the <structname>pg_stat_archiver</> view.
       </entry>
      </row>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 575a40f..3a8d7b4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -672,6 +672,13 @@ CREATE VIEW pg_stat_xact_user_functions AS
     WHERE P.prolang != 12  -- fast check to eliminate built-in functions
           AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
 
+CREATE VIEW pg_stat_archiver AS
+    SELECT
+        pg_stat_get_archiver_archived_wals() AS archived_wals,
+        pg_stat_get_archiver_last_archived_wal() AS last_archived_wal,
+        pg_stat_get_archiver_last_archived_wal_time() AS last_archived_wal_time,
+        pg_stat_get_archiver_stat_reset_time() AS stats_reset;
+
 CREATE VIEW pg_stat_bgwriter AS
     SELECT
         pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
diff --git a/src/backend/postmaster/pgarch.c b/src/backend/postmaster/pgarch.c
index 2bb572e..3cb1ddc 100644
--- a/src/backend/postmaster/pgarch.c
+++ b/src/backend/postmaster/pgarch.c
@@ -36,6 +36,7 @@
 #include "access/xlog_internal.h"
 #include "libpq/pqsignal.h"
 #include "miscadmin.h"
+#include "pgstat.h"
 #include "postmaster/fork_process.h"
 #include "postmaster/pgarch.h"
 #include "postmaster/postmaster.h"
@@ -46,6 +47,7 @@
 #include "storage/pmsignal.h"
 #include "utils/guc.h"
 #include "utils/ps_status.h"
+#include "utils/timestamp.h"
 
 
 /* ----------
@@ -755,4 +757,12 @@ pgarch_archiveDone(char *xlog)
 				(errcode_for_file_access(),
 				 errmsg("could not rename file \"%s\" to \"%s\": %m",
 						rlogready, rlogdone)));
+	/*
+	 * Send off activity statistics to the stats collector.
+	 */
+	ArchiverStats.m_archived_wals++;
+	strncpy(ArchiverStats.m_last_archived_wal,
+		xlog, sizeof(ArchiverStats.m_last_archived_wal));
+	ArchiverStats.m_last_archived_wal_timestamp = GetCurrentTimestamp();
+	pgstat_send_archiver();
 }
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 4bfa62e..ffe5d0e 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -123,6 +123,13 @@ char	   *pgstat_stat_filename = NULL;
 char	   *pgstat_stat_tmpname = NULL;
 
 /*
+ * Archiver global statistics counters (unused in other processes).
+ * Stored directly in a stats message structure so it can be sent
+ * without needing to copy things around.  We assume this inits to zeroes.
+ */
+PgStat_MsgArchiver ArchiverStats;
+
+/*
  * BgWriter global statistics counters (unused in other processes).
  * Stored directly in a stats message structure so it can be sent
  * without needing to copy things around.  We assume this inits to zeroes.
@@ -217,6 +224,12 @@ static PgBackendStatus *localBackendStatusTable = NULL;
 static int	localNumBackends = 0;
 
 /*
+ * Archiver statistics, kept in the stats collector.
+ * Contains statistics regarding the archiving process.
+ */
+static PgStat_ArchiverStats archiverStats;
+
+/*
  * Cluster wide statistics, kept in the stats collector.
  * Contains statistics that are not collected per database
  * or per table.
@@ -292,6 +305,7 @@ static void pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, in
 static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
 static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
 static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+static void pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len);
 static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
 static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
 static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
@@ -1257,7 +1271,9 @@ pgstat_reset_shared_counters(const char *target)
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 				 errmsg("must be superuser to reset statistics counters")));
 
-	if (strcmp(target, "bgwriter") == 0)
+	if (strcmp(target, "archiver") == 0)
+		msg.m_resettarget = RESET_ARCHIVER;
+	else if (strcmp(target, "bgwriter") == 0)
 		msg.m_resettarget = RESET_BGWRITER;
 	else
 		ereport(ERROR,
@@ -2321,6 +2337,23 @@ pgstat_fetch_stat_numbackends(void)
 
 /*
  * ---------
+ * pgstat_fetch_archiver() -
+ *
+ *	Support function for the SQL-callable pgstat* functions. Returns
+ *	a pointer to the archiver statistics struct.
+ * ---------
+ */
+PgStat_ArchiverStats *
+pgstat_fetch_archiver(void)
+{
+	backend_read_statsfile();
+
+	return &archiverStats;
+}
+
+
+/*
+ * ---------
  * pgstat_fetch_global() -
  *
  *	Support function for the SQL-callable pgstat* functions. Returns
@@ -3034,6 +3067,39 @@ pgstat_send(void *msg, int len)
 }
 
 /* ----------
+ * pgstat_send_archiver() -
+ *
+ *		Send archiver statistics to the collector
+ * ----------
+ */
+void
+pgstat_send_archiver(void)
+{
+	/* We assume this initializes to zeroes */
+	static const PgStat_MsgArchiver all_zeroes;
+
+	/*
+	 * This function can be called even if nothing at all has happened. In
+	 * this case, avoid sending a completely empty message to the stats
+	 * collector.
+	 */
+	if (memcmp(&ArchiverStats, &all_zeroes, sizeof(PgStat_MsgArchiver)) == 0)
+		return;
+
+	/*
+	 * Prepare and send the message
+	 */
+	pgstat_setheader(&ArchiverStats.m_hdr, PGSTAT_MTYPE_ARCHIVER);
+	pgstat_send(&ArchiverStats, sizeof(ArchiverStats));
+
+	/*
+	 * Clear out the statistics buffer, so it can be re-used.
+	 */
+	MemSet(&ArchiverStats, 0, sizeof(ArchiverStats));
+}
+
+
+/* ----------
  * pgstat_send_bgwriter() -
  *
  *		Send bgwriter statistics to the collector
@@ -3276,6 +3342,10 @@ PgstatCollectorMain(int argc, char *argv[])
 					pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len);
 					break;
 
+				case PGSTAT_MTYPE_ARCHIVER:
+					pgstat_recv_archiver((PgStat_MsgArchiver *) &msg, len);
+					break;
+
 				case PGSTAT_MTYPE_BGWRITER:
 					pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
 					break;
@@ -3546,6 +3616,7 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
 	 * Set the timestamp of the stats file.
 	 */
 	globalStats.stats_timestamp = GetCurrentTimestamp();
+	archiverStats.stats_timestamp = globalStats.stats_timestamp;
 
 	/*
 	 * Write the file header --- currently just a format ID.
@@ -3561,6 +3632,12 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
 	(void) rc;					/* we'll check for error with ferror */
 
 	/*
+	 * Write archiver stats struct
+	 */
+	rc = fwrite(&archiverStats, sizeof(archiverStats), 1, fpout);
+	(void) rc;					/* we'll check for error with ferror */
+
+	/*
 	 * Walk through the database table.
 	 */
 	hash_seq_init(&hstat, pgStatDBHash);
@@ -3826,16 +3903,18 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
 						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
 
 	/*
-	 * Clear out global statistics so they start from zero in case we can't
-	 * load an existing statsfile.
+	 * Clear out global and archiver statistics so they start from zero
+	 * in case we can't load an existing statsfile.
 	 */
 	memset(&globalStats, 0, sizeof(globalStats));
+	memset(&archiverStats, 0, sizeof(archiverStats));
 
 	/*
 	 * Set the current timestamp (will be kept only in case we can't load an
 	 * existing statsfile).
 	 */
 	globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+	archiverStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
 
 	/*
 	 * Try to open the stats file. If it doesn't exist, the backends simply
@@ -3873,7 +3952,17 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
 	if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats))
 	{
 		ereport(pgStatRunningInCollector ? LOG : WARNING,
-				(errmsg("corrupted statistics file \"%s\"", statfile)));
+				(errmsg("corrupted statistics file (global) \"%s\"", statfile)));
+		goto done;
+	}
+
+	/*
+	 * Read archiver stats struct
+	 */
+	if (fread(&archiverStats, 1, sizeof(archiverStats), fpin) != sizeof(archiverStats))
+	{
+		ereport(pgStatRunningInCollector ? LOG : WARNING,
+				(errmsg("corrupted statistics file (archiver) \"%s\"", statfile)));
 		goto done;
 	}
 
@@ -4167,6 +4256,7 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
 {
 	PgStat_StatDBEntry dbentry;
 	PgStat_GlobalStats myGlobalStats;
+	PgStat_ArchiverStats myArchiverStats;
 	FILE	   *fpin;
 	int32		format_id;
 	const char *statfile = permanent ? PGSTAT_STAT_PERMANENT_FILENAME : pgstat_stat_filename;
@@ -4209,6 +4299,18 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
 		return false;
 	}
 
+	/*
+	 * Read archiver stats struct
+	 */
+	if (fread(&myArchiverStats, 1, sizeof(myArchiverStats),
+			  fpin) != sizeof(myArchiverStats))
+	{
+		ereport(pgStatRunningInCollector ? LOG : WARNING,
+				(errmsg("corrupted statistics file \"%s\"", statfile)));
+		FreeFile(fpin);
+		return false;
+	}
+
 	/* By default, we're going to return the timestamp of the global file. */
 	*ts = myGlobalStats.stats_timestamp;
 
@@ -4736,6 +4838,12 @@ pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
 		memset(&globalStats, 0, sizeof(globalStats));
 		globalStats.stat_reset_timestamp = GetCurrentTimestamp();
 	}
+	else if (msg->m_resettarget == RESET_ARCHIVER)
+	{
+		/* Reset the archiver statistics for the cluster. */
+		memset(&archiverStats, 0, sizeof(archiverStats));
+		archiverStats.stat_reset_timestamp = GetCurrentTimestamp();
+	}
 
 	/*
 	 * Presumably the sender of this message validated the target, don't
@@ -4867,6 +4975,21 @@ pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
 
 
 /* ----------
+ * pgstat_recv_archiver() -
+ *
+ *	Process a ARCHIVER message.
+ * ----------
+ */
+static void
+pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len)
+{
+	archiverStats.archived_wals += msg->m_archived_wals;
+	memcpy(archiverStats.last_archived_wal, msg->m_last_archived_wal,
+		sizeof(archiverStats.last_archived_wal));
+	archiverStats.last_archived_wal_timestamp = msg->m_last_archived_wal_timestamp;
+}
+
+/* ----------
  * pgstat_recv_bgwriter() -
  *
  *	Process a BGWRITER message.
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2957fc7..90c7453 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -87,6 +87,11 @@ extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blk_read_time(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS);
 
+extern Datum pg_stat_get_archiver_archived_wals(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_archiver_last_archived_wal(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_archiver_last_archived_wal_time(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_archiver_stat_reset_time(PG_FUNCTION_ARGS);
+
 extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS);
@@ -1408,6 +1413,46 @@ pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS)
 }
 
 Datum
+pg_stat_get_archiver_archived_wals(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT64(pgstat_fetch_archiver()->archived_wals);
+}
+
+Datum
+pg_stat_get_archiver_last_archived_wal(PG_FUNCTION_ARGS)
+{
+	char *result = pgstat_fetch_archiver()->last_archived_wal;
+
+	if (result == 0)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(cstring_to_text(result));
+}
+
+Datum
+pg_stat_get_archiver_last_archived_wal_time(PG_FUNCTION_ARGS)
+{
+	TimestampTz result = pgstat_fetch_archiver()->last_archived_wal_timestamp;
+
+	if (result == 0)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
+Datum
+pg_stat_get_archiver_stat_reset_time(PG_FUNCTION_ARGS)
+{
+	TimestampTz result = pgstat_fetch_archiver()->stat_reset_timestamp;
+
+	if (result == 0)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
+
+Datum
 pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
 {
 	PG_RETURN_INT64(pgstat_fetch_global()->timed_checkpoints);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 05d7ba5..0c1aea3 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2700,6 +2700,14 @@ DATA(insert OID = 2844 (  pg_stat_get_db_blk_read_time	PGNSP PGUID 12 1 0 0 0 f
 DESCR("statistics: block read time, in msec");
 DATA(insert OID = 2845 (  pg_stat_get_db_blk_write_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 701 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blk_write_time _null_ _null_ _null_ ));
 DESCR("statistics: block write time, in msec");
+DATA(insert OID = 3195 ( pg_stat_get_archiver_archived_wals PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_archiver_archived_wals _null_ _null_ _null_ ));
+DESCR("statistics: number of WAL files successfully archived by the archiver");
+DATA(insert OID = 3196 ( pg_stat_get_archiver_last_archived_wal_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 1184 "" _null_ _null_ _null_ _null_ pg_stat_get_archiver_last_archived_wal_time _null_ _null_ _null_ ));
+DESCR("statistics: timestamp of the last successfully archived WAL file");
+DATA(insert OID = 3197 ( pg_stat_get_archiver_last_archived_wal PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 25 "" _null_ _null_ _null_ _null_ pg_stat_get_archiver_last_archived_wal _null_ _null_ _null_ ));
+DESCR("statistics: last succesfully archived WAL file");
+DATA(insert OID = 3198 ( pg_stat_get_archiver_stat_reset_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 1184 "" _null_ _null_ _null_ _null_	pg_stat_get_archiver_stat_reset_time _null_ _null_ _null_ ));
+DESCR("statistics: last reset for the archiver");
 DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
 DESCR("statistics: number of timed checkpoints started by the bgwriter");
 DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index b79a0eb..84a9498 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -18,6 +18,7 @@
 #include "utils/hsearch.h"
 #include "utils/relcache.h"
 
+#define MAX_XFN_CHARS	40
 
 /* Values for track_functions GUC variable --- order is significant! */
 typedef enum TrackFunctionsLevel
@@ -44,6 +45,7 @@ typedef enum StatMsgType
 	PGSTAT_MTYPE_AUTOVAC_START,
 	PGSTAT_MTYPE_VACUUM,
 	PGSTAT_MTYPE_ANALYZE,
+	PGSTAT_MTYPE_ARCHIVER,
 	PGSTAT_MTYPE_BGWRITER,
 	PGSTAT_MTYPE_FUNCSTAT,
 	PGSTAT_MTYPE_FUNCPURGE,
@@ -102,6 +104,7 @@ typedef struct PgStat_TableCounts
 /* Possible targets for resetting cluster-wide shared values */
 typedef enum PgStat_Shared_Reset_Target
 {
+	RESET_ARCHIVER,
 	RESET_BGWRITER
 } PgStat_Shared_Reset_Target;
 
@@ -355,6 +358,19 @@ typedef struct PgStat_MsgAnalyze
 
 
 /* ----------
+ * PgStat_MsgArchiver			Sent by the archiver to update statistics.
+ * ----------
+ */
+typedef struct PgStat_MsgArchiver
+{
+	PgStat_MsgHdr m_hdr;
+
+	PgStat_Counter m_archived_wals;
+	char m_last_archived_wal[MAX_XFN_CHARS +1];
+	TimestampTz m_last_archived_wal_timestamp;
+} PgStat_MsgArchiver;
+
+/* ----------
  * PgStat_MsgBgWriter			Sent by the bgwriter to update statistics.
  * ----------
  */
@@ -501,6 +517,7 @@ typedef union PgStat_Msg
 	PgStat_MsgAutovacStart msg_autovacuum;
 	PgStat_MsgVacuum msg_vacuum;
 	PgStat_MsgAnalyze msg_analyze;
+	PgStat_MsgArchiver msg_archiver;
 	PgStat_MsgBgWriter msg_bgwriter;
 	PgStat_MsgFuncstat msg_funcstat;
 	PgStat_MsgFuncpurge msg_funcpurge;
@@ -611,6 +628,18 @@ typedef struct PgStat_StatFuncEntry
 
 
 /*
+ * Archiver statistics kept in the stats collector
+ */
+typedef struct PgStat_ArchiverStats
+{
+	TimestampTz stats_timestamp;	/* time of stats file update */
+	PgStat_Counter archived_wals;
+	char last_archived_wal[MAX_XFN_CHARS +1]; /* file name of the last archived WAL */
+	TimestampTz last_archived_wal_timestamp;	/* time of last successful archival */
+	TimestampTz stat_reset_timestamp;
+} PgStat_ArchiverStats;
+
+/*
  * Global statistics kept in the stats collector
  */
 typedef struct PgStat_GlobalStats
@@ -731,6 +760,11 @@ extern char *pgstat_stat_tmpname;
 extern char *pgstat_stat_filename;
 
 /*
+ * Archiver statistics counters are updated directly by pgarch
+ */
+extern PgStat_MsgArchiver ArchiverStats;
+
+/*
  * BgWriter statistics counters are updated directly by bgwriter and bufmgr
  */
 extern PgStat_MsgBgWriter BgWriterStats;
@@ -862,6 +896,7 @@ extern void pgstat_twophase_postcommit(TransactionId xid, uint16 info,
 extern void pgstat_twophase_postabort(TransactionId xid, uint16 info,
 						  void *recdata, uint32 len);
 
+extern void pgstat_send_archiver(void);
 extern void pgstat_send_bgwriter(void);
 
 /* ----------
@@ -874,6 +909,7 @@ extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
 extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
 extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
 extern int	pgstat_fetch_stat_numbackends(void);
+extern PgStat_ArchiverStats *pgstat_fetch_archiver(void);
 extern PgStat_GlobalStats *pgstat_fetch_global(void);
 
 #endif   /* PGSTAT_H */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 9f089e3..5916889 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1640,6 +1640,10 @@ pg_stat_all_tables| SELECT c.oid AS relid,
    LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
   GROUP BY c.oid, n.nspname, c.relname;
+pg_stat_archiver| SELECT pg_stat_get_archiver_archived_wals() AS archived_wals,
+    pg_stat_get_archiver_last_archived_wal() AS last_archived_wal,
+    pg_stat_get_archiver_last_archived_wal_time() AS last_archived_wal_time,
+    pg_stat_get_archiver_stat_reset_time() AS stats_reset;
 pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
     pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
     pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
#2Magnus Hagander
magnus@hagander.net
In reply to: Gabriele Bartolini (#1)
Re: [PATCH] Support for pg_stat_archiver view

On Sat, Jan 4, 2014 at 1:33 AM, Gabriele Bartolini <
gabriele.bartolini@2ndquadrant.it> wrote:

Hello,

please find attached the patch that adds basic support for the
pg_stat_archiver system view, which allows users that have continuous
archiving procedures in place to keep track of some important metrics
and information.

Currently, pg_stat_archiver displays:

* archived_wals: number of successfully archived WAL files since start
(or the last reset)
* last_archived_wal: last successfully archived WAL file
* last_archived_wal_time: timestamp of the latest successful WAL archival
* stats_reset: time of last stats reset

This is an example of output:

postgres=# select * from pg_stat_archiver ;
-[ RECORD 1 ]----------+------------------------------
archived_wals | 1
last_archived_wal | 000000010000000000000001
last_archived_wal_time | 2014-01-04 01:01:08.858648+01
stats_reset | 2014-01-04 00:59:25.895034+01

Similarly to pg_stat_bgwriter, it is possible to reset statistics just
for this context, calling the pg_stat_reset_shared('archiver') function.

The patch is here for discussion and has been prepared against HEAD.
It includes also changes in the documentation and the rules.out test.

I plan to add further information to the pg_stat_archiver view,
including the number of failed attempts of archival and the WAL and
timestamp of the latest failure. However, before proceeding, I'd like to
get some feedback on this small patch as well as advice on possible
regression tests to be added.

My first reaction was that exactly those two things were missing. And then
I read your whole email :)

With those two, I think it would make much sense to have a view like this.

I'd suggest making the view on top of an SRF like pg_stat_replication and
pg_stat_activity (for example), instead of a whole lot of separate function
calls like the older stats views.

in pgarch_ArchiveDone() you seem to be increasing the m_archived_vals value
for each call and then sending it off. And then you add that number in the
stats collector. Isn't that going to add the wrong number in the end -
after a while, the archiver is going to send "add 100" when it's just sent
one file? ISTM that pgstat_recv_archiver should just do ++ on the value?

Oh, and you need to change the format id number of the stats file.

There's a quick review you for ;) I think it's definitely worthwhile with
those things fixed (and a proper review, that was just a quick one-over)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

#3Gabriele Bartolini
gabriele.bartolini@2ndQuadrant.it
In reply to: Magnus Hagander (#2)
Re: [PATCH] Support for pg_stat_archiver view

Hi Magnus,

Il 04/01/14 13:25, Magnus Hagander ha scritto:

My first reaction was that exactly those two things were missing. And
then I read your whole email :)

:)

With those two, I think it would make much sense to have a view like
this.

Ok, I will prepare version 2 with those.

I'd suggest making the view on top of an SRF like pg_stat_replication
and pg_stat_activity (for example), instead of a whole lot of separate
function calls like the older stats views.

Ok, good idea.

in pgarch_ArchiveDone() you seem to be increasing the m_archived_vals
value for each call and then sending it off. And then you add that
number in the stats collector. Isn't that going to add the wrong
number in the end - after a while, the archiver is going to send "add
100" when it's just sent one file? ISTM that pgstat_recv_archiver
should just do ++ on the value?

You are right. The purpose was to set it to 1 in ArchiveDone (I might
have missed that change), so that I can manage the failed counters in
the same way. I will fix this in version 2.

Oh, and you need to change the format id number of the stats file.

I have not found any instruction on how to set it. I assume you are
talking about this:

PGSTAT_FILE_FORMAT_ID 0x01A5BC9B

Any suggestion is welcome.

There's a quick review you for ;) I think it's definitely worthwhile
with those things fixed (and a proper review, that was just a quick
one-over)

Thanks for that. It already means a lot if you agree too it is worth it.

Ciao,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#4Magnus Hagander
magnus@hagander.net
In reply to: Gabriele Bartolini (#3)
Re: [PATCH] Support for pg_stat_archiver view

On Sat, Jan 4, 2014 at 2:01 PM, Gabriele Bartolini <
gabriele.bartolini@2ndquadrant.it> wrote:

Il 04/01/14 13:25, Magnus Hagander ha scritto:

With those two, I think it would make much sense to have a view like
this.

Ok, I will prepare version 2 with those.

Oh, and you need to change the format id number of the stats file.

I have not found any instruction on how to set it. I assume you are
talking about this:

PGSTAT_FILE_FORMAT_ID 0x01A5BC9B

Any suggestion is welcome.

Yes, that's what I'm talking about. And just increment it by 1.

Not sure where the original value came from, but that's what people have
been doing recently.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

#5Gabriele Bartolini
gabriele.bartolini@2ndQuadrant.it
In reply to: Magnus Hagander (#4)
1 attachment(s)
Re: [PATCH] Support for pg_stat_archiver view

Il 05/01/14 13:52, Magnus Hagander ha scritto:

Yes, that's what I'm talking about. And just increment it by 1.

Done. I am attaching version 2 of the patch, which now implements only
one function (pg_stat_get_archiver()) and adds:

* failed attempts
* WAL of the last failed attempt
* time of the last failed attempt

Thanks for your inputs.

Ciao,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

Attachments:

pg_stat_archiver.patch.v2text/plain; charset=UTF-8; name=pg_stat_archiver.patch.v2; x-mac-creator=0; x-mac-type=0Download
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4ec6981..0094c19 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -270,6 +270,14 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
      </row>
 
      <row>
+      <entry><structname>pg_stat_archiver</><indexterm><primary>pg_stat_archiver</primary></indexterm></entry>
+      <entry>One row only, showing statistics about the
+       WAL archiver process's activity. See
+       <xref linkend="pg-stat-archiver-view"> for details.
+     </entry>
+     </row>
+
+     <row>
       <entry><structname>pg_stat_bgwriter</><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
       <entry>One row only, showing statistics about the
        background writer process's activity. See
@@ -648,6 +656,64 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
    </para>
   </note>
 
+  <table id="pg-stat-archiver-view" xreflabel="pg_stat_archiver">
+   <title><structname>pg_stat_archiver</structname> View</title>
+
+   <tgroup cols="3">
+    <thead>
+    <row>
+      <entry>Column</entry>
+      <entry>Type</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><structfield>archived_wals</></entry>
+      <entry><type>bigint</type></entry>
+      <entry>Number of WAL files that have been successfully archived</entry>
+     </row>
+     <row>
+      <entry><structfield>last_archived_wal</></entry>
+      <entry><type>text</type></entry>
+      <entry>Name of the last successfully archived WAL file</entry>
+     </row>
+     <row>
+      <entry><structfield>last_archived_wal_time</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time of the last successful archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>failed_attempts</></entry>
+      <entry><type>bigint</type></entry>
+      <entry>Number of failed attempts for archiving WAL files</entry>
+     </row>
+     <row>
+      <entry><structfield>last_failed_wal</></entry>
+      <entry><type>text</type></entry>
+      <entry>Name of the WAL file of the last failed archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>last_failed_wal_time</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time of the last failed archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>stats_reset</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time at which these statistics were last reset</entry>
+     </row>
+    </tbody>
+    </tgroup>
+  </table>
+
+  <para>
+   The <structname>pg_stat_archiver</structname> view will always have a
+   single row, containing data about the archiver process of the cluster.
+  </para>
+
+
   <table id="pg-stat-bgwriter-view" xreflabel="pg_stat_bgwriter">
    <title><structname>pg_stat_bgwriter</structname> View</title>
 
@@ -1613,6 +1679,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
        argument (requires superuser privileges).
        Calling <literal>pg_stat_reset_shared('bgwriter')</> will zero all the
        counters shown in the <structname>pg_stat_bgwriter</> view.
+       Calling <literal>pg_stat_reset_shared('archiver')</> will zero all the
+       counters shown in the <structname>pg_stat_archiver</> view.
       </entry>
      </row>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 575a40f..5ea8c87 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -672,6 +672,17 @@ CREATE VIEW pg_stat_xact_user_functions AS
     WHERE P.prolang != 12  -- fast check to eliminate built-in functions
           AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
 
+CREATE VIEW pg_stat_archiver AS
+    SELECT
+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,
+        s.stats_reset
+    FROM pg_stat_get_archiver() s;
+
 CREATE VIEW pg_stat_bgwriter AS
     SELECT
         pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
diff --git a/src/backend/postmaster/pgarch.c b/src/backend/postmaster/pgarch.c
index 2bb572e..60f957c 100644
--- a/src/backend/postmaster/pgarch.c
+++ b/src/backend/postmaster/pgarch.c
@@ -36,6 +36,7 @@
 #include "access/xlog_internal.h"
 #include "libpq/pqsignal.h"
 #include "miscadmin.h"
+#include "pgstat.h"
 #include "postmaster/fork_process.h"
 #include "postmaster/pgarch.h"
 #include "postmaster/postmaster.h"
@@ -46,6 +47,7 @@
 #include "storage/pmsignal.h"
 #include "utils/guc.h"
 #include "utils/ps_status.h"
+#include "utils/timestamp.h"
 
 
 /* ----------
@@ -647,6 +649,15 @@ pgarch_archiveXlog(char *xlog)
 		snprintf(activitymsg, sizeof(activitymsg), "failed on %s", xlog);
 		set_ps_display(activitymsg, false);
 
+		/*
+		 * Send off activity statistics to the stats collector.
+		 */
+		ArchiverStats.m_type = 'F';
+		strncpy(ArchiverStats.m_xlog,
+			xlog, sizeof(ArchiverStats.m_xlog));
+		ArchiverStats.m_timestamp = GetCurrentTimestamp();
+		pgstat_send_archiver();
+
 		return false;
 	}
 	ereport(DEBUG1,
@@ -755,4 +766,12 @@ pgarch_archiveDone(char *xlog)
 				(errcode_for_file_access(),
 				 errmsg("could not rename file \"%s\" to \"%s\": %m",
 						rlogready, rlogdone)));
+	/*
+	 * Send off activity statistics to the stats collector.
+	 */
+	ArchiverStats.m_type = 'A';
+	strncpy(ArchiverStats.m_xlog,
+		xlog, sizeof(ArchiverStats.m_xlog));
+	ArchiverStats.m_timestamp = GetCurrentTimestamp();
+	pgstat_send_archiver();
 }
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 4bfa62e..c039ae2 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -123,6 +123,13 @@ char	   *pgstat_stat_filename = NULL;
 char	   *pgstat_stat_tmpname = NULL;
 
 /*
+ * Archiver global statistics counters (unused in other processes).
+ * Stored directly in a stats message structure so it can be sent
+ * without needing to copy things around.  We assume this inits to zeroes.
+ */
+PgStat_MsgArchiver ArchiverStats;
+
+/*
  * BgWriter global statistics counters (unused in other processes).
  * Stored directly in a stats message structure so it can be sent
  * without needing to copy things around.  We assume this inits to zeroes.
@@ -217,6 +224,12 @@ static PgBackendStatus *localBackendStatusTable = NULL;
 static int	localNumBackends = 0;
 
 /*
+ * Archiver statistics, kept in the stats collector.
+ * Contains statistics regarding the archiving process.
+ */
+static PgStat_ArchiverStats archiverStats;
+
+/*
  * Cluster wide statistics, kept in the stats collector.
  * Contains statistics that are not collected per database
  * or per table.
@@ -292,6 +305,7 @@ static void pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, in
 static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
 static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
 static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+static void pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len);
 static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
 static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
 static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
@@ -1257,7 +1271,9 @@ pgstat_reset_shared_counters(const char *target)
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 				 errmsg("must be superuser to reset statistics counters")));
 
-	if (strcmp(target, "bgwriter") == 0)
+	if (strcmp(target, "archiver") == 0)
+		msg.m_resettarget = RESET_ARCHIVER;
+	else if (strcmp(target, "bgwriter") == 0)
 		msg.m_resettarget = RESET_BGWRITER;
 	else
 		ereport(ERROR,
@@ -2321,6 +2337,23 @@ pgstat_fetch_stat_numbackends(void)
 
 /*
  * ---------
+ * pgstat_fetch_archiver() -
+ *
+ *	Support function for the SQL-callable pgstat* functions. Returns
+ *	a pointer to the archiver statistics struct.
+ * ---------
+ */
+PgStat_ArchiverStats *
+pgstat_fetch_archiver(void)
+{
+	backend_read_statsfile();
+
+	return &archiverStats;
+}
+
+
+/*
+ * ---------
  * pgstat_fetch_global() -
  *
  *	Support function for the SQL-callable pgstat* functions. Returns
@@ -3034,6 +3067,39 @@ pgstat_send(void *msg, int len)
 }
 
 /* ----------
+ * pgstat_send_archiver() -
+ *
+ *		Send archiver statistics to the collector
+ * ----------
+ */
+void
+pgstat_send_archiver(void)
+{
+	/* We assume this initializes to zeroes */
+	static const PgStat_MsgArchiver all_zeroes;
+
+	/*
+	 * This function can be called even if nothing at all has happened. In
+	 * this case, avoid sending a completely empty message to the stats
+	 * collector.
+	 */
+	if (memcmp(&ArchiverStats, &all_zeroes, sizeof(PgStat_MsgArchiver)) == 0)
+		return;
+
+	/*
+	 * Prepare and send the message
+	 */
+	pgstat_setheader(&ArchiverStats.m_hdr, PGSTAT_MTYPE_ARCHIVER);
+	pgstat_send(&ArchiverStats, sizeof(ArchiverStats));
+
+	/*
+	 * Clear out the statistics buffer, so it can be re-used.
+	 */
+	MemSet(&ArchiverStats, 0, sizeof(ArchiverStats));
+}
+
+
+/* ----------
  * pgstat_send_bgwriter() -
  *
  *		Send bgwriter statistics to the collector
@@ -3276,6 +3342,10 @@ PgstatCollectorMain(int argc, char *argv[])
 					pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len);
 					break;
 
+				case PGSTAT_MTYPE_ARCHIVER:
+					pgstat_recv_archiver((PgStat_MsgArchiver *) &msg, len);
+					break;
+
 				case PGSTAT_MTYPE_BGWRITER:
 					pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
 					break;
@@ -3546,6 +3616,7 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
 	 * Set the timestamp of the stats file.
 	 */
 	globalStats.stats_timestamp = GetCurrentTimestamp();
+	archiverStats.stats_timestamp = globalStats.stats_timestamp;
 
 	/*
 	 * Write the file header --- currently just a format ID.
@@ -3561,6 +3632,12 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
 	(void) rc;					/* we'll check for error with ferror */
 
 	/*
+	 * Write archiver stats struct
+	 */
+	rc = fwrite(&archiverStats, sizeof(archiverStats), 1, fpout);
+	(void) rc;					/* we'll check for error with ferror */
+
+	/*
 	 * Walk through the database table.
 	 */
 	hash_seq_init(&hstat, pgStatDBHash);
@@ -3826,16 +3903,18 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
 						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
 
 	/*
-	 * Clear out global statistics so they start from zero in case we can't
-	 * load an existing statsfile.
+	 * Clear out global and archiver statistics so they start from zero
+	 * in case we can't load an existing statsfile.
 	 */
 	memset(&globalStats, 0, sizeof(globalStats));
+	memset(&archiverStats, 0, sizeof(archiverStats));
 
 	/*
 	 * Set the current timestamp (will be kept only in case we can't load an
 	 * existing statsfile).
 	 */
 	globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+	archiverStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
 
 	/*
 	 * Try to open the stats file. If it doesn't exist, the backends simply
@@ -3873,7 +3952,17 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
 	if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats))
 	{
 		ereport(pgStatRunningInCollector ? LOG : WARNING,
-				(errmsg("corrupted statistics file \"%s\"", statfile)));
+				(errmsg("corrupted statistics file (global) \"%s\"", statfile)));
+		goto done;
+	}
+
+	/*
+	 * Read archiver stats struct
+	 */
+	if (fread(&archiverStats, 1, sizeof(archiverStats), fpin) != sizeof(archiverStats))
+	{
+		ereport(pgStatRunningInCollector ? LOG : WARNING,
+				(errmsg("corrupted statistics file (archiver) \"%s\"", statfile)));
 		goto done;
 	}
 
@@ -4167,6 +4256,7 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
 {
 	PgStat_StatDBEntry dbentry;
 	PgStat_GlobalStats myGlobalStats;
+	PgStat_ArchiverStats myArchiverStats;
 	FILE	   *fpin;
 	int32		format_id;
 	const char *statfile = permanent ? PGSTAT_STAT_PERMANENT_FILENAME : pgstat_stat_filename;
@@ -4209,6 +4299,18 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
 		return false;
 	}
 
+	/*
+	 * Read archiver stats struct
+	 */
+	if (fread(&myArchiverStats, 1, sizeof(myArchiverStats),
+			  fpin) != sizeof(myArchiverStats))
+	{
+		ereport(pgStatRunningInCollector ? LOG : WARNING,
+				(errmsg("corrupted statistics file \"%s\"", statfile)));
+		FreeFile(fpin);
+		return false;
+	}
+
 	/* By default, we're going to return the timestamp of the global file. */
 	*ts = myGlobalStats.stats_timestamp;
 
@@ -4736,6 +4838,12 @@ pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
 		memset(&globalStats, 0, sizeof(globalStats));
 		globalStats.stat_reset_timestamp = GetCurrentTimestamp();
 	}
+	else if (msg->m_resettarget == RESET_ARCHIVER)
+	{
+		/* Reset the archiver statistics for the cluster. */
+		memset(&archiverStats, 0, sizeof(archiverStats));
+		archiverStats.stat_reset_timestamp = GetCurrentTimestamp();
+	}
 
 	/*
 	 * Presumably the sender of this message validated the target, don't
@@ -4867,6 +4975,34 @@ pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
 
 
 /* ----------
+ * pgstat_recv_archiver() -
+ *
+ *	Process a ARCHIVER message.
+ * ----------
+ */
+static void
+pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len)
+{
+	switch(msg->m_type)
+	{
+		case 'A':
+			/* Successful archival operation */
+			++archiverStats.archived_wals;
+			memcpy(archiverStats.last_archived_wal, msg->m_xlog,
+				sizeof(archiverStats.last_archived_wal));
+			archiverStats.last_archived_wal_timestamp = msg->m_timestamp;
+			break;
+		case 'F':
+			/* Failed archival attempt */
+			++archiverStats.failed_attempts;
+			memcpy(archiverStats.last_failed_wal, msg->m_xlog,
+				sizeof(archiverStats.last_failed_wal));
+			archiverStats.last_failed_wal_timestamp = msg->m_timestamp;
+			break;
+	}
+}
+
+/* ----------
  * pgstat_recv_bgwriter() -
  *
  *	Process a BGWRITER message.
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2957fc7..d938264 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -87,6 +87,8 @@ extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blk_read_time(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS);
 
+extern Datum pg_stat_get_archiver(PG_FUNCTION_ARGS);
+
 extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS);
@@ -1712,3 +1714,94 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
 
 	PG_RETURN_VOID();
 }
+
+Datum
+pg_stat_get_archiver(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		TupleDesc	tupdesc;
+
+		funcctx = SRF_FIRSTCALL_INIT();
+
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		tupdesc = CreateTemplateTupleDesc(7, false);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "archived_wals",
+						   INT8OID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "last_archived_wal",
+						   TEXTOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 3, "last_archived_wal_time",
+						   TIMESTAMPTZOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 4, "failed_attempts",
+						   INT8OID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 5, "last_failed_wal",
+						   TEXTOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 6, "last_failed_wal_time",
+						   TIMESTAMPTZOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stats_reset",
+						   TIMESTAMPTZOID, -1, 0);
+
+		funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+
+		funcctx->max_calls = 1;
+
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	/* stuff done on every call of the function */
+	funcctx = SRF_PERCALL_SETUP();
+
+	if (funcctx->call_cntr < funcctx->max_calls)
+	{
+		Datum		values[7];
+		bool		nulls[7];
+		HeapTuple	tuple;
+		PgStat_ArchiverStats *archiver_stats;
+
+		MemSet(values, 0, sizeof(values));
+		MemSet(nulls, 0, sizeof(nulls));
+
+		/* Get statistics about the archiver process */
+		archiver_stats = pgstat_fetch_archiver();
+
+		values[0] = Int64GetDatum(archiver_stats->archived_wals);
+		if (archiver_stats->last_archived_wal == 0)
+			nulls[1] = true;
+		else
+			values[1] = CStringGetTextDatum(archiver_stats->last_archived_wal);
+
+		if (archiver_stats->last_archived_wal_timestamp == 0)
+			nulls[2] = true;
+		else
+			values[2] = TimestampTzGetDatum(archiver_stats->last_archived_wal_timestamp);
+
+		values[3] = Int64GetDatum(archiver_stats->failed_attempts);
+		if (archiver_stats->last_failed_wal == 0)
+			nulls[4] = true;
+		else
+			values[4] = CStringGetTextDatum(archiver_stats->last_failed_wal);
+
+		if (archiver_stats->last_failed_wal_timestamp == 0)
+			nulls[5] = true;
+		else
+			values[5] = TimestampTzGetDatum(archiver_stats->last_failed_wal_timestamp);
+
+		if (archiver_stats->stat_reset_timestamp == 0)
+			nulls[6] = true;
+		else
+			values[6] = TimestampTzGetDatum(archiver_stats->stat_reset_timestamp);
+
+		tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+
+		SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
+	}
+	else
+	{
+		/* nothing left */
+		SRF_RETURN_DONE(funcctx);
+	}
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 05d7ba5..313ef80 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2700,6 +2700,8 @@ DATA(insert OID = 2844 (  pg_stat_get_db_blk_read_time	PGNSP PGUID 12 1 0 0 0 f
 DESCR("statistics: block read time, in msec");
 DATA(insert OID = 2845 (  pg_stat_get_db_blk_write_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 701 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blk_write_time _null_ _null_ _null_ ));
 DESCR("statistics: block write time, in msec");
+DATA(insert OID = 3195 (  pg_stat_get_archiver		PGNSP PGUID 12 1 0 0 0 f f f f f f s 0 0 2249 "" "{20,25,1184,20,25,1184,1184}" "{o,o,o,o,o,o,o}" "{archived_wals,last_archived_wal,last_archived_wal_time,failed_attempts,last_failed_wal,last_failed_wal_time,stats_reset}" _null_ pg_stat_get_archiver _null_ _null_ _null_ ));
+DESCR("statistics: information about WAL archiver");
 DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
 DESCR("statistics: number of timed checkpoints started by the bgwriter");
 DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index b79a0eb..14a666a 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -18,6 +18,7 @@
 #include "utils/hsearch.h"
 #include "utils/relcache.h"
 
+#define MAX_XFN_CHARS	40
 
 /* Values for track_functions GUC variable --- order is significant! */
 typedef enum TrackFunctionsLevel
@@ -44,6 +45,7 @@ typedef enum StatMsgType
 	PGSTAT_MTYPE_AUTOVAC_START,
 	PGSTAT_MTYPE_VACUUM,
 	PGSTAT_MTYPE_ANALYZE,
+	PGSTAT_MTYPE_ARCHIVER,
 	PGSTAT_MTYPE_BGWRITER,
 	PGSTAT_MTYPE_FUNCSTAT,
 	PGSTAT_MTYPE_FUNCPURGE,
@@ -102,6 +104,7 @@ typedef struct PgStat_TableCounts
 /* Possible targets for resetting cluster-wide shared values */
 typedef enum PgStat_Shared_Reset_Target
 {
+	RESET_ARCHIVER,
 	RESET_BGWRITER
 } PgStat_Shared_Reset_Target;
 
@@ -355,6 +358,19 @@ typedef struct PgStat_MsgAnalyze
 
 
 /* ----------
+ * PgStat_MsgArchiver			Sent by the archiver to update statistics.
+ * ----------
+ */
+typedef struct PgStat_MsgArchiver
+{
+	PgStat_MsgHdr m_hdr;
+
+	char m_type; /* A = archival, F = failure */
+	char m_xlog[MAX_XFN_CHARS +1];
+	TimestampTz m_timestamp;
+} PgStat_MsgArchiver;
+
+/* ----------
  * PgStat_MsgBgWriter			Sent by the bgwriter to update statistics.
  * ----------
  */
@@ -501,6 +517,7 @@ typedef union PgStat_Msg
 	PgStat_MsgAutovacStart msg_autovacuum;
 	PgStat_MsgVacuum msg_vacuum;
 	PgStat_MsgAnalyze msg_analyze;
+	PgStat_MsgArchiver msg_archiver;
 	PgStat_MsgBgWriter msg_bgwriter;
 	PgStat_MsgFuncstat msg_funcstat;
 	PgStat_MsgFuncpurge msg_funcpurge;
@@ -517,7 +534,7 @@ typedef union PgStat_Msg
  * ------------------------------------------------------------
  */
 
-#define PGSTAT_FILE_FORMAT_ID	0x01A5BC9B
+#define PGSTAT_FILE_FORMAT_ID	0x01A5BC9C
 
 /* ----------
  * PgStat_StatDBEntry			The collector's data per database
@@ -611,6 +628,21 @@ typedef struct PgStat_StatFuncEntry
 
 
 /*
+ * Archiver statistics kept in the stats collector
+ */
+typedef struct PgStat_ArchiverStats
+{
+	TimestampTz stats_timestamp;	/* time of stats file update */
+	PgStat_Counter archived_wals;
+	char last_archived_wal[MAX_XFN_CHARS +1]; /* file name of the last archived WAL */
+	TimestampTz last_archived_wal_timestamp;	/* time of last successful archival */
+	PgStat_Counter failed_attempts;
+	char last_failed_wal[MAX_XFN_CHARS +1]; /* file name of the last failed WAL */
+	TimestampTz last_failed_wal_timestamp;	/* time of last failed attempt of archival */
+	TimestampTz stat_reset_timestamp;
+} PgStat_ArchiverStats;
+
+/*
  * Global statistics kept in the stats collector
  */
 typedef struct PgStat_GlobalStats
@@ -731,6 +763,11 @@ extern char *pgstat_stat_tmpname;
 extern char *pgstat_stat_filename;
 
 /*
+ * Archiver statistics counters are updated directly by pgarch
+ */
+extern PgStat_MsgArchiver ArchiverStats;
+
+/*
  * BgWriter statistics counters are updated directly by bgwriter and bufmgr
  */
 extern PgStat_MsgBgWriter BgWriterStats;
@@ -862,6 +899,7 @@ extern void pgstat_twophase_postcommit(TransactionId xid, uint16 info,
 extern void pgstat_twophase_postabort(TransactionId xid, uint16 info,
 						  void *recdata, uint32 len);
 
+extern void pgstat_send_archiver(void);
 extern void pgstat_send_bgwriter(void);
 
 /* ----------
@@ -874,6 +912,7 @@ extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
 extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
 extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
 extern int	pgstat_fetch_stat_numbackends(void);
+extern PgStat_ArchiverStats *pgstat_fetch_archiver(void);
 extern PgStat_GlobalStats *pgstat_fetch_global(void);
 
 #endif   /* PGSTAT_H */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 9f089e3..bffcb30 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1640,6 +1640,14 @@ pg_stat_all_tables| SELECT c.oid AS relid,
    LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
   GROUP BY c.oid, n.nspname, c.relname;
+pg_stat_archiver| SELECT s.archived_wals,
+    s.last_archived_wal,
+    s.last_archived_wal_time,
+    s.failed_attempts,
+    s.last_failed_wal,
+    s.last_failed_wal_time,
+    s.stats_reset
+   FROM pg_stat_get_archiver() s(archived_wals, last_archived_wal, last_archived_wal_time, failed_attempts, last_failed_wal, last_failed_wal_time, stats_reset);
 pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
     pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
     pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
#6Fabrizio Mello
fabriziomello@gmail.com
In reply to: Gabriele Bartolini (#5)
Re: [PATCH] Support for pg_stat_archiver view

Enviado via iPhone

Em 05/01/2014, às 16:27, Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it> escreveu:

Il 05/01/14 13:52, Magnus Hagander ha scritto:

Yes, that's what I'm talking about. And just increment it by 1.

Done. I am attaching version 2 of the patch, which now implements only
one function (pg_stat_get_archiver()) and adds:

* failed attempts
* WAL of the last failed attempt
* time of the last failed attempt

Hi,

I don't see your code yet, but I would like to know if is possible to implement this view as an extension.

Regards,

Fabrízio Mello

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#7Gabriele Bartolini
gabriele.bartolini@2ndQuadrant.it
In reply to: Fabrizio Mello (#6)
Re: [PATCH] Support for pg_stat_archiver view

Hi Fabrizio,

Il 05/01/14 20:46, Fabrizio Mello ha scritto:

I don't see your code yet, but I would like to know if is possible to
implement this view as an extension.

I wanted to do it as an extension - so that I could backport that to
previous versions of Postgres.

I do not think it is a possibility, given that the client code that is
aware of the events lies in pgarch.c.

Ciao,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#8Michael Paquier
michael.paquier@gmail.com
In reply to: Gabriele Bartolini (#7)
Re: [PATCH] Support for pg_stat_archiver view

On Mon, Jan 6, 2014 at 5:37 PM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Hi Fabrizio,

Il 05/01/14 20:46, Fabrizio Mello ha scritto:

I don't see your code yet, but I would like to know if is possible to
implement this view as an extension.

I wanted to do it as an extension - so that I could backport that to
previous versions of Postgres.

I do not think it is a possibility, given that the client code that is
aware of the events lies in pgarch.c.

I don't see particularly any point in doing that as an extension as
you want to log this statistical information once archive has either
failed or passed.

I just had a quick look at v2, and it looks that the patch is in good
shape. Sorry to be picky, but I am not sure that using the character
m_type is adapted when delivering messages to pgstat facility. You
might want to use a boolean instead... MAX_XFN_CHARS is not adapted as
well IMO: you should remove it and use instead MAXFNAMELEN of
xlog_internal.h, where all the file names related to WAL files,
including history and backup files, are generated.

Then, the patch looks to be working as expected, here are some results
with a short test:
=# \x
Expanded display (expanded) is on.
=# select * from pg_stat_get_archiver();
-[ RECORD 1 ]----------+------------------------------
archived_wals | 6
last_archived_wal | 000000010000000000000005
last_archived_wal_time | 2014-01-07 17:27:34.752903+09
failed_attempts | 12
last_failed_wal | 000000010000000000000006
last_failed_wal_time | 2014-01-07 17:31:18.409528+09
stats_reset
(1 row)
=# select * from pg_stat_archiver;
-[ RECORD 1 ]----------+------------------------------
archived_wals | 6
last_archived_wal | 000000010000000000000005
last_archived_wal_time | 2014-01-07 17:27:34.752903+09
failed_attempts | 12
last_failed_wal | 000000010000000000000006
last_failed_wal_time | 2014-01-07 17:31:18.409528+09
stats_reset | 2014-01-07 17:25:51.949498+09

Regards,
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#9Gabriele Bartolini
gabriele.bartolini@2ndQuadrant.it
In reply to: Michael Paquier (#8)
1 attachment(s)
Re: [PATCH] Support for pg_stat_archiver view

Hi Michael,

Il 07/01/14 09:33, Michael Paquier ha scritto:

I just had a quick look at v2, and it looks that the patch is in good
shape.

Thanks.

Sorry to be picky, but I am not sure that using the character m_type
is adapted when delivering messages to pgstat facility. You might want
to use a boolean instead...

I had had the same doubt, and opted for a more generic approach using a
char. I have followed your tip and made the field a boolean.

MAX_XFN_CHARS is not adapted as well IMO: you should remove it and use
instead MAXFNAMELEN of xlog_internal.h

Again, I had some concerns about redefining a constant (as MAX_XFN_CHARS
was defined only in pgarch.c). I wonder now if we should change this
behaviour too in pgarch.c (but that's a different patch).

Please find attached version 3.

I will update the commitfest page and add your previous message as
comment/review. I will tentatively add you as reviewer of the patch (as
well as Magnus).

Thanks,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

Attachments:

pg_stat_archiver.patch.v3text/plain; charset=UTF-8; name=pg_stat_archiver.patch.v3; x-mac-creator=0; x-mac-type=0Download
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4ec6981..0094c19 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -270,6 +270,14 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
      </row>
 
      <row>
+      <entry><structname>pg_stat_archiver</><indexterm><primary>pg_stat_archiver</primary></indexterm></entry>
+      <entry>One row only, showing statistics about the
+       WAL archiver process's activity. See
+       <xref linkend="pg-stat-archiver-view"> for details.
+     </entry>
+     </row>
+
+     <row>
       <entry><structname>pg_stat_bgwriter</><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
       <entry>One row only, showing statistics about the
        background writer process's activity. See
@@ -648,6 +656,64 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
    </para>
   </note>
 
+  <table id="pg-stat-archiver-view" xreflabel="pg_stat_archiver">
+   <title><structname>pg_stat_archiver</structname> View</title>
+
+   <tgroup cols="3">
+    <thead>
+    <row>
+      <entry>Column</entry>
+      <entry>Type</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><structfield>archived_wals</></entry>
+      <entry><type>bigint</type></entry>
+      <entry>Number of WAL files that have been successfully archived</entry>
+     </row>
+     <row>
+      <entry><structfield>last_archived_wal</></entry>
+      <entry><type>text</type></entry>
+      <entry>Name of the last successfully archived WAL file</entry>
+     </row>
+     <row>
+      <entry><structfield>last_archived_wal_time</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time of the last successful archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>failed_attempts</></entry>
+      <entry><type>bigint</type></entry>
+      <entry>Number of failed attempts for archiving WAL files</entry>
+     </row>
+     <row>
+      <entry><structfield>last_failed_wal</></entry>
+      <entry><type>text</type></entry>
+      <entry>Name of the WAL file of the last failed archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>last_failed_wal_time</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time of the last failed archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>stats_reset</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time at which these statistics were last reset</entry>
+     </row>
+    </tbody>
+    </tgroup>
+  </table>
+
+  <para>
+   The <structname>pg_stat_archiver</structname> view will always have a
+   single row, containing data about the archiver process of the cluster.
+  </para>
+
+
   <table id="pg-stat-bgwriter-view" xreflabel="pg_stat_bgwriter">
    <title><structname>pg_stat_bgwriter</structname> View</title>
 
@@ -1613,6 +1679,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
        argument (requires superuser privileges).
        Calling <literal>pg_stat_reset_shared('bgwriter')</> will zero all the
        counters shown in the <structname>pg_stat_bgwriter</> view.
+       Calling <literal>pg_stat_reset_shared('archiver')</> will zero all the
+       counters shown in the <structname>pg_stat_archiver</> view.
       </entry>
      </row>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 575a40f..5ea8c87 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -672,6 +672,17 @@ CREATE VIEW pg_stat_xact_user_functions AS
     WHERE P.prolang != 12  -- fast check to eliminate built-in functions
           AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
 
+CREATE VIEW pg_stat_archiver AS
+    SELECT
+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,
+        s.stats_reset
+    FROM pg_stat_get_archiver() s;
+
 CREATE VIEW pg_stat_bgwriter AS
     SELECT
         pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
diff --git a/src/backend/postmaster/pgarch.c b/src/backend/postmaster/pgarch.c
index 2bb572e..d3b5d33 100644
--- a/src/backend/postmaster/pgarch.c
+++ b/src/backend/postmaster/pgarch.c
@@ -36,6 +36,7 @@
 #include "access/xlog_internal.h"
 #include "libpq/pqsignal.h"
 #include "miscadmin.h"
+#include "pgstat.h"
 #include "postmaster/fork_process.h"
 #include "postmaster/pgarch.h"
 #include "postmaster/postmaster.h"
@@ -46,6 +47,7 @@
 #include "storage/pmsignal.h"
 #include "utils/guc.h"
 #include "utils/ps_status.h"
+#include "utils/timestamp.h"
 
 
 /* ----------
@@ -647,6 +649,15 @@ pgarch_archiveXlog(char *xlog)
 		snprintf(activitymsg, sizeof(activitymsg), "failed on %s", xlog);
 		set_ps_display(activitymsg, false);
 
+		/*
+		 * Send off activity statistics to the stats collector.
+		 */
+		ArchiverStats.m_failed = true;
+		strncpy(ArchiverStats.m_xlog,
+			xlog, sizeof(ArchiverStats.m_xlog));
+		ArchiverStats.m_timestamp = GetCurrentTimestamp();
+		pgstat_send_archiver();
+
 		return false;
 	}
 	ereport(DEBUG1,
@@ -755,4 +766,12 @@ pgarch_archiveDone(char *xlog)
 				(errcode_for_file_access(),
 				 errmsg("could not rename file \"%s\" to \"%s\": %m",
 						rlogready, rlogdone)));
+	/*
+	 * Send off activity statistics to the stats collector.
+	 */
+	ArchiverStats.m_failed = false;
+	strncpy(ArchiverStats.m_xlog,
+		xlog, sizeof(ArchiverStats.m_xlog));
+	ArchiverStats.m_timestamp = GetCurrentTimestamp();
+	pgstat_send_archiver();
 }
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 4bfa62e..926261f 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -123,6 +123,13 @@ char	   *pgstat_stat_filename = NULL;
 char	   *pgstat_stat_tmpname = NULL;
 
 /*
+ * Archiver global statistics counters (unused in other processes).
+ * Stored directly in a stats message structure so it can be sent
+ * without needing to copy things around.  We assume this inits to zeroes.
+ */
+PgStat_MsgArchiver ArchiverStats;
+
+/*
  * BgWriter global statistics counters (unused in other processes).
  * Stored directly in a stats message structure so it can be sent
  * without needing to copy things around.  We assume this inits to zeroes.
@@ -217,6 +224,12 @@ static PgBackendStatus *localBackendStatusTable = NULL;
 static int	localNumBackends = 0;
 
 /*
+ * Archiver statistics, kept in the stats collector.
+ * Contains statistics regarding the archiving process.
+ */
+static PgStat_ArchiverStats archiverStats;
+
+/*
  * Cluster wide statistics, kept in the stats collector.
  * Contains statistics that are not collected per database
  * or per table.
@@ -292,6 +305,7 @@ static void pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, in
 static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
 static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
 static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+static void pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len);
 static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
 static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
 static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
@@ -1257,7 +1271,9 @@ pgstat_reset_shared_counters(const char *target)
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 				 errmsg("must be superuser to reset statistics counters")));
 
-	if (strcmp(target, "bgwriter") == 0)
+	if (strcmp(target, "archiver") == 0)
+		msg.m_resettarget = RESET_ARCHIVER;
+	else if (strcmp(target, "bgwriter") == 0)
 		msg.m_resettarget = RESET_BGWRITER;
 	else
 		ereport(ERROR,
@@ -2321,6 +2337,23 @@ pgstat_fetch_stat_numbackends(void)
 
 /*
  * ---------
+ * pgstat_fetch_archiver() -
+ *
+ *	Support function for the SQL-callable pgstat* functions. Returns
+ *	a pointer to the archiver statistics struct.
+ * ---------
+ */
+PgStat_ArchiverStats *
+pgstat_fetch_archiver(void)
+{
+	backend_read_statsfile();
+
+	return &archiverStats;
+}
+
+
+/*
+ * ---------
  * pgstat_fetch_global() -
  *
  *	Support function for the SQL-callable pgstat* functions. Returns
@@ -3034,6 +3067,39 @@ pgstat_send(void *msg, int len)
 }
 
 /* ----------
+ * pgstat_send_archiver() -
+ *
+ *		Send archiver statistics to the collector
+ * ----------
+ */
+void
+pgstat_send_archiver(void)
+{
+	/* We assume this initializes to zeroes */
+	static const PgStat_MsgArchiver all_zeroes;
+
+	/*
+	 * This function can be called even if nothing at all has happened. In
+	 * this case, avoid sending a completely empty message to the stats
+	 * collector.
+	 */
+	if (memcmp(&ArchiverStats, &all_zeroes, sizeof(PgStat_MsgArchiver)) == 0)
+		return;
+
+	/*
+	 * Prepare and send the message
+	 */
+	pgstat_setheader(&ArchiverStats.m_hdr, PGSTAT_MTYPE_ARCHIVER);
+	pgstat_send(&ArchiverStats, sizeof(ArchiverStats));
+
+	/*
+	 * Clear out the statistics buffer, so it can be re-used.
+	 */
+	MemSet(&ArchiverStats, 0, sizeof(ArchiverStats));
+}
+
+
+/* ----------
  * pgstat_send_bgwriter() -
  *
  *		Send bgwriter statistics to the collector
@@ -3276,6 +3342,10 @@ PgstatCollectorMain(int argc, char *argv[])
 					pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len);
 					break;
 
+				case PGSTAT_MTYPE_ARCHIVER:
+					pgstat_recv_archiver((PgStat_MsgArchiver *) &msg, len);
+					break;
+
 				case PGSTAT_MTYPE_BGWRITER:
 					pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
 					break;
@@ -3546,6 +3616,7 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
 	 * Set the timestamp of the stats file.
 	 */
 	globalStats.stats_timestamp = GetCurrentTimestamp();
+	archiverStats.stats_timestamp = globalStats.stats_timestamp;
 
 	/*
 	 * Write the file header --- currently just a format ID.
@@ -3561,6 +3632,12 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
 	(void) rc;					/* we'll check for error with ferror */
 
 	/*
+	 * Write archiver stats struct
+	 */
+	rc = fwrite(&archiverStats, sizeof(archiverStats), 1, fpout);
+	(void) rc;					/* we'll check for error with ferror */
+
+	/*
 	 * Walk through the database table.
 	 */
 	hash_seq_init(&hstat, pgStatDBHash);
@@ -3826,16 +3903,18 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
 						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
 
 	/*
-	 * Clear out global statistics so they start from zero in case we can't
-	 * load an existing statsfile.
+	 * Clear out global and archiver statistics so they start from zero
+	 * in case we can't load an existing statsfile.
 	 */
 	memset(&globalStats, 0, sizeof(globalStats));
+	memset(&archiverStats, 0, sizeof(archiverStats));
 
 	/*
 	 * Set the current timestamp (will be kept only in case we can't load an
 	 * existing statsfile).
 	 */
 	globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+	archiverStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
 
 	/*
 	 * Try to open the stats file. If it doesn't exist, the backends simply
@@ -3873,7 +3952,17 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
 	if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats))
 	{
 		ereport(pgStatRunningInCollector ? LOG : WARNING,
-				(errmsg("corrupted statistics file \"%s\"", statfile)));
+				(errmsg("corrupted statistics file (global) \"%s\"", statfile)));
+		goto done;
+	}
+
+	/*
+	 * Read archiver stats struct
+	 */
+	if (fread(&archiverStats, 1, sizeof(archiverStats), fpin) != sizeof(archiverStats))
+	{
+		ereport(pgStatRunningInCollector ? LOG : WARNING,
+				(errmsg("corrupted statistics file (archiver) \"%s\"", statfile)));
 		goto done;
 	}
 
@@ -4167,6 +4256,7 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
 {
 	PgStat_StatDBEntry dbentry;
 	PgStat_GlobalStats myGlobalStats;
+	PgStat_ArchiverStats myArchiverStats;
 	FILE	   *fpin;
 	int32		format_id;
 	const char *statfile = permanent ? PGSTAT_STAT_PERMANENT_FILENAME : pgstat_stat_filename;
@@ -4209,6 +4299,18 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
 		return false;
 	}
 
+	/*
+	 * Read archiver stats struct
+	 */
+	if (fread(&myArchiverStats, 1, sizeof(myArchiverStats),
+			  fpin) != sizeof(myArchiverStats))
+	{
+		ereport(pgStatRunningInCollector ? LOG : WARNING,
+				(errmsg("corrupted statistics file \"%s\"", statfile)));
+		FreeFile(fpin);
+		return false;
+	}
+
 	/* By default, we're going to return the timestamp of the global file. */
 	*ts = myGlobalStats.stats_timestamp;
 
@@ -4736,6 +4838,12 @@ pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
 		memset(&globalStats, 0, sizeof(globalStats));
 		globalStats.stat_reset_timestamp = GetCurrentTimestamp();
 	}
+	else if (msg->m_resettarget == RESET_ARCHIVER)
+	{
+		/* Reset the archiver statistics for the cluster. */
+		memset(&archiverStats, 0, sizeof(archiverStats));
+		archiverStats.stat_reset_timestamp = GetCurrentTimestamp();
+	}
 
 	/*
 	 * Presumably the sender of this message validated the target, don't
@@ -4867,6 +4975,33 @@ pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
 
 
 /* ----------
+ * pgstat_recv_archiver() -
+ *
+ *	Process a ARCHIVER message.
+ * ----------
+ */
+static void
+pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len)
+{
+	if (msg->m_failed)
+	{
+		/* Failed archival attempt */
+		++archiverStats.failed_attempts;
+		memcpy(archiverStats.last_failed_wal, msg->m_xlog,
+			sizeof(archiverStats.last_failed_wal));
+		archiverStats.last_failed_wal_timestamp = msg->m_timestamp;
+	}
+	else
+	{
+		/* Successful archival operation */
+		++archiverStats.archived_wals;
+		memcpy(archiverStats.last_archived_wal, msg->m_xlog,
+			sizeof(archiverStats.last_archived_wal));
+		archiverStats.last_archived_wal_timestamp = msg->m_timestamp;
+	}
+}
+
+/* ----------
  * pgstat_recv_bgwriter() -
  *
  *	Process a BGWRITER message.
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2957fc7..d938264 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -87,6 +87,8 @@ extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blk_read_time(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS);
 
+extern Datum pg_stat_get_archiver(PG_FUNCTION_ARGS);
+
 extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS);
@@ -1712,3 +1714,94 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
 
 	PG_RETURN_VOID();
 }
+
+Datum
+pg_stat_get_archiver(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		TupleDesc	tupdesc;
+
+		funcctx = SRF_FIRSTCALL_INIT();
+
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		tupdesc = CreateTemplateTupleDesc(7, false);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "archived_wals",
+						   INT8OID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "last_archived_wal",
+						   TEXTOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 3, "last_archived_wal_time",
+						   TIMESTAMPTZOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 4, "failed_attempts",
+						   INT8OID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 5, "last_failed_wal",
+						   TEXTOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 6, "last_failed_wal_time",
+						   TIMESTAMPTZOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stats_reset",
+						   TIMESTAMPTZOID, -1, 0);
+
+		funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+
+		funcctx->max_calls = 1;
+
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	/* stuff done on every call of the function */
+	funcctx = SRF_PERCALL_SETUP();
+
+	if (funcctx->call_cntr < funcctx->max_calls)
+	{
+		Datum		values[7];
+		bool		nulls[7];
+		HeapTuple	tuple;
+		PgStat_ArchiverStats *archiver_stats;
+
+		MemSet(values, 0, sizeof(values));
+		MemSet(nulls, 0, sizeof(nulls));
+
+		/* Get statistics about the archiver process */
+		archiver_stats = pgstat_fetch_archiver();
+
+		values[0] = Int64GetDatum(archiver_stats->archived_wals);
+		if (archiver_stats->last_archived_wal == 0)
+			nulls[1] = true;
+		else
+			values[1] = CStringGetTextDatum(archiver_stats->last_archived_wal);
+
+		if (archiver_stats->last_archived_wal_timestamp == 0)
+			nulls[2] = true;
+		else
+			values[2] = TimestampTzGetDatum(archiver_stats->last_archived_wal_timestamp);
+
+		values[3] = Int64GetDatum(archiver_stats->failed_attempts);
+		if (archiver_stats->last_failed_wal == 0)
+			nulls[4] = true;
+		else
+			values[4] = CStringGetTextDatum(archiver_stats->last_failed_wal);
+
+		if (archiver_stats->last_failed_wal_timestamp == 0)
+			nulls[5] = true;
+		else
+			values[5] = TimestampTzGetDatum(archiver_stats->last_failed_wal_timestamp);
+
+		if (archiver_stats->stat_reset_timestamp == 0)
+			nulls[6] = true;
+		else
+			values[6] = TimestampTzGetDatum(archiver_stats->stat_reset_timestamp);
+
+		tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+
+		SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
+	}
+	else
+	{
+		/* nothing left */
+		SRF_RETURN_DONE(funcctx);
+	}
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 05d7ba5..313ef80 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2700,6 +2700,8 @@ DATA(insert OID = 2844 (  pg_stat_get_db_blk_read_time	PGNSP PGUID 12 1 0 0 0 f
 DESCR("statistics: block read time, in msec");
 DATA(insert OID = 2845 (  pg_stat_get_db_blk_write_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 701 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blk_write_time _null_ _null_ _null_ ));
 DESCR("statistics: block write time, in msec");
+DATA(insert OID = 3195 (  pg_stat_get_archiver		PGNSP PGUID 12 1 0 0 0 f f f f f f s 0 0 2249 "" "{20,25,1184,20,25,1184,1184}" "{o,o,o,o,o,o,o}" "{archived_wals,last_archived_wal,last_archived_wal_time,failed_attempts,last_failed_wal,last_failed_wal_time,stats_reset}" _null_ pg_stat_get_archiver _null_ _null_ _null_ ));
+DESCR("statistics: information about WAL archiver");
 DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
 DESCR("statistics: number of timed checkpoints started by the bgwriter");
 DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index b79a0eb..a78f1a4 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -17,6 +17,7 @@
 #include "portability/instr_time.h"
 #include "utils/hsearch.h"
 #include "utils/relcache.h"
+#include "access/xlog_internal.h"
 
 
 /* Values for track_functions GUC variable --- order is significant! */
@@ -44,6 +45,7 @@ typedef enum StatMsgType
 	PGSTAT_MTYPE_AUTOVAC_START,
 	PGSTAT_MTYPE_VACUUM,
 	PGSTAT_MTYPE_ANALYZE,
+	PGSTAT_MTYPE_ARCHIVER,
 	PGSTAT_MTYPE_BGWRITER,
 	PGSTAT_MTYPE_FUNCSTAT,
 	PGSTAT_MTYPE_FUNCPURGE,
@@ -102,6 +104,7 @@ typedef struct PgStat_TableCounts
 /* Possible targets for resetting cluster-wide shared values */
 typedef enum PgStat_Shared_Reset_Target
 {
+	RESET_ARCHIVER,
 	RESET_BGWRITER
 } PgStat_Shared_Reset_Target;
 
@@ -355,6 +358,19 @@ typedef struct PgStat_MsgAnalyze
 
 
 /* ----------
+ * PgStat_MsgArchiver			Sent by the archiver to update statistics.
+ * ----------
+ */
+typedef struct PgStat_MsgArchiver
+{
+	PgStat_MsgHdr m_hdr;
+
+	bool m_failed; /* Failed attempt */
+	char m_xlog[MAXFNAMELEN +1];
+	TimestampTz m_timestamp;
+} PgStat_MsgArchiver;
+
+/* ----------
  * PgStat_MsgBgWriter			Sent by the bgwriter to update statistics.
  * ----------
  */
@@ -501,6 +517,7 @@ typedef union PgStat_Msg
 	PgStat_MsgAutovacStart msg_autovacuum;
 	PgStat_MsgVacuum msg_vacuum;
 	PgStat_MsgAnalyze msg_analyze;
+	PgStat_MsgArchiver msg_archiver;
 	PgStat_MsgBgWriter msg_bgwriter;
 	PgStat_MsgFuncstat msg_funcstat;
 	PgStat_MsgFuncpurge msg_funcpurge;
@@ -517,7 +534,7 @@ typedef union PgStat_Msg
  * ------------------------------------------------------------
  */
 
-#define PGSTAT_FILE_FORMAT_ID	0x01A5BC9B
+#define PGSTAT_FILE_FORMAT_ID	0x01A5BC9C
 
 /* ----------
  * PgStat_StatDBEntry			The collector's data per database
@@ -611,6 +628,21 @@ typedef struct PgStat_StatFuncEntry
 
 
 /*
+ * Archiver statistics kept in the stats collector
+ */
+typedef struct PgStat_ArchiverStats
+{
+	TimestampTz stats_timestamp;	/* time of stats file update */
+	PgStat_Counter archived_wals;
+	char last_archived_wal[MAXFNAMELEN +1]; /* file name of the last archived WAL */
+	TimestampTz last_archived_wal_timestamp;	/* time of last successful archival */
+	PgStat_Counter failed_attempts;
+	char last_failed_wal[MAXFNAMELEN +1]; /* file name of the last failed WAL */
+	TimestampTz last_failed_wal_timestamp;	/* time of last failed attempt of archival */
+	TimestampTz stat_reset_timestamp;
+} PgStat_ArchiverStats;
+
+/*
  * Global statistics kept in the stats collector
  */
 typedef struct PgStat_GlobalStats
@@ -731,6 +763,11 @@ extern char *pgstat_stat_tmpname;
 extern char *pgstat_stat_filename;
 
 /*
+ * Archiver statistics counters are updated directly by pgarch
+ */
+extern PgStat_MsgArchiver ArchiverStats;
+
+/*
  * BgWriter statistics counters are updated directly by bgwriter and bufmgr
  */
 extern PgStat_MsgBgWriter BgWriterStats;
@@ -862,6 +899,7 @@ extern void pgstat_twophase_postcommit(TransactionId xid, uint16 info,
 extern void pgstat_twophase_postabort(TransactionId xid, uint16 info,
 						  void *recdata, uint32 len);
 
+extern void pgstat_send_archiver(void);
 extern void pgstat_send_bgwriter(void);
 
 /* ----------
@@ -874,6 +912,7 @@ extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
 extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
 extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
 extern int	pgstat_fetch_stat_numbackends(void);
+extern PgStat_ArchiverStats *pgstat_fetch_archiver(void);
 extern PgStat_GlobalStats *pgstat_fetch_global(void);
 
 #endif   /* PGSTAT_H */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 9f089e3..bffcb30 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1640,6 +1640,14 @@ pg_stat_all_tables| SELECT c.oid AS relid,
    LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
   GROUP BY c.oid, n.nspname, c.relname;
+pg_stat_archiver| SELECT s.archived_wals,
+    s.last_archived_wal,
+    s.last_archived_wal_time,
+    s.failed_attempts,
+    s.last_failed_wal,
+    s.last_failed_wal_time,
+    s.stats_reset
+   FROM pg_stat_get_archiver() s(archived_wals, last_archived_wal, last_archived_wal_time, failed_attempts, last_failed_wal, last_failed_wal_time, stats_reset);
 pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
     pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
     pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
#10Simon Riggs
simon@2ndQuadrant.com
In reply to: Gabriele Bartolini (#3)
Re: [PATCH] Support for pg_stat_archiver view

On 4 January 2014 13:01, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

I'd suggest making the view on top of an SRF like pg_stat_replication
and pg_stat_activity (for example), instead of a whole lot of separate
function calls like the older stats views.

Ok, good idea.

Not sure I see why it needs to be an SRF. It only returns one row.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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

#11Magnus Hagander
magnus@hagander.net
In reply to: Simon Riggs (#10)
Re: [PATCH] Support for pg_stat_archiver view

On Wed, Jan 8, 2014 at 6:42 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On 4 January 2014 13:01, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

I'd suggest making the view on top of an SRF like pg_stat_replication
and pg_stat_activity (for example), instead of a whole lot of separate
function calls like the older stats views.

Ok, good idea.

Not sure I see why it needs to be an SRF. It only returns one row.

Good point, it could/should be a general function returning a composite
type.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

#12Gabriele Bartolini
gabriele.bartolini@2ndQuadrant.it
In reply to: Simon Riggs (#10)
1 attachment(s)
Re: [PATCH] Support for pg_stat_archiver view

Hi Simon,

Il 08/01/14 18:42, Simon Riggs ha scritto:

Not sure I see why it needs to be an SRF. It only returns one row.

Attached is version 4, which removes management of SRF stages.

Thanks for pointing it out.

Cheers,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

Attachments:

pg_stat_archiver.patch.v4text/plain; charset=UTF-8; name=pg_stat_archiver.patch.v4; x-mac-creator=0; x-mac-type=0Download
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4ec6981..0094c19 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -270,6 +270,14 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
      </row>
 
      <row>
+      <entry><structname>pg_stat_archiver</><indexterm><primary>pg_stat_archiver</primary></indexterm></entry>
+      <entry>One row only, showing statistics about the
+       WAL archiver process's activity. See
+       <xref linkend="pg-stat-archiver-view"> for details.
+     </entry>
+     </row>
+
+     <row>
       <entry><structname>pg_stat_bgwriter</><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
       <entry>One row only, showing statistics about the
        background writer process's activity. See
@@ -648,6 +656,64 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
    </para>
   </note>
 
+  <table id="pg-stat-archiver-view" xreflabel="pg_stat_archiver">
+   <title><structname>pg_stat_archiver</structname> View</title>
+
+   <tgroup cols="3">
+    <thead>
+    <row>
+      <entry>Column</entry>
+      <entry>Type</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><structfield>archived_wals</></entry>
+      <entry><type>bigint</type></entry>
+      <entry>Number of WAL files that have been successfully archived</entry>
+     </row>
+     <row>
+      <entry><structfield>last_archived_wal</></entry>
+      <entry><type>text</type></entry>
+      <entry>Name of the last successfully archived WAL file</entry>
+     </row>
+     <row>
+      <entry><structfield>last_archived_wal_time</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time of the last successful archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>failed_attempts</></entry>
+      <entry><type>bigint</type></entry>
+      <entry>Number of failed attempts for archiving WAL files</entry>
+     </row>
+     <row>
+      <entry><structfield>last_failed_wal</></entry>
+      <entry><type>text</type></entry>
+      <entry>Name of the WAL file of the last failed archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>last_failed_wal_time</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time of the last failed archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>stats_reset</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time at which these statistics were last reset</entry>
+     </row>
+    </tbody>
+    </tgroup>
+  </table>
+
+  <para>
+   The <structname>pg_stat_archiver</structname> view will always have a
+   single row, containing data about the archiver process of the cluster.
+  </para>
+
+
   <table id="pg-stat-bgwriter-view" xreflabel="pg_stat_bgwriter">
    <title><structname>pg_stat_bgwriter</structname> View</title>
 
@@ -1613,6 +1679,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
        argument (requires superuser privileges).
        Calling <literal>pg_stat_reset_shared('bgwriter')</> will zero all the
        counters shown in the <structname>pg_stat_bgwriter</> view.
+       Calling <literal>pg_stat_reset_shared('archiver')</> will zero all the
+       counters shown in the <structname>pg_stat_archiver</> view.
       </entry>
      </row>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 575a40f..5ea8c87 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -672,6 +672,17 @@ CREATE VIEW pg_stat_xact_user_functions AS
     WHERE P.prolang != 12  -- fast check to eliminate built-in functions
           AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
 
+CREATE VIEW pg_stat_archiver AS
+    SELECT
+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,
+        s.stats_reset
+    FROM pg_stat_get_archiver() s;
+
 CREATE VIEW pg_stat_bgwriter AS
     SELECT
         pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
diff --git a/src/backend/postmaster/pgarch.c b/src/backend/postmaster/pgarch.c
index 2bb572e..d3b5d33 100644
--- a/src/backend/postmaster/pgarch.c
+++ b/src/backend/postmaster/pgarch.c
@@ -36,6 +36,7 @@
 #include "access/xlog_internal.h"
 #include "libpq/pqsignal.h"
 #include "miscadmin.h"
+#include "pgstat.h"
 #include "postmaster/fork_process.h"
 #include "postmaster/pgarch.h"
 #include "postmaster/postmaster.h"
@@ -46,6 +47,7 @@
 #include "storage/pmsignal.h"
 #include "utils/guc.h"
 #include "utils/ps_status.h"
+#include "utils/timestamp.h"
 
 
 /* ----------
@@ -647,6 +649,15 @@ pgarch_archiveXlog(char *xlog)
 		snprintf(activitymsg, sizeof(activitymsg), "failed on %s", xlog);
 		set_ps_display(activitymsg, false);
 
+		/*
+		 * Send off activity statistics to the stats collector.
+		 */
+		ArchiverStats.m_failed = true;
+		strncpy(ArchiverStats.m_xlog,
+			xlog, sizeof(ArchiverStats.m_xlog));
+		ArchiverStats.m_timestamp = GetCurrentTimestamp();
+		pgstat_send_archiver();
+
 		return false;
 	}
 	ereport(DEBUG1,
@@ -755,4 +766,12 @@ pgarch_archiveDone(char *xlog)
 				(errcode_for_file_access(),
 				 errmsg("could not rename file \"%s\" to \"%s\": %m",
 						rlogready, rlogdone)));
+	/*
+	 * Send off activity statistics to the stats collector.
+	 */
+	ArchiverStats.m_failed = false;
+	strncpy(ArchiverStats.m_xlog,
+		xlog, sizeof(ArchiverStats.m_xlog));
+	ArchiverStats.m_timestamp = GetCurrentTimestamp();
+	pgstat_send_archiver();
 }
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 4bfa62e..926261f 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -123,6 +123,13 @@ char	   *pgstat_stat_filename = NULL;
 char	   *pgstat_stat_tmpname = NULL;
 
 /*
+ * Archiver global statistics counters (unused in other processes).
+ * Stored directly in a stats message structure so it can be sent
+ * without needing to copy things around.  We assume this inits to zeroes.
+ */
+PgStat_MsgArchiver ArchiverStats;
+
+/*
  * BgWriter global statistics counters (unused in other processes).
  * Stored directly in a stats message structure so it can be sent
  * without needing to copy things around.  We assume this inits to zeroes.
@@ -217,6 +224,12 @@ static PgBackendStatus *localBackendStatusTable = NULL;
 static int	localNumBackends = 0;
 
 /*
+ * Archiver statistics, kept in the stats collector.
+ * Contains statistics regarding the archiving process.
+ */
+static PgStat_ArchiverStats archiverStats;
+
+/*
  * Cluster wide statistics, kept in the stats collector.
  * Contains statistics that are not collected per database
  * or per table.
@@ -292,6 +305,7 @@ static void pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, in
 static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
 static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
 static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+static void pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len);
 static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
 static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
 static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
@@ -1257,7 +1271,9 @@ pgstat_reset_shared_counters(const char *target)
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 				 errmsg("must be superuser to reset statistics counters")));
 
-	if (strcmp(target, "bgwriter") == 0)
+	if (strcmp(target, "archiver") == 0)
+		msg.m_resettarget = RESET_ARCHIVER;
+	else if (strcmp(target, "bgwriter") == 0)
 		msg.m_resettarget = RESET_BGWRITER;
 	else
 		ereport(ERROR,
@@ -2321,6 +2337,23 @@ pgstat_fetch_stat_numbackends(void)
 
 /*
  * ---------
+ * pgstat_fetch_archiver() -
+ *
+ *	Support function for the SQL-callable pgstat* functions. Returns
+ *	a pointer to the archiver statistics struct.
+ * ---------
+ */
+PgStat_ArchiverStats *
+pgstat_fetch_archiver(void)
+{
+	backend_read_statsfile();
+
+	return &archiverStats;
+}
+
+
+/*
+ * ---------
  * pgstat_fetch_global() -
  *
  *	Support function for the SQL-callable pgstat* functions. Returns
@@ -3034,6 +3067,39 @@ pgstat_send(void *msg, int len)
 }
 
 /* ----------
+ * pgstat_send_archiver() -
+ *
+ *		Send archiver statistics to the collector
+ * ----------
+ */
+void
+pgstat_send_archiver(void)
+{
+	/* We assume this initializes to zeroes */
+	static const PgStat_MsgArchiver all_zeroes;
+
+	/*
+	 * This function can be called even if nothing at all has happened. In
+	 * this case, avoid sending a completely empty message to the stats
+	 * collector.
+	 */
+	if (memcmp(&ArchiverStats, &all_zeroes, sizeof(PgStat_MsgArchiver)) == 0)
+		return;
+
+	/*
+	 * Prepare and send the message
+	 */
+	pgstat_setheader(&ArchiverStats.m_hdr, PGSTAT_MTYPE_ARCHIVER);
+	pgstat_send(&ArchiverStats, sizeof(ArchiverStats));
+
+	/*
+	 * Clear out the statistics buffer, so it can be re-used.
+	 */
+	MemSet(&ArchiverStats, 0, sizeof(ArchiverStats));
+}
+
+
+/* ----------
  * pgstat_send_bgwriter() -
  *
  *		Send bgwriter statistics to the collector
@@ -3276,6 +3342,10 @@ PgstatCollectorMain(int argc, char *argv[])
 					pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len);
 					break;
 
+				case PGSTAT_MTYPE_ARCHIVER:
+					pgstat_recv_archiver((PgStat_MsgArchiver *) &msg, len);
+					break;
+
 				case PGSTAT_MTYPE_BGWRITER:
 					pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
 					break;
@@ -3546,6 +3616,7 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
 	 * Set the timestamp of the stats file.
 	 */
 	globalStats.stats_timestamp = GetCurrentTimestamp();
+	archiverStats.stats_timestamp = globalStats.stats_timestamp;
 
 	/*
 	 * Write the file header --- currently just a format ID.
@@ -3561,6 +3632,12 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
 	(void) rc;					/* we'll check for error with ferror */
 
 	/*
+	 * Write archiver stats struct
+	 */
+	rc = fwrite(&archiverStats, sizeof(archiverStats), 1, fpout);
+	(void) rc;					/* we'll check for error with ferror */
+
+	/*
 	 * Walk through the database table.
 	 */
 	hash_seq_init(&hstat, pgStatDBHash);
@@ -3826,16 +3903,18 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
 						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
 
 	/*
-	 * Clear out global statistics so they start from zero in case we can't
-	 * load an existing statsfile.
+	 * Clear out global and archiver statistics so they start from zero
+	 * in case we can't load an existing statsfile.
 	 */
 	memset(&globalStats, 0, sizeof(globalStats));
+	memset(&archiverStats, 0, sizeof(archiverStats));
 
 	/*
 	 * Set the current timestamp (will be kept only in case we can't load an
 	 * existing statsfile).
 	 */
 	globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+	archiverStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
 
 	/*
 	 * Try to open the stats file. If it doesn't exist, the backends simply
@@ -3873,7 +3952,17 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
 	if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats))
 	{
 		ereport(pgStatRunningInCollector ? LOG : WARNING,
-				(errmsg("corrupted statistics file \"%s\"", statfile)));
+				(errmsg("corrupted statistics file (global) \"%s\"", statfile)));
+		goto done;
+	}
+
+	/*
+	 * Read archiver stats struct
+	 */
+	if (fread(&archiverStats, 1, sizeof(archiverStats), fpin) != sizeof(archiverStats))
+	{
+		ereport(pgStatRunningInCollector ? LOG : WARNING,
+				(errmsg("corrupted statistics file (archiver) \"%s\"", statfile)));
 		goto done;
 	}
 
@@ -4167,6 +4256,7 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
 {
 	PgStat_StatDBEntry dbentry;
 	PgStat_GlobalStats myGlobalStats;
+	PgStat_ArchiverStats myArchiverStats;
 	FILE	   *fpin;
 	int32		format_id;
 	const char *statfile = permanent ? PGSTAT_STAT_PERMANENT_FILENAME : pgstat_stat_filename;
@@ -4209,6 +4299,18 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
 		return false;
 	}
 
+	/*
+	 * Read archiver stats struct
+	 */
+	if (fread(&myArchiverStats, 1, sizeof(myArchiverStats),
+			  fpin) != sizeof(myArchiverStats))
+	{
+		ereport(pgStatRunningInCollector ? LOG : WARNING,
+				(errmsg("corrupted statistics file \"%s\"", statfile)));
+		FreeFile(fpin);
+		return false;
+	}
+
 	/* By default, we're going to return the timestamp of the global file. */
 	*ts = myGlobalStats.stats_timestamp;
 
@@ -4736,6 +4838,12 @@ pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
 		memset(&globalStats, 0, sizeof(globalStats));
 		globalStats.stat_reset_timestamp = GetCurrentTimestamp();
 	}
+	else if (msg->m_resettarget == RESET_ARCHIVER)
+	{
+		/* Reset the archiver statistics for the cluster. */
+		memset(&archiverStats, 0, sizeof(archiverStats));
+		archiverStats.stat_reset_timestamp = GetCurrentTimestamp();
+	}
 
 	/*
 	 * Presumably the sender of this message validated the target, don't
@@ -4867,6 +4975,33 @@ pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
 
 
 /* ----------
+ * pgstat_recv_archiver() -
+ *
+ *	Process a ARCHIVER message.
+ * ----------
+ */
+static void
+pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len)
+{
+	if (msg->m_failed)
+	{
+		/* Failed archival attempt */
+		++archiverStats.failed_attempts;
+		memcpy(archiverStats.last_failed_wal, msg->m_xlog,
+			sizeof(archiverStats.last_failed_wal));
+		archiverStats.last_failed_wal_timestamp = msg->m_timestamp;
+	}
+	else
+	{
+		/* Successful archival operation */
+		++archiverStats.archived_wals;
+		memcpy(archiverStats.last_archived_wal, msg->m_xlog,
+			sizeof(archiverStats.last_archived_wal));
+		archiverStats.last_archived_wal_timestamp = msg->m_timestamp;
+	}
+}
+
+/* ----------
  * pgstat_recv_bgwriter() -
  *
  *	Process a BGWRITER message.
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2957fc7..4c5eda9 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -87,6 +87,8 @@ extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blk_read_time(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS);
 
+extern Datum pg_stat_get_archiver(PG_FUNCTION_ARGS);
+
 extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS);
@@ -1712,3 +1714,72 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
 
 	PG_RETURN_VOID();
 }
+
+Datum
+pg_stat_get_archiver(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[7];
+	bool		nulls[7];
+	HeapTuple	tuple;
+	PgStat_ArchiverStats *archiver_stats;
+
+	/* 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(7, false);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "archived_wals",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "last_archived_wal",
+					   TEXTOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 3, "last_archived_wal_time",
+					   TIMESTAMPTZOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 4, "failed_attempts",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 5, "last_failed_wal",
+					   TEXTOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 6, "last_failed_wal_time",
+					   TIMESTAMPTZOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stats_reset",
+					   TIMESTAMPTZOID, -1, 0);
+
+	/* Get statistics about the archiver process */
+	archiver_stats = pgstat_fetch_archiver();
+
+	/* Fill values and NULLs */
+	values[0] = Int64GetDatum(archiver_stats->archived_wals);
+	if (archiver_stats->last_archived_wal == 0)
+		nulls[1] = true;
+	else
+		values[1] = CStringGetTextDatum(archiver_stats->last_archived_wal);
+
+	if (archiver_stats->last_archived_wal_timestamp == 0)
+		nulls[2] = true;
+	else
+		values[2] = TimestampTzGetDatum(archiver_stats->last_archived_wal_timestamp);
+
+	values[3] = Int64GetDatum(archiver_stats->failed_attempts);
+	if (archiver_stats->last_failed_wal == 0)
+		nulls[4] = true;
+	else
+		values[4] = CStringGetTextDatum(archiver_stats->last_failed_wal);
+
+	if (archiver_stats->last_failed_wal_timestamp == 0)
+		nulls[5] = true;
+	else
+		values[5] = TimestampTzGetDatum(archiver_stats->last_failed_wal_timestamp);
+
+	if (archiver_stats->stat_reset_timestamp == 0)
+		nulls[6] = true;
+	else
+		values[6] = TimestampTzGetDatum(archiver_stats->stat_reset_timestamp);
+
+	BlessTupleDesc(tupdesc); 
+
+	/* 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 05d7ba5..313ef80 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2700,6 +2700,8 @@ DATA(insert OID = 2844 (  pg_stat_get_db_blk_read_time	PGNSP PGUID 12 1 0 0 0 f
 DESCR("statistics: block read time, in msec");
 DATA(insert OID = 2845 (  pg_stat_get_db_blk_write_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 701 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blk_write_time _null_ _null_ _null_ ));
 DESCR("statistics: block write time, in msec");
+DATA(insert OID = 3195 (  pg_stat_get_archiver		PGNSP PGUID 12 1 0 0 0 f f f f f f s 0 0 2249 "" "{20,25,1184,20,25,1184,1184}" "{o,o,o,o,o,o,o}" "{archived_wals,last_archived_wal,last_archived_wal_time,failed_attempts,last_failed_wal,last_failed_wal_time,stats_reset}" _null_ pg_stat_get_archiver _null_ _null_ _null_ ));
+DESCR("statistics: information about WAL archiver");
 DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
 DESCR("statistics: number of timed checkpoints started by the bgwriter");
 DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index b79a0eb..a78f1a4 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -17,6 +17,7 @@
 #include "portability/instr_time.h"
 #include "utils/hsearch.h"
 #include "utils/relcache.h"
+#include "access/xlog_internal.h"
 
 
 /* Values for track_functions GUC variable --- order is significant! */
@@ -44,6 +45,7 @@ typedef enum StatMsgType
 	PGSTAT_MTYPE_AUTOVAC_START,
 	PGSTAT_MTYPE_VACUUM,
 	PGSTAT_MTYPE_ANALYZE,
+	PGSTAT_MTYPE_ARCHIVER,
 	PGSTAT_MTYPE_BGWRITER,
 	PGSTAT_MTYPE_FUNCSTAT,
 	PGSTAT_MTYPE_FUNCPURGE,
@@ -102,6 +104,7 @@ typedef struct PgStat_TableCounts
 /* Possible targets for resetting cluster-wide shared values */
 typedef enum PgStat_Shared_Reset_Target
 {
+	RESET_ARCHIVER,
 	RESET_BGWRITER
 } PgStat_Shared_Reset_Target;
 
@@ -355,6 +358,19 @@ typedef struct PgStat_MsgAnalyze
 
 
 /* ----------
+ * PgStat_MsgArchiver			Sent by the archiver to update statistics.
+ * ----------
+ */
+typedef struct PgStat_MsgArchiver
+{
+	PgStat_MsgHdr m_hdr;
+
+	bool m_failed; /* Failed attempt */
+	char m_xlog[MAXFNAMELEN +1];
+	TimestampTz m_timestamp;
+} PgStat_MsgArchiver;
+
+/* ----------
  * PgStat_MsgBgWriter			Sent by the bgwriter to update statistics.
  * ----------
  */
@@ -501,6 +517,7 @@ typedef union PgStat_Msg
 	PgStat_MsgAutovacStart msg_autovacuum;
 	PgStat_MsgVacuum msg_vacuum;
 	PgStat_MsgAnalyze msg_analyze;
+	PgStat_MsgArchiver msg_archiver;
 	PgStat_MsgBgWriter msg_bgwriter;
 	PgStat_MsgFuncstat msg_funcstat;
 	PgStat_MsgFuncpurge msg_funcpurge;
@@ -517,7 +534,7 @@ typedef union PgStat_Msg
  * ------------------------------------------------------------
  */
 
-#define PGSTAT_FILE_FORMAT_ID	0x01A5BC9B
+#define PGSTAT_FILE_FORMAT_ID	0x01A5BC9C
 
 /* ----------
  * PgStat_StatDBEntry			The collector's data per database
@@ -611,6 +628,21 @@ typedef struct PgStat_StatFuncEntry
 
 
 /*
+ * Archiver statistics kept in the stats collector
+ */
+typedef struct PgStat_ArchiverStats
+{
+	TimestampTz stats_timestamp;	/* time of stats file update */
+	PgStat_Counter archived_wals;
+	char last_archived_wal[MAXFNAMELEN +1]; /* file name of the last archived WAL */
+	TimestampTz last_archived_wal_timestamp;	/* time of last successful archival */
+	PgStat_Counter failed_attempts;
+	char last_failed_wal[MAXFNAMELEN +1]; /* file name of the last failed WAL */
+	TimestampTz last_failed_wal_timestamp;	/* time of last failed attempt of archival */
+	TimestampTz stat_reset_timestamp;
+} PgStat_ArchiverStats;
+
+/*
  * Global statistics kept in the stats collector
  */
 typedef struct PgStat_GlobalStats
@@ -731,6 +763,11 @@ extern char *pgstat_stat_tmpname;
 extern char *pgstat_stat_filename;
 
 /*
+ * Archiver statistics counters are updated directly by pgarch
+ */
+extern PgStat_MsgArchiver ArchiverStats;
+
+/*
  * BgWriter statistics counters are updated directly by bgwriter and bufmgr
  */
 extern PgStat_MsgBgWriter BgWriterStats;
@@ -862,6 +899,7 @@ extern void pgstat_twophase_postcommit(TransactionId xid, uint16 info,
 extern void pgstat_twophase_postabort(TransactionId xid, uint16 info,
 						  void *recdata, uint32 len);
 
+extern void pgstat_send_archiver(void);
 extern void pgstat_send_bgwriter(void);
 
 /* ----------
@@ -874,6 +912,7 @@ extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
 extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
 extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
 extern int	pgstat_fetch_stat_numbackends(void);
+extern PgStat_ArchiverStats *pgstat_fetch_archiver(void);
 extern PgStat_GlobalStats *pgstat_fetch_global(void);
 
 #endif   /* PGSTAT_H */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 9f089e3..bffcb30 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1640,6 +1640,14 @@ pg_stat_all_tables| SELECT c.oid AS relid,
    LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
   GROUP BY c.oid, n.nspname, c.relname;
+pg_stat_archiver| SELECT s.archived_wals,
+    s.last_archived_wal,
+    s.last_archived_wal_time,
+    s.failed_attempts,
+    s.last_failed_wal,
+    s.last_failed_wal_time,
+    s.stats_reset
+   FROM pg_stat_get_archiver() s(archived_wals, last_archived_wal, last_archived_wal_time, failed_attempts, last_failed_wal, last_failed_wal_time, stats_reset);
 pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
     pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
     pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
#13Michael Paquier
michael.paquier@gmail.com
In reply to: Gabriele Bartolini (#12)
1 attachment(s)
Re: [PATCH] Support for pg_stat_archiver view

On Thu, Jan 9, 2014 at 6:36 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Il 08/01/14 18:42, Simon Riggs ha scritto:

Not sure I see why it needs to be an SRF. It only returns one row.

Attached is version 4, which removes management of SRF stages.

I have been looking at v4 a bit more, and found a couple of small things:
- a warning in pgstatfuncs.c
- some typos and format fixing in the sgml docs
- some corrections in a couple of comments
- Fixed an error message related to pg_stat_reset_shared referring
only to bgwriter and not the new option archiver. Here is how the new
message looks:
=# select pg_stat_reset_shared('popo');
ERROR: 22023: unrecognized reset target: "popo"
HINT: Target must be "bgwriter" or "archiver"
A new version is attached containing those fixes. I played also with
the patch and pgbench, simulating some archive failures and successes
while running pgbench and the reports given by pg_stat_archiver were
correct, so I am marking this patch as "Ready for committer".

Regards,
--
Michael

Attachments:

20140123_pgstat_archiver_v5.patchtext/x-diff; charset=US-ASCII; name=20140123_pgstat_archiver_v5.patchDownload
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4ec6981..eb5131f 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -270,11 +270,19 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
      </row>
 
      <row>
+      <entry><structname>pg_stat_archiver</><indexterm><primary>pg_stat_archiver</primary></indexterm></entry>
+      <entry>One row only, showing statistics about the
+       WAL archiver process's activity. See
+       <xref linkend="pg-stat-archiver-view"> for details.
+      </entry>
+     </row>
+
+     <row>
       <entry><structname>pg_stat_bgwriter</><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
       <entry>One row only, showing statistics about the
        background writer process's activity. See
        <xref linkend="pg-stat-bgwriter-view"> for details.
-     </entry>
+      </entry>
      </row>
 
      <row>
@@ -648,6 +656,63 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
    </para>
   </note>
 
+  <table id="pg-stat-archiver-view" xreflabel="pg_stat_archiver">
+   <title><structname>pg_stat_archiver</structname> View</title>
+
+   <tgroup cols="3">
+    <thead>
+     <row>
+      <entry>Column</entry>
+      <entry>Type</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><structfield>archived_wals</></entry>
+      <entry><type>bigint</type></entry>
+      <entry>Number of WAL files that have been successfully archived</entry>
+     </row>
+     <row>
+      <entry><structfield>last_archived_wal</></entry>
+      <entry><type>text</type></entry>
+      <entry>Name of the last WAL file successfully archived</entry>
+     </row>
+     <row>
+      <entry><structfield>last_archived_wal_time</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time of the last successful archive operation</entry>
+     </row>
+     <row>
+      <entry><structfield>failed_attempts</></entry>
+      <entry><type>bigint</type></entry>
+      <entry>Number of failed attempts for archiving WAL files</entry>
+     </row>
+     <row>
+      <entry><structfield>last_failed_wal</></entry>
+      <entry><type>text</type></entry>
+      <entry>Name of the WAL file of the last failed archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>last_failed_wal_time</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time of the last failed archival operation</entry>
+     </row>
+     <row>
+      <entry><structfield>stats_reset</></entry>
+      <entry><type>timestamp with time zone</type></entry>
+      <entry>Time at which these statistics were last reset</entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
+  <para>
+   The <structname>pg_stat_archiver</structname> view will always have a
+   single row, containing data about the archiver process of the cluster.
+  </para>
+
   <table id="pg-stat-bgwriter-view" xreflabel="pg_stat_bgwriter">
    <title><structname>pg_stat_bgwriter</structname> View</title>
 
@@ -1613,6 +1678,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
        argument (requires superuser privileges).
        Calling <literal>pg_stat_reset_shared('bgwriter')</> will zero all the
        counters shown in the <structname>pg_stat_bgwriter</> view.
+       Calling <literal>pg_stat_reset_shared('archiver')</> will zero all the
+       counters shown in the <structname>pg_stat_archiver</> view.
       </entry>
      </row>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 043d118..c8a89fc 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -672,6 +672,17 @@ CREATE VIEW pg_stat_xact_user_functions AS
     WHERE P.prolang != 12  -- fast check to eliminate built-in functions
           AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
 
+CREATE VIEW pg_stat_archiver AS
+    SELECT
+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,
+        s.stats_reset
+    FROM pg_stat_get_archiver() s;
+
 CREATE VIEW pg_stat_bgwriter AS
     SELECT
         pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
diff --git a/src/backend/postmaster/pgarch.c b/src/backend/postmaster/pgarch.c
index 2fd7a01..69f8600 100644
--- a/src/backend/postmaster/pgarch.c
+++ b/src/backend/postmaster/pgarch.c
@@ -36,6 +36,7 @@
 #include "access/xlog_internal.h"
 #include "libpq/pqsignal.h"
 #include "miscadmin.h"
+#include "pgstat.h"
 #include "postmaster/fork_process.h"
 #include "postmaster/pgarch.h"
 #include "postmaster/postmaster.h"
@@ -46,6 +47,7 @@
 #include "storage/pmsignal.h"
 #include "utils/guc.h"
 #include "utils/ps_status.h"
+#include "utils/timestamp.h"
 
 
 /* ----------
@@ -647,6 +649,13 @@ pgarch_archiveXlog(char *xlog)
 		snprintf(activitymsg, sizeof(activitymsg), "failed on %s", xlog);
 		set_ps_display(activitymsg, false);
 
+		/* Send archiver statistics to the stats collector */
+		ArchiverStats.m_failed = true;
+		strncpy(ArchiverStats.m_xlog,
+			xlog, sizeof(ArchiverStats.m_xlog));
+		ArchiverStats.m_timestamp = GetCurrentTimestamp();
+		pgstat_send_archiver();
+
 		return false;
 	}
 	ereport(DEBUG1,
@@ -755,4 +764,10 @@ pgarch_archiveDone(char *xlog)
 				(errcode_for_file_access(),
 				 errmsg("could not rename file \"%s\" to \"%s\": %m",
 						rlogready, rlogdone)));
+	/* Send archiver statistics to the stats collector */
+	ArchiverStats.m_failed = false;
+	strncpy(ArchiverStats.m_xlog,
+		xlog, sizeof(ArchiverStats.m_xlog));
+	ArchiverStats.m_timestamp = GetCurrentTimestamp();
+	pgstat_send_archiver();
 }
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 1c3b481..8aa5127 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -123,6 +123,13 @@ char	   *pgstat_stat_filename = NULL;
 char	   *pgstat_stat_tmpname = NULL;
 
 /*
+ * Archiver global statistics counters (unused in other processes).
+ * Stored directly in a stats message structure so it can be sent
+ * without needing to copy things around.  We assume this inits to zeroes.
+ */
+PgStat_MsgArchiver ArchiverStats;
+
+/*
  * BgWriter global statistics counters (unused in other processes).
  * Stored directly in a stats message structure so it can be sent
  * without needing to copy things around.  We assume this inits to zeroes.
@@ -217,6 +224,12 @@ static PgBackendStatus *localBackendStatusTable = NULL;
 static int	localNumBackends = 0;
 
 /*
+ * Archiver statistics, kept in the stats collector.
+ * Contains statistics regarding the archiving process.
+ */
+static PgStat_ArchiverStats archiverStats;
+
+/*
  * Cluster wide statistics, kept in the stats collector.
  * Contains statistics that are not collected per database
  * or per table.
@@ -292,6 +305,7 @@ static void pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, in
 static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
 static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
 static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+static void pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len);
 static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
 static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
 static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
@@ -1257,13 +1271,15 @@ pgstat_reset_shared_counters(const char *target)
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 				 errmsg("must be superuser to reset statistics counters")));
 
-	if (strcmp(target, "bgwriter") == 0)
+	if (strcmp(target, "archiver") == 0)
+		msg.m_resettarget = RESET_ARCHIVER;
+	else if (strcmp(target, "bgwriter") == 0)
 		msg.m_resettarget = RESET_BGWRITER;
 	else
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("unrecognized reset target: \"%s\"", target),
-				 errhint("Target must be \"bgwriter\".")));
+				 errhint("Target must be \"bgwriter\" or \"archiver\".")));
 
 	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSHAREDCOUNTER);
 	pgstat_send(&msg, sizeof(msg));
@@ -2323,6 +2339,23 @@ pgstat_fetch_stat_numbackends(void)
 
 /*
  * ---------
+ * pgstat_fetch_stat_archiver() -
+ *
+ *	Support function for the SQL-callable pgstat* functions. Returns
+ *	a pointer to the archiver statistics struct.
+ * ---------
+ */
+PgStat_ArchiverStats *
+pgstat_fetch_stat_archiver(void)
+{
+	backend_read_statsfile();
+
+	return &archiverStats;
+}
+
+
+/*
+ * ---------
  * pgstat_fetch_global() -
  *
  *	Support function for the SQL-callable pgstat* functions. Returns
@@ -3036,6 +3069,39 @@ pgstat_send(void *msg, int len)
 }
 
 /* ----------
+ * pgstat_send_archiver() -
+ *
+ *		Send archiver statistics to the collector
+ * ----------
+ */
+void
+pgstat_send_archiver(void)
+{
+	/* We assume this initializes to zeroes */
+	static const PgStat_MsgArchiver all_zeroes;
+
+	/*
+	 * This function can be called even if nothing at all has happened. In
+	 * this case, avoid sending a completely empty message to the stats
+	 * collector.
+	 */
+	if (memcmp(&ArchiverStats, &all_zeroes, sizeof(PgStat_MsgArchiver)) == 0)
+		return;
+
+	/*
+	 * Prepare and send the message
+	 */
+	pgstat_setheader(&ArchiverStats.m_hdr, PGSTAT_MTYPE_ARCHIVER);
+	pgstat_send(&ArchiverStats, sizeof(ArchiverStats));
+
+	/*
+	 * Clear out the statistics buffer, so it can be re-used.
+	 */
+	MemSet(&ArchiverStats, 0, sizeof(ArchiverStats));
+}
+
+
+/* ----------
  * pgstat_send_bgwriter() -
  *
  *		Send bgwriter statistics to the collector
@@ -3278,6 +3344,10 @@ PgstatCollectorMain(int argc, char *argv[])
 					pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len);
 					break;
 
+				case PGSTAT_MTYPE_ARCHIVER:
+					pgstat_recv_archiver((PgStat_MsgArchiver *) &msg, len);
+					break;
+
 				case PGSTAT_MTYPE_BGWRITER:
 					pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
 					break;
@@ -3548,6 +3618,7 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
 	 * Set the timestamp of the stats file.
 	 */
 	globalStats.stats_timestamp = GetCurrentTimestamp();
+	archiverStats.stats_timestamp = globalStats.stats_timestamp;
 
 	/*
 	 * Write the file header --- currently just a format ID.
@@ -3563,6 +3634,12 @@ pgstat_write_statsfiles(bool permanent, bool allDbs)
 	(void) rc;					/* we'll check for error with ferror */
 
 	/*
+	 * Write archiver stats struct
+	 */
+	rc = fwrite(&archiverStats, sizeof(archiverStats), 1, fpout);
+	(void) rc;					/* we'll check for error with ferror */
+
+	/*
 	 * Walk through the database table.
 	 */
 	hash_seq_init(&hstat, pgStatDBHash);
@@ -3828,16 +3905,18 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
 						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
 
 	/*
-	 * Clear out global statistics so they start from zero in case we can't
-	 * load an existing statsfile.
+	 * Clear out global and archiver statistics so they start from zero
+	 * in case we can't load an existing statsfile.
 	 */
 	memset(&globalStats, 0, sizeof(globalStats));
+	memset(&archiverStats, 0, sizeof(archiverStats));
 
 	/*
 	 * Set the current timestamp (will be kept only in case we can't load an
 	 * existing statsfile).
 	 */
 	globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+	archiverStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
 
 	/*
 	 * Try to open the stats file. If it doesn't exist, the backends simply
@@ -3875,7 +3954,17 @@ pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
 	if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats))
 	{
 		ereport(pgStatRunningInCollector ? LOG : WARNING,
-				(errmsg("corrupted statistics file \"%s\"", statfile)));
+				(errmsg("corrupted statistics file (global) \"%s\"", statfile)));
+		goto done;
+	}
+
+	/*
+	 * Read archiver stats struct
+	 */
+	if (fread(&archiverStats, 1, sizeof(archiverStats), fpin) != sizeof(archiverStats))
+	{
+		ereport(pgStatRunningInCollector ? LOG : WARNING,
+				(errmsg("corrupted statistics file (archiver) \"%s\"", statfile)));
 		goto done;
 	}
 
@@ -4169,6 +4258,7 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
 {
 	PgStat_StatDBEntry dbentry;
 	PgStat_GlobalStats myGlobalStats;
+	PgStat_ArchiverStats myArchiverStats;
 	FILE	   *fpin;
 	int32		format_id;
 	const char *statfile = permanent ? PGSTAT_STAT_PERMANENT_FILENAME : pgstat_stat_filename;
@@ -4211,6 +4301,18 @@ pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
 		return false;
 	}
 
+	/*
+	 * Read archiver stats struct
+	 */
+	if (fread(&myArchiverStats, 1, sizeof(myArchiverStats),
+			  fpin) != sizeof(myArchiverStats))
+	{
+		ereport(pgStatRunningInCollector ? LOG : WARNING,
+				(errmsg("corrupted statistics file \"%s\"", statfile)));
+		FreeFile(fpin);
+		return false;
+	}
+
 	/* By default, we're going to return the timestamp of the global file. */
 	*ts = myGlobalStats.stats_timestamp;
 
@@ -4738,6 +4840,12 @@ pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
 		memset(&globalStats, 0, sizeof(globalStats));
 		globalStats.stat_reset_timestamp = GetCurrentTimestamp();
 	}
+	else if (msg->m_resettarget == RESET_ARCHIVER)
+	{
+		/* Reset the archiver statistics for the cluster. */
+		memset(&archiverStats, 0, sizeof(archiverStats));
+		archiverStats.stat_reset_timestamp = GetCurrentTimestamp();
+	}
 
 	/*
 	 * Presumably the sender of this message validated the target, don't
@@ -4868,6 +4976,33 @@ pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
 
 
 /* ----------
+ * pgstat_recv_archiver() -
+ *
+ *	Process a ARCHIVER message.
+ * ----------
+ */
+static void
+pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len)
+{
+	if (msg->m_failed)
+	{
+		/* Failed archival attempt */
+		++archiverStats.failed_attempts;
+		memcpy(archiverStats.last_failed_wal, msg->m_xlog,
+			sizeof(archiverStats.last_failed_wal));
+		archiverStats.last_failed_wal_timestamp = msg->m_timestamp;
+	}
+	else
+	{
+		/* Successful archival operation */
+		++archiverStats.archived_wals;
+		memcpy(archiverStats.last_archived_wal, msg->m_xlog,
+			sizeof(archiverStats.last_archived_wal));
+		archiverStats.last_archived_wal_timestamp = msg->m_timestamp;
+	}
+}
+
+/* ----------
  * pgstat_recv_bgwriter() -
  *
  *	Process a BGWRITER message.
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2b8f5ee..0e5cd09 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -87,6 +87,8 @@ extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blk_read_time(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS);
 
+extern Datum pg_stat_get_archiver(PG_FUNCTION_ARGS);
+
 extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS);
@@ -1712,3 +1714,70 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
 
 	PG_RETURN_VOID();
 }
+
+Datum
+pg_stat_get_archiver(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[7];
+	bool		nulls[7];
+	PgStat_ArchiverStats *archiver_stats;
+
+	/* 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(7, false);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "archived_wals",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "last_archived_wal",
+					   TEXTOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 3, "last_archived_wal_time",
+					   TIMESTAMPTZOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 4, "failed_attempts",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 5, "last_failed_wal",
+					   TEXTOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 6, "last_failed_wal_time",
+					   TIMESTAMPTZOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stats_reset",
+					   TIMESTAMPTZOID, -1, 0);
+
+	BlessTupleDesc(tupdesc);
+
+	/* Get statistics about the archiver process */
+	archiver_stats = pgstat_fetch_stat_archiver();
+
+	/* Fill values and NULLs */
+	values[0] = Int64GetDatum(archiver_stats->archived_wals);
+	if (archiver_stats->last_archived_wal == 0)
+		nulls[1] = true;
+	else
+		values[1] = CStringGetTextDatum(archiver_stats->last_archived_wal);
+
+	if (archiver_stats->last_archived_wal_timestamp == 0)
+		nulls[2] = true;
+	else
+		values[2] = TimestampTzGetDatum(archiver_stats->last_archived_wal_timestamp);
+
+	values[3] = Int64GetDatum(archiver_stats->failed_attempts);
+	if (archiver_stats->last_failed_wal == 0)
+		nulls[4] = true;
+	else
+		values[4] = CStringGetTextDatum(archiver_stats->last_failed_wal);
+
+	if (archiver_stats->last_failed_wal_timestamp == 0)
+		nulls[5] = true;
+	else
+		values[5] = TimestampTzGetDatum(archiver_stats->last_failed_wal_timestamp);
+
+	if (archiver_stats->stat_reset_timestamp == 0)
+		nulls[6] = true;
+	else
+		values[6] = TimestampTzGetDatum(archiver_stats->stat_reset_timestamp);
+
+	/* 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 ad9774c..e81ef2e 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2702,6 +2702,8 @@ DATA(insert OID = 2844 (  pg_stat_get_db_blk_read_time	PGNSP PGUID 12 1 0 0 0 f
 DESCR("statistics: block read time, in msec");
 DATA(insert OID = 2845 (  pg_stat_get_db_blk_write_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 701 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blk_write_time _null_ _null_ _null_ ));
 DESCR("statistics: block write time, in msec");
+DATA(insert OID = 3195 (  pg_stat_get_archiver		PGNSP PGUID 12 1 0 0 0 f f f f f f s 0 0 2249 "" "{20,25,1184,20,25,1184,1184}" "{o,o,o,o,o,o,o}" "{archived_wals,last_archived_wal,last_archived_wal_time,failed_attempts,last_failed_wal,last_failed_wal_time,stats_reset}" _null_ pg_stat_get_archiver _null_ _null_ _null_ ));
+DESCR("statistics: information about WAL archiver");
 DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
 DESCR("statistics: number of timed checkpoints started by the bgwriter");
 DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 0b458e5..d07abd2 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -17,6 +17,7 @@
 #include "portability/instr_time.h"
 #include "utils/hsearch.h"
 #include "utils/relcache.h"
+#include "access/xlog_internal.h"
 
 
 /* Values for track_functions GUC variable --- order is significant! */
@@ -44,6 +45,7 @@ typedef enum StatMsgType
 	PGSTAT_MTYPE_AUTOVAC_START,
 	PGSTAT_MTYPE_VACUUM,
 	PGSTAT_MTYPE_ANALYZE,
+	PGSTAT_MTYPE_ARCHIVER,
 	PGSTAT_MTYPE_BGWRITER,
 	PGSTAT_MTYPE_FUNCSTAT,
 	PGSTAT_MTYPE_FUNCPURGE,
@@ -102,6 +104,7 @@ typedef struct PgStat_TableCounts
 /* Possible targets for resetting cluster-wide shared values */
 typedef enum PgStat_Shared_Reset_Target
 {
+	RESET_ARCHIVER,
 	RESET_BGWRITER
 } PgStat_Shared_Reset_Target;
 
@@ -356,6 +359,18 @@ typedef struct PgStat_MsgAnalyze
 
 
 /* ----------
+ * PgStat_MsgArchiver			Sent by the archiver to update statistics.
+ * ----------
+ */
+typedef struct PgStat_MsgArchiver
+{
+	PgStat_MsgHdr	m_hdr;
+	bool			m_failed; /* Failed attempt */
+	char			m_xlog[MAXFNAMELEN];
+	TimestampTz		m_timestamp;
+} PgStat_MsgArchiver;
+
+/* ----------
  * PgStat_MsgBgWriter			Sent by the bgwriter to update statistics.
  * ----------
  */
@@ -502,6 +517,7 @@ typedef union PgStat_Msg
 	PgStat_MsgAutovacStart msg_autovacuum;
 	PgStat_MsgVacuum msg_vacuum;
 	PgStat_MsgAnalyze msg_analyze;
+	PgStat_MsgArchiver msg_archiver;
 	PgStat_MsgBgWriter msg_bgwriter;
 	PgStat_MsgFuncstat msg_funcstat;
 	PgStat_MsgFuncpurge msg_funcpurge;
@@ -518,7 +534,7 @@ typedef union PgStat_Msg
  * ------------------------------------------------------------
  */
 
-#define PGSTAT_FILE_FORMAT_ID	0x01A5BC9B
+#define PGSTAT_FILE_FORMAT_ID	0x01A5BC9C
 
 /* ----------
  * PgStat_StatDBEntry			The collector's data per database
@@ -612,6 +628,21 @@ typedef struct PgStat_StatFuncEntry
 
 
 /*
+ * Archiver statistics kept in the stats collector
+ */
+typedef struct PgStat_ArchiverStats
+{
+	TimestampTz stats_timestamp;	/* time of stats file update */
+	PgStat_Counter archived_wals;
+	char last_archived_wal[MAXFNAMELEN];	/* last WAL file archived */
+	TimestampTz last_archived_wal_timestamp;	/* last archival success */
+	PgStat_Counter failed_attempts;
+	char last_failed_wal[MAXFNAMELEN];	/* last WAL file involved in failure */
+	TimestampTz last_failed_wal_timestamp;	/* last archival failure */
+	TimestampTz stat_reset_timestamp;
+} PgStat_ArchiverStats;
+
+/*
  * Global statistics kept in the stats collector
  */
 typedef struct PgStat_GlobalStats
@@ -732,6 +763,11 @@ extern char *pgstat_stat_tmpname;
 extern char *pgstat_stat_filename;
 
 /*
+ * Archiver statistics counters are updated directly by pgarch
+ */
+extern PgStat_MsgArchiver ArchiverStats;
+
+/*
  * BgWriter statistics counters are updated directly by bgwriter and bufmgr
  */
 extern PgStat_MsgBgWriter BgWriterStats;
@@ -863,6 +899,7 @@ extern void pgstat_twophase_postcommit(TransactionId xid, uint16 info,
 extern void pgstat_twophase_postabort(TransactionId xid, uint16 info,
 						  void *recdata, uint32 len);
 
+extern void pgstat_send_archiver(void);
 extern void pgstat_send_bgwriter(void);
 
 /* ----------
@@ -875,6 +912,7 @@ extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
 extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
 extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
 extern int	pgstat_fetch_stat_numbackends(void);
+extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
 extern PgStat_GlobalStats *pgstat_fetch_global(void);
 
 #endif   /* PGSTAT_H */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 9f089e3..bffcb30 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1640,6 +1640,14 @@ pg_stat_all_tables| SELECT c.oid AS relid,
    LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
   GROUP BY c.oid, n.nspname, c.relname;
+pg_stat_archiver| SELECT s.archived_wals,
+    s.last_archived_wal,
+    s.last_archived_wal_time,
+    s.failed_attempts,
+    s.last_failed_wal,
+    s.last_failed_wal_time,
+    s.stats_reset
+   FROM pg_stat_get_archiver() s(archived_wals, last_archived_wal, last_archived_wal_time, failed_attempts, last_failed_wal, last_failed_wal_time, stats_reset);
 pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
     pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
     pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
#14Fujii Masao
masao.fujii@gmail.com
In reply to: Michael Paquier (#13)
1 attachment(s)
Re: [PATCH] Support for pg_stat_archiver view

On Thu, Jan 23, 2014 at 4:10 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Thu, Jan 9, 2014 at 6:36 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Il 08/01/14 18:42, Simon Riggs ha scritto:

Not sure I see why it needs to be an SRF. It only returns one row.

Attached is version 4, which removes management of SRF stages.

I have been looking at v4 a bit more, and found a couple of small things:
- a warning in pgstatfuncs.c
- some typos and format fixing in the sgml docs
- some corrections in a couple of comments
- Fixed an error message related to pg_stat_reset_shared referring
only to bgwriter and not the new option archiver. Here is how the new
message looks:
=# select pg_stat_reset_shared('popo');
ERROR: 22023: unrecognized reset target: "popo"
HINT: Target must be "bgwriter" or "archiver"
A new version is attached containing those fixes. I played also with
the patch and pgbench, simulating some archive failures and successes
while running pgbench and the reports given by pg_stat_archiver were
correct, so I am marking this patch as "Ready for committer".

I refactored the patch further.

* Remove ArchiverStats global variable to simplify pgarch.c.
* Remove stats_timestamp field from PgStat_ArchiverStats struct because
it's not required.

I have some review comments:

+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,

The column names of pg_stat_archiver look not consistent at least to me.
What about the followings?

archive_count
last_archived_wal
last_archived_time
fail_count
last_failed_wal
last_failed_time

I think that it's time to rename all the variables related to pg_stat_bgwriter.
For example, it's better to change PgStat_GlobalStats to PgStat_Bgwriter.
I think that it's okay to make this change as separate patch, though.

+    char last_archived_wal[MAXFNAMELEN];    /* last WAL file archived */
+    TimestampTz last_archived_wal_timestamp;    /* last archival success */
+    PgStat_Counter failed_attempts;
+    char last_failed_wal[MAXFNAMELEN];    /* last WAL file involved
in failure */

Some hackers don't like the increase of the size of the statsfile. In order to
reduce the size as much as possible, we should use the exact size of WAL file
here instead of MAXFNAMELEN?

Regards,

--
Fujii Masao

Attachments:

pgstat_archiver_v6.patchtext/x-diff; charset=US-ASCII; name=pgstat_archiver_v6.patchDownload
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
***************
*** 270,275 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 270,283 ----
       </row>
  
       <row>
+       <entry><structname>pg_stat_archiver</><indexterm><primary>pg_stat_archiver</primary></indexterm></entry>
+       <entry>One row only, showing statistics about the
+        WAL archiver process's activity. See
+        <xref linkend="pg-stat-archiver-view"> for details.
+       </entry>
+      </row>
+ 
+      <row>
        <entry><structname>pg_stat_bgwriter</><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
        <entry>One row only, showing statistics about the
         background writer process's activity. See
***************
*** 648,653 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 656,718 ----
     </para>
    </note>
  
+   <table id="pg-stat-archiver-view" xreflabel="pg_stat_archiver">
+    <title><structname>pg_stat_archiver</structname> View</title>
+ 
+    <tgroup cols="3">
+     <thead>
+      <row>
+       <entry>Column</entry>
+       <entry>Type</entry>
+       <entry>Description</entry>
+      </row>
+     </thead>
+ 
+     <tbody>
+      <row>
+       <entry><structfield>archived_wals</></entry>
+       <entry><type>bigint</type></entry>
+       <entry>Number of WAL files that have been successfully archived</entry>
+      </row>
+      <row>
+       <entry><structfield>last_archived_wal</></entry>
+       <entry><type>text</type></entry>
+       <entry>Name of the last WAL file successfully archived</entry>
+      </row>
+      <row>
+       <entry><structfield>last_archived_wal_time</></entry>
+       <entry><type>timestamp with time zone</type></entry>
+       <entry>Time of the last successful archive operation</entry>
+      </row>
+      <row>
+       <entry><structfield>failed_attempts</></entry>
+       <entry><type>bigint</type></entry>
+       <entry>Number of failed attempts for archiving WAL files</entry>
+      </row>
+      <row>
+       <entry><structfield>last_failed_wal</></entry>
+       <entry><type>text</type></entry>
+       <entry>Name of the WAL file of the last failed archival operation</entry>
+      </row>
+      <row>
+       <entry><structfield>last_failed_wal_time</></entry>
+       <entry><type>timestamp with time zone</type></entry>
+       <entry>Time of the last failed archival operation</entry>
+      </row>
+      <row>
+       <entry><structfield>stats_reset</></entry>
+       <entry><type>timestamp with time zone</type></entry>
+       <entry>Time at which these statistics were last reset</entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+ 
+   <para>
+    The <structname>pg_stat_archiver</structname> view will always have a
+    single row, containing data about the archiver process of the cluster.
+   </para>
+ 
    <table id="pg-stat-bgwriter-view" xreflabel="pg_stat_bgwriter">
     <title><structname>pg_stat_bgwriter</structname> View</title>
  
***************
*** 1613,1618 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 1678,1685 ----
         argument (requires superuser privileges).
         Calling <literal>pg_stat_reset_shared('bgwriter')</> will zero all the
         counters shown in the <structname>pg_stat_bgwriter</> view.
+        Calling <literal>pg_stat_reset_shared('archiver')</> will zero all the
+        counters shown in the <structname>pg_stat_archiver</> view.
        </entry>
       </row>
  
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***************
*** 672,677 **** CREATE VIEW pg_stat_xact_user_functions AS
--- 672,688 ----
      WHERE P.prolang != 12  -- fast check to eliminate built-in functions
            AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
  
+ CREATE VIEW pg_stat_archiver AS
+     SELECT
+         s.archived_wals,
+         s.last_archived_wal,
+         s.last_archived_wal_time,
+         s.failed_attempts,
+         s.last_failed_wal,
+         s.last_failed_wal_time,
+         s.stats_reset
+     FROM pg_stat_get_archiver() s;
+ 
  CREATE VIEW pg_stat_bgwriter AS
      SELECT
          pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
*** a/src/backend/postmaster/pgarch.c
--- b/src/backend/postmaster/pgarch.c
***************
*** 36,41 ****
--- 36,42 ----
  #include "access/xlog_internal.h"
  #include "libpq/pqsignal.h"
  #include "miscadmin.h"
+ #include "pgstat.h"
  #include "postmaster/fork_process.h"
  #include "postmaster/pgarch.h"
  #include "postmaster/postmaster.h"
***************
*** 496,505 **** pgarch_ArchiverCopyLoop(void)
--- 497,513 ----
  			{
  				/* successful */
  				pgarch_archiveDone(xlog);
+ 
+ 				/* Tell the collector about the WAL file that we successfully archived */
+ 				pgstat_send_archiver(xlog, false);
+ 
  				break;			/* out of inner retry loop */
  			}
  			else
  			{
+ 				/* Tell the collector about the WAL file that we failed to archive */
+ 				pgstat_send_archiver(xlog, true);
+ 
  				if (++failures >= NUM_ARCHIVE_RETRIES)
  				{
  					ereport(WARNING,
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
***************
*** 221,226 **** static int	localNumBackends = 0;
--- 221,227 ----
   * Contains statistics that are not collected per database
   * or per table.
   */
+ static PgStat_ArchiverStats archiverStats;
  static PgStat_GlobalStats globalStats;
  
  /* Write request info for each database */
***************
*** 292,297 **** static void pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, in
--- 293,299 ----
  static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
  static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
  static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+ static void pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len);
  static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
  static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
  static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
***************
*** 1257,1269 **** pgstat_reset_shared_counters(const char *target)
  				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
  				 errmsg("must be superuser to reset statistics counters")));
  
! 	if (strcmp(target, "bgwriter") == 0)
  		msg.m_resettarget = RESET_BGWRITER;
  	else
  		ereport(ERROR,
  				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
  				 errmsg("unrecognized reset target: \"%s\"", target),
! 				 errhint("Target must be \"bgwriter\".")));
  
  	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSHAREDCOUNTER);
  	pgstat_send(&msg, sizeof(msg));
--- 1259,1273 ----
  				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
  				 errmsg("must be superuser to reset statistics counters")));
  
! 	if (strcmp(target, "archiver") == 0)
! 		msg.m_resettarget = RESET_ARCHIVER;
! 	else if (strcmp(target, "bgwriter") == 0)
  		msg.m_resettarget = RESET_BGWRITER;
  	else
  		ereport(ERROR,
  				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
  				 errmsg("unrecognized reset target: \"%s\"", target),
! 				 errhint("Target must be \"archiver\" or \"bgwriter\".")));
  
  	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSHAREDCOUNTER);
  	pgstat_send(&msg, sizeof(msg));
***************
*** 2323,2328 **** pgstat_fetch_stat_numbackends(void)
--- 2327,2349 ----
  
  /*
   * ---------
+  * pgstat_fetch_stat_archiver() -
+  *
+  *	Support function for the SQL-callable pgstat* functions. Returns
+  *	a pointer to the archiver statistics struct.
+  * ---------
+  */
+ PgStat_ArchiverStats *
+ pgstat_fetch_stat_archiver(void)
+ {
+ 	backend_read_statsfile();
+ 
+ 	return &archiverStats;
+ }
+ 
+ 
+ /*
+  * ---------
   * pgstat_fetch_global() -
   *
   *	Support function for the SQL-callable pgstat* functions. Returns
***************
*** 3036,3041 **** pgstat_send(void *msg, int len)
--- 3057,3084 ----
  }
  
  /* ----------
+  * pgstat_send_archiver() -
+  *
+  *	Tell the collector about the WAL file that we successfully
+  *	archived or failed to archive.
+  * ----------
+  */
+ void
+ pgstat_send_archiver(const char *xlog, bool failed)
+ {
+ 	PgStat_MsgArchiver	msg;
+ 
+ 	/*
+ 	 * Prepare and send the message
+ 	 */
+ 	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_ARCHIVER);
+ 	msg.m_failed = failed;
+ 	strncpy(msg.m_xlog, xlog, sizeof(msg.m_xlog));
+ 	msg.m_timestamp = GetCurrentTimestamp();
+ 	pgstat_send(&msg, sizeof(msg));
+ }
+ 
+ /* ----------
   * pgstat_send_bgwriter() -
   *
   *		Send bgwriter statistics to the collector
***************
*** 3278,3283 **** PgstatCollectorMain(int argc, char *argv[])
--- 3321,3330 ----
  					pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len);
  					break;
  
+ 				case PGSTAT_MTYPE_ARCHIVER:
+ 					pgstat_recv_archiver((PgStat_MsgArchiver *) &msg, len);
+ 					break;
+ 
  				case PGSTAT_MTYPE_BGWRITER:
  					pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
  					break;
***************
*** 3563,3568 **** pgstat_write_statsfiles(bool permanent, bool allDbs)
--- 3610,3621 ----
  	(void) rc;					/* we'll check for error with ferror */
  
  	/*
+ 	 * Write archiver stats struct
+ 	 */
+ 	rc = fwrite(&archiverStats, sizeof(archiverStats), 1, fpout);
+ 	(void) rc;					/* we'll check for error with ferror */
+ 
+ 	/*
  	 * Walk through the database table.
  	 */
  	hash_seq_init(&hstat, pgStatDBHash);
***************
*** 3828,3843 **** pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
  						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
  
  	/*
! 	 * Clear out global statistics so they start from zero in case we can't
! 	 * load an existing statsfile.
  	 */
  	memset(&globalStats, 0, sizeof(globalStats));
  
  	/*
  	 * Set the current timestamp (will be kept only in case we can't load an
  	 * existing statsfile).
  	 */
  	globalStats.stat_reset_timestamp = GetCurrentTimestamp();
  
  	/*
  	 * Try to open the stats file. If it doesn't exist, the backends simply
--- 3881,3898 ----
  						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
  
  	/*
! 	 * Clear out global and archiver statistics so they start from zero
! 	 * in case we can't load an existing statsfile.
  	 */
  	memset(&globalStats, 0, sizeof(globalStats));
+ 	memset(&archiverStats, 0, sizeof(archiverStats));
  
  	/*
  	 * Set the current timestamp (will be kept only in case we can't load an
  	 * existing statsfile).
  	 */
  	globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+ 	archiverStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
  
  	/*
  	 * Try to open the stats file. If it doesn't exist, the backends simply
***************
*** 3875,3881 **** pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
  	if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats))
  	{
  		ereport(pgStatRunningInCollector ? LOG : WARNING,
! 				(errmsg("corrupted statistics file \"%s\"", statfile)));
  		goto done;
  	}
  
--- 3930,3946 ----
  	if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats))
  	{
  		ereport(pgStatRunningInCollector ? LOG : WARNING,
! 				(errmsg("corrupted statistics file (global) \"%s\"", statfile)));
! 		goto done;
! 	}
! 
! 	/*
! 	 * Read archiver stats struct
! 	 */
! 	if (fread(&archiverStats, 1, sizeof(archiverStats), fpin) != sizeof(archiverStats))
! 	{
! 		ereport(pgStatRunningInCollector ? LOG : WARNING,
! 				(errmsg("corrupted statistics file (archiver) \"%s\"", statfile)));
  		goto done;
  	}
  
***************
*** 4159,4165 **** done:
   *	stats_timestamp value.
   *
   *	- if there's no db stat entry (e.g. for a new or inactive database),
!  *	there's no stat_timestamp value, but also nothing to write so we return
   *	the timestamp of the global statfile.
   * ----------
   */
--- 4224,4230 ----
   *	stats_timestamp value.
   *
   *	- if there's no db stat entry (e.g. for a new or inactive database),
!  *	there's no stats_timestamp value, but also nothing to write so we return
   *	the timestamp of the global statfile.
   * ----------
   */
***************
*** 4169,4174 **** pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
--- 4234,4240 ----
  {
  	PgStat_StatDBEntry dbentry;
  	PgStat_GlobalStats myGlobalStats;
+ 	PgStat_ArchiverStats myArchiverStats;
  	FILE	   *fpin;
  	int32		format_id;
  	const char *statfile = permanent ? PGSTAT_STAT_PERMANENT_FILENAME : pgstat_stat_filename;
***************
*** 4211,4216 **** pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
--- 4277,4294 ----
  		return false;
  	}
  
+ 	/*
+ 	 * Read archiver stats struct
+ 	 */
+ 	if (fread(&myArchiverStats, 1, sizeof(myArchiverStats),
+ 			  fpin) != sizeof(myArchiverStats))
+ 	{
+ 		ereport(pgStatRunningInCollector ? LOG : WARNING,
+ 				(errmsg("corrupted statistics file \"%s\"", statfile)));
+ 		FreeFile(fpin);
+ 		return false;
+ 	}
+ 
  	/* By default, we're going to return the timestamp of the global file. */
  	*ts = myGlobalStats.stats_timestamp;
  
***************
*** 4738,4743 **** pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
--- 4816,4827 ----
  		memset(&globalStats, 0, sizeof(globalStats));
  		globalStats.stat_reset_timestamp = GetCurrentTimestamp();
  	}
+ 	else if (msg->m_resettarget == RESET_ARCHIVER)
+ 	{
+ 		/* Reset the archiver statistics for the cluster. */
+ 		memset(&archiverStats, 0, sizeof(archiverStats));
+ 		archiverStats.stat_reset_timestamp = GetCurrentTimestamp();
+ 	}
  
  	/*
  	 * Presumably the sender of this message validated the target, don't
***************
*** 4868,4873 **** pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
--- 4952,4984 ----
  
  
  /* ----------
+  * pgstat_recv_archiver() -
+  *
+  *	Process a ARCHIVER message.
+  * ----------
+  */
+ static void
+ pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len)
+ {
+ 	if (msg->m_failed)
+ 	{
+ 		/* Failed archival attempt */
+ 		++archiverStats.failed_attempts;
+ 		memcpy(archiverStats.last_failed_wal, msg->m_xlog,
+ 			sizeof(archiverStats.last_failed_wal));
+ 		archiverStats.last_failed_wal_timestamp = msg->m_timestamp;
+ 	}
+ 	else
+ 	{
+ 		/* Successful archival operation */
+ 		++archiverStats.archived_wals;
+ 		memcpy(archiverStats.last_archived_wal, msg->m_xlog,
+ 			sizeof(archiverStats.last_archived_wal));
+ 		archiverStats.last_archived_wal_timestamp = msg->m_timestamp;
+ 	}
+ }
+ 
+ /* ----------
   * pgstat_recv_bgwriter() -
   *
   *	Process a BGWRITER message.
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
***************
*** 87,92 **** extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
--- 87,94 ----
  extern Datum pg_stat_get_db_blk_read_time(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS);
  
+ extern Datum pg_stat_get_archiver(PG_FUNCTION_ARGS);
+ 
  extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS);
***************
*** 1712,1714 **** pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
--- 1714,1783 ----
  
  	PG_RETURN_VOID();
  }
+ 
+ Datum
+ pg_stat_get_archiver(PG_FUNCTION_ARGS)
+ {
+ 	TupleDesc	tupdesc;
+ 	Datum		values[7];
+ 	bool		nulls[7];
+ 	PgStat_ArchiverStats *archiver_stats;
+ 
+ 	/* 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(7, false);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "archived_wals",
+ 					   INT8OID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "last_archived_wal",
+ 					   TEXTOID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 3, "last_archived_wal_time",
+ 					   TIMESTAMPTZOID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 4, "failed_attempts",
+ 					   INT8OID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 5, "last_failed_wal",
+ 					   TEXTOID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 6, "last_failed_wal_time",
+ 					   TIMESTAMPTZOID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stats_reset",
+ 					   TIMESTAMPTZOID, -1, 0);
+ 
+ 	BlessTupleDesc(tupdesc);
+ 
+ 	/* Get statistics about the archiver process */
+ 	archiver_stats = pgstat_fetch_stat_archiver();
+ 
+ 	/* Fill values and NULLs */
+ 	values[0] = Int64GetDatum(archiver_stats->archived_wals);
+ 	if (archiver_stats->last_archived_wal == 0)
+ 		nulls[1] = true;
+ 	else
+ 		values[1] = CStringGetTextDatum(archiver_stats->last_archived_wal);
+ 
+ 	if (archiver_stats->last_archived_wal_timestamp == 0)
+ 		nulls[2] = true;
+ 	else
+ 		values[2] = TimestampTzGetDatum(archiver_stats->last_archived_wal_timestamp);
+ 
+ 	values[3] = Int64GetDatum(archiver_stats->failed_attempts);
+ 	if (archiver_stats->last_failed_wal == 0)
+ 		nulls[4] = true;
+ 	else
+ 		values[4] = CStringGetTextDatum(archiver_stats->last_failed_wal);
+ 
+ 	if (archiver_stats->last_failed_wal_timestamp == 0)
+ 		nulls[5] = true;
+ 	else
+ 		values[5] = TimestampTzGetDatum(archiver_stats->last_failed_wal_timestamp);
+ 
+ 	if (archiver_stats->stat_reset_timestamp == 0)
+ 		nulls[6] = true;
+ 	else
+ 		values[6] = TimestampTzGetDatum(archiver_stats->stat_reset_timestamp);
+ 
+ 	/* Returns the record as Datum */
+ 	PG_RETURN_DATUM(HeapTupleGetDatum(
+ 						heap_form_tuple(tupdesc, values, nulls)));
+ }
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2702,2707 **** DATA(insert OID = 2844 (  pg_stat_get_db_blk_read_time	PGNSP PGUID 12 1 0 0 0 f
--- 2702,2709 ----
  DESCR("statistics: block read time, in msec");
  DATA(insert OID = 2845 (  pg_stat_get_db_blk_write_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 701 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blk_write_time _null_ _null_ _null_ ));
  DESCR("statistics: block write time, in msec");
+ DATA(insert OID = 3195 (  pg_stat_get_archiver		PGNSP PGUID 12 1 0 0 0 f f f f f f s 0 0 2249 "" "{20,25,1184,20,25,1184,1184}" "{o,o,o,o,o,o,o}" "{archived_wals,last_archived_wal,last_archived_wal_time,failed_attempts,last_failed_wal,last_failed_wal_time,stats_reset}" _null_ pg_stat_get_archiver _null_ _null_ _null_ ));
+ DESCR("statistics: information about WAL archiver");
  DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
  DESCR("statistics: number of timed checkpoints started by the bgwriter");
  DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ ));
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
***************
*** 17,22 ****
--- 17,23 ----
  #include "portability/instr_time.h"
  #include "utils/hsearch.h"
  #include "utils/relcache.h"
+ #include "access/xlog_internal.h"
  
  
  /* Values for track_functions GUC variable --- order is significant! */
***************
*** 44,49 **** typedef enum StatMsgType
--- 45,51 ----
  	PGSTAT_MTYPE_AUTOVAC_START,
  	PGSTAT_MTYPE_VACUUM,
  	PGSTAT_MTYPE_ANALYZE,
+ 	PGSTAT_MTYPE_ARCHIVER,
  	PGSTAT_MTYPE_BGWRITER,
  	PGSTAT_MTYPE_FUNCSTAT,
  	PGSTAT_MTYPE_FUNCPURGE,
***************
*** 102,107 **** typedef struct PgStat_TableCounts
--- 104,110 ----
  /* Possible targets for resetting cluster-wide shared values */
  typedef enum PgStat_Shared_Reset_Target
  {
+ 	RESET_ARCHIVER,
  	RESET_BGWRITER
  } PgStat_Shared_Reset_Target;
  
***************
*** 356,361 **** typedef struct PgStat_MsgAnalyze
--- 359,376 ----
  
  
  /* ----------
+  * PgStat_MsgArchiver			Sent by the archiver to update statistics.
+  * ----------
+  */
+ typedef struct PgStat_MsgArchiver
+ {
+ 	PgStat_MsgHdr	m_hdr;
+ 	bool			m_failed; /* Failed attempt */
+ 	char			m_xlog[MAXFNAMELEN];
+ 	TimestampTz		m_timestamp;
+ } PgStat_MsgArchiver;
+ 
+ /* ----------
   * PgStat_MsgBgWriter			Sent by the bgwriter to update statistics.
   * ----------
   */
***************
*** 502,507 **** typedef union PgStat_Msg
--- 517,523 ----
  	PgStat_MsgAutovacStart msg_autovacuum;
  	PgStat_MsgVacuum msg_vacuum;
  	PgStat_MsgAnalyze msg_analyze;
+ 	PgStat_MsgArchiver msg_archiver;
  	PgStat_MsgBgWriter msg_bgwriter;
  	PgStat_MsgFuncstat msg_funcstat;
  	PgStat_MsgFuncpurge msg_funcpurge;
***************
*** 518,524 **** typedef union PgStat_Msg
   * ------------------------------------------------------------
   */
  
! #define PGSTAT_FILE_FORMAT_ID	0x01A5BC9B
  
  /* ----------
   * PgStat_StatDBEntry			The collector's data per database
--- 534,540 ----
   * ------------------------------------------------------------
   */
  
! #define PGSTAT_FILE_FORMAT_ID	0x01A5BC9C
  
  /* ----------
   * PgStat_StatDBEntry			The collector's data per database
***************
*** 612,617 **** typedef struct PgStat_StatFuncEntry
--- 628,647 ----
  
  
  /*
+  * Archiver statistics kept in the stats collector
+  */
+ typedef struct PgStat_ArchiverStats
+ {
+ 	PgStat_Counter archived_wals;
+ 	char last_archived_wal[MAXFNAMELEN];	/* last WAL file archived */
+ 	TimestampTz last_archived_wal_timestamp;	/* last archival success */
+ 	PgStat_Counter failed_attempts;
+ 	char last_failed_wal[MAXFNAMELEN];	/* last WAL file involved in failure */
+ 	TimestampTz last_failed_wal_timestamp;	/* last archival failure */
+ 	TimestampTz stat_reset_timestamp;
+ } PgStat_ArchiverStats;
+ 
+ /*
   * Global statistics kept in the stats collector
   */
  typedef struct PgStat_GlobalStats
***************
*** 863,868 **** extern void pgstat_twophase_postcommit(TransactionId xid, uint16 info,
--- 893,899 ----
  extern void pgstat_twophase_postabort(TransactionId xid, uint16 info,
  						  void *recdata, uint32 len);
  
+ extern void pgstat_send_archiver(const char *xlog, bool failed);
  extern void pgstat_send_bgwriter(void);
  
  /* ----------
***************
*** 875,880 **** extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
--- 906,912 ----
  extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
  extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
  extern int	pgstat_fetch_stat_numbackends(void);
+ extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
  extern PgStat_GlobalStats *pgstat_fetch_global(void);
  
  #endif   /* PGSTAT_H */
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
***************
*** 1640,1645 **** pg_stat_all_tables| SELECT c.oid AS relid,
--- 1640,1653 ----
     LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
    WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
    GROUP BY c.oid, n.nspname, c.relname;
+ pg_stat_archiver| SELECT s.archived_wals,
+     s.last_archived_wal,
+     s.last_archived_wal_time,
+     s.failed_attempts,
+     s.last_failed_wal,
+     s.last_failed_wal_time,
+     s.stats_reset
+    FROM pg_stat_get_archiver() s(archived_wals, last_archived_wal, last_archived_wal_time, failed_attempts, last_failed_wal, last_failed_wal_time, stats_reset);
  pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
      pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
      pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
#15Michael Paquier
michael.paquier@gmail.com
In reply to: Fujii Masao (#14)
Re: [PATCH] Support for pg_stat_archiver view

On Sat, Jan 25, 2014 at 5:41 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Thu, Jan 23, 2014 at 4:10 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Thu, Jan 9, 2014 at 6:36 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Il 08/01/14 18:42, Simon Riggs ha scritto:

Not sure I see why it needs to be an SRF. It only returns one row.

Attached is version 4, which removes management of SRF stages.

I have been looking at v4 a bit more, and found a couple of small things:
- a warning in pgstatfuncs.c
- some typos and format fixing in the sgml docs
- some corrections in a couple of comments
- Fixed an error message related to pg_stat_reset_shared referring
only to bgwriter and not the new option archiver. Here is how the new
message looks:
=# select pg_stat_reset_shared('popo');
ERROR: 22023: unrecognized reset target: "popo"
HINT: Target must be "bgwriter" or "archiver"
A new version is attached containing those fixes. I played also with
the patch and pgbench, simulating some archive failures and successes
while running pgbench and the reports given by pg_stat_archiver were
correct, so I am marking this patch as "Ready for committer".

I refactored the patch further.

* Remove ArchiverStats global variable to simplify pgarch.c.
* Remove stats_timestamp field from PgStat_ArchiverStats struct because
it's not required.

Thanks, pgstat_send_archiver is cleaner now.

I have some review comments:

+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,

The column names of pg_stat_archiver look not consistent at least to me.
What about the followings?

archive_count
last_archived_wal
last_archived_time
fail_count
last_failed_wal
last_failed_time

And what about archived_count and failed_count instead of respectively
archive_count and failed_count? The rest of the names are better now
indeed.

I think that it's time to rename all the variables related to pg_stat_bgwriter.
For example, it's better to change PgStat_GlobalStats to PgStat_Bgwriter.
I think that it's okay to make this change as separate patch, though.

Yep, this is definitely a different patch.

+    char last_archived_wal[MAXFNAMELEN];    /* last WAL file archived */
+    TimestampTz last_archived_wal_timestamp;    /* last archival success */
+    PgStat_Counter failed_attempts;
+    char last_failed_wal[MAXFNAMELEN];    /* last WAL file involved
in failure */
Some hackers don't like the increase of the size of the statsfile. In order to
reduce the size as much as possible, we should use the exact size of WAL file
here instead of MAXFNAMELEN?

The first versions of the patch used a more limited size length more
inline with what you say:
+#define MAX_XFN_CHARS 40
But this is inconsistent with xlog_internal.h.
Regards,
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#16Michael Paquier
michael.paquier@gmail.com
In reply to: Michael Paquier (#15)
1 attachment(s)
Re: [PATCH] Support for pg_stat_archiver view

On Sat, Jan 25, 2014 at 3:19 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Sat, Jan 25, 2014 at 5:41 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Thu, Jan 23, 2014 at 4:10 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Thu, Jan 9, 2014 at 6:36 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Il 08/01/14 18:42, Simon Riggs ha scritto:

Not sure I see why it needs to be an SRF. It only returns one row.

Attached is version 4, which removes management of SRF stages.

I have been looking at v4 a bit more, and found a couple of small things:
- a warning in pgstatfuncs.c
- some typos and format fixing in the sgml docs
- some corrections in a couple of comments
- Fixed an error message related to pg_stat_reset_shared referring
only to bgwriter and not the new option archiver. Here is how the new
message looks:
=# select pg_stat_reset_shared('popo');
ERROR: 22023: unrecognized reset target: "popo"
HINT: Target must be "bgwriter" or "archiver"
A new version is attached containing those fixes. I played also with
the patch and pgbench, simulating some archive failures and successes
while running pgbench and the reports given by pg_stat_archiver were
correct, so I am marking this patch as "Ready for committer".

I refactored the patch further.

* Remove ArchiverStats global variable to simplify pgarch.c.
* Remove stats_timestamp field from PgStat_ArchiverStats struct because
it's not required.

Thanks, pgstat_send_archiver is cleaner now.

I have some review comments:

+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,

The column names of pg_stat_archiver look not consistent at least to me.
What about the followings?

archive_count
last_archived_wal
last_archived_time
fail_count
last_failed_wal
last_failed_time

And what about archived_count and failed_count instead of respectively
archive_count and failed_count? The rest of the names are better now
indeed.

Please find attached an updated patch updated with the new column
names (in context diffs this time).
Regards,
--
Michael

Attachments:

pg_stat_archiver_v7.patchtext/plain; charset=US-ASCII; name=pg_stat_archiver_v7.patchDownload
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
***************
*** 270,275 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 270,283 ----
       </row>
  
       <row>
+       <entry><structname>pg_stat_archiver</><indexterm><primary>pg_stat_archiver</primary></indexterm></entry>
+       <entry>One row only, showing statistics about the
+        WAL archiver process's activity. See
+        <xref linkend="pg-stat-archiver-view"> for details.
+       </entry>
+      </row>
+ 
+      <row>
        <entry><structname>pg_stat_bgwriter</><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
        <entry>One row only, showing statistics about the
         background writer process's activity. See
***************
*** 648,653 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 656,718 ----
     </para>
    </note>
  
+   <table id="pg-stat-archiver-view" xreflabel="pg_stat_archiver">
+    <title><structname>pg_stat_archiver</structname> View</title>
+ 
+    <tgroup cols="3">
+     <thead>
+      <row>
+       <entry>Column</entry>
+       <entry>Type</entry>
+       <entry>Description</entry>
+      </row>
+     </thead>
+ 
+     <tbody>
+      <row>
+       <entry><structfield>archived_count</></entry>
+       <entry><type>bigint</type></entry>
+       <entry>Number of WAL files that have been successfully archived</entry>
+      </row>
+      <row>
+       <entry><structfield>last_archived_wal</></entry>
+       <entry><type>text</type></entry>
+       <entry>Name of the last WAL file successfully archived</entry>
+      </row>
+      <row>
+       <entry><structfield>last_archived_time</></entry>
+       <entry><type>timestamp with time zone</type></entry>
+       <entry>Time of the last successful archive operation</entry>
+      </row>
+      <row>
+       <entry><structfield>failed_count</></entry>
+       <entry><type>bigint</type></entry>
+       <entry>Number of failed attempts for archiving WAL files</entry>
+      </row>
+      <row>
+       <entry><structfield>last_failed_wal</></entry>
+       <entry><type>text</type></entry>
+       <entry>Name of the WAL file of the last failed archival operation</entry>
+      </row>
+      <row>
+       <entry><structfield>last_failed_time</></entry>
+       <entry><type>timestamp with time zone</type></entry>
+       <entry>Time of the last failed archival operation</entry>
+      </row>
+      <row>
+       <entry><structfield>stats_reset</></entry>
+       <entry><type>timestamp with time zone</type></entry>
+       <entry>Time at which these statistics were last reset</entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+ 
+   <para>
+    The <structname>pg_stat_archiver</structname> view will always have a
+    single row, containing data about the archiver process of the cluster.
+   </para>
+ 
    <table id="pg-stat-bgwriter-view" xreflabel="pg_stat_bgwriter">
     <title><structname>pg_stat_bgwriter</structname> View</title>
  
***************
*** 1613,1618 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 1678,1685 ----
         argument (requires superuser privileges).
         Calling <literal>pg_stat_reset_shared('bgwriter')</> will zero all the
         counters shown in the <structname>pg_stat_bgwriter</> view.
+        Calling <literal>pg_stat_reset_shared('archiver')</> will zero all the
+        counters shown in the <structname>pg_stat_archiver</> view.
        </entry>
       </row>
  
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***************
*** 672,677 **** CREATE VIEW pg_stat_xact_user_functions AS
--- 672,688 ----
      WHERE P.prolang != 12  -- fast check to eliminate built-in functions
            AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
  
+ CREATE VIEW pg_stat_archiver AS
+     SELECT
+         s.archived_count,
+         s.last_archived_wal,
+         s.last_archived_time,
+         s.failed_count,
+         s.last_failed_wal,
+         s.last_failed_time,
+         s.stats_reset
+     FROM pg_stat_get_archiver() s;
+ 
  CREATE VIEW pg_stat_bgwriter AS
      SELECT
          pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
*** a/src/backend/postmaster/pgarch.c
--- b/src/backend/postmaster/pgarch.c
***************
*** 36,41 ****
--- 36,42 ----
  #include "access/xlog_internal.h"
  #include "libpq/pqsignal.h"
  #include "miscadmin.h"
+ #include "pgstat.h"
  #include "postmaster/fork_process.h"
  #include "postmaster/pgarch.h"
  #include "postmaster/postmaster.h"
***************
*** 496,505 **** pgarch_ArchiverCopyLoop(void)
--- 497,513 ----
  			{
  				/* successful */
  				pgarch_archiveDone(xlog);
+ 
+ 				/* Tell the collector about the WAL file that we successfully archived */
+ 				pgstat_send_archiver(xlog, false);
+ 
  				break;			/* out of inner retry loop */
  			}
  			else
  			{
+ 				/* Tell the collector about the WAL file that we failed to archive */
+ 				pgstat_send_archiver(xlog, true);
+ 
  				if (++failures >= NUM_ARCHIVE_RETRIES)
  				{
  					ereport(WARNING,
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
***************
*** 221,226 **** static int	localNumBackends = 0;
--- 221,227 ----
   * Contains statistics that are not collected per database
   * or per table.
   */
+ static PgStat_ArchiverStats archiverStats;
  static PgStat_GlobalStats globalStats;
  
  /* Write request info for each database */
***************
*** 292,297 **** static void pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, in
--- 293,299 ----
  static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
  static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
  static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+ static void pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len);
  static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
  static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
  static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
***************
*** 1257,1269 **** pgstat_reset_shared_counters(const char *target)
  				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
  				 errmsg("must be superuser to reset statistics counters")));
  
! 	if (strcmp(target, "bgwriter") == 0)
  		msg.m_resettarget = RESET_BGWRITER;
  	else
  		ereport(ERROR,
  				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
  				 errmsg("unrecognized reset target: \"%s\"", target),
! 				 errhint("Target must be \"bgwriter\".")));
  
  	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSHAREDCOUNTER);
  	pgstat_send(&msg, sizeof(msg));
--- 1259,1273 ----
  				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
  				 errmsg("must be superuser to reset statistics counters")));
  
! 	if (strcmp(target, "archiver") == 0)
! 		msg.m_resettarget = RESET_ARCHIVER;
! 	else if (strcmp(target, "bgwriter") == 0)
  		msg.m_resettarget = RESET_BGWRITER;
  	else
  		ereport(ERROR,
  				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
  				 errmsg("unrecognized reset target: \"%s\"", target),
! 				 errhint("Target must be \"archiver\" or \"bgwriter\".")));
  
  	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSHAREDCOUNTER);
  	pgstat_send(&msg, sizeof(msg));
***************
*** 2323,2328 **** pgstat_fetch_stat_numbackends(void)
--- 2327,2349 ----
  
  /*
   * ---------
+  * pgstat_fetch_stat_archiver() -
+  *
+  *	Support function for the SQL-callable pgstat* functions. Returns
+  *	a pointer to the archiver statistics struct.
+  * ---------
+  */
+ PgStat_ArchiverStats *
+ pgstat_fetch_stat_archiver(void)
+ {
+ 	backend_read_statsfile();
+ 
+ 	return &archiverStats;
+ }
+ 
+ 
+ /*
+  * ---------
   * pgstat_fetch_global() -
   *
   *	Support function for the SQL-callable pgstat* functions. Returns
***************
*** 3036,3041 **** pgstat_send(void *msg, int len)
--- 3057,3084 ----
  }
  
  /* ----------
+  * pgstat_send_archiver() -
+  *
+  *	Tell the collector about the WAL file that we successfully
+  *	archived or failed to archive.
+  * ----------
+  */
+ void
+ pgstat_send_archiver(const char *xlog, bool failed)
+ {
+ 	PgStat_MsgArchiver	msg;
+ 
+ 	/*
+ 	 * Prepare and send the message
+ 	 */
+ 	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_ARCHIVER);
+ 	msg.m_failed = failed;
+ 	strncpy(msg.m_xlog, xlog, sizeof(msg.m_xlog));
+ 	msg.m_timestamp = GetCurrentTimestamp();
+ 	pgstat_send(&msg, sizeof(msg));
+ }
+ 
+ /* ----------
   * pgstat_send_bgwriter() -
   *
   *		Send bgwriter statistics to the collector
***************
*** 3278,3283 **** PgstatCollectorMain(int argc, char *argv[])
--- 3321,3330 ----
  					pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len);
  					break;
  
+ 				case PGSTAT_MTYPE_ARCHIVER:
+ 					pgstat_recv_archiver((PgStat_MsgArchiver *) &msg, len);
+ 					break;
+ 
  				case PGSTAT_MTYPE_BGWRITER:
  					pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
  					break;
***************
*** 3563,3568 **** pgstat_write_statsfiles(bool permanent, bool allDbs)
--- 3610,3621 ----
  	(void) rc;					/* we'll check for error with ferror */
  
  	/*
+ 	 * Write archiver stats struct
+ 	 */
+ 	rc = fwrite(&archiverStats, sizeof(archiverStats), 1, fpout);
+ 	(void) rc;					/* we'll check for error with ferror */
+ 
+ 	/*
  	 * Walk through the database table.
  	 */
  	hash_seq_init(&hstat, pgStatDBHash);
***************
*** 3828,3843 **** pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
  						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
  
  	/*
! 	 * Clear out global statistics so they start from zero in case we can't
! 	 * load an existing statsfile.
  	 */
  	memset(&globalStats, 0, sizeof(globalStats));
  
  	/*
  	 * Set the current timestamp (will be kept only in case we can't load an
  	 * existing statsfile).
  	 */
  	globalStats.stat_reset_timestamp = GetCurrentTimestamp();
  
  	/*
  	 * Try to open the stats file. If it doesn't exist, the backends simply
--- 3881,3898 ----
  						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
  
  	/*
! 	 * Clear out global and archiver statistics so they start from zero
! 	 * in case we can't load an existing statsfile.
  	 */
  	memset(&globalStats, 0, sizeof(globalStats));
+ 	memset(&archiverStats, 0, sizeof(archiverStats));
  
  	/*
  	 * Set the current timestamp (will be kept only in case we can't load an
  	 * existing statsfile).
  	 */
  	globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+ 	archiverStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
  
  	/*
  	 * Try to open the stats file. If it doesn't exist, the backends simply
***************
*** 3875,3881 **** pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
  	if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats))
  	{
  		ereport(pgStatRunningInCollector ? LOG : WARNING,
! 				(errmsg("corrupted statistics file \"%s\"", statfile)));
  		goto done;
  	}
  
--- 3930,3946 ----
  	if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats))
  	{
  		ereport(pgStatRunningInCollector ? LOG : WARNING,
! 				(errmsg("corrupted statistics file (global) \"%s\"", statfile)));
! 		goto done;
! 	}
! 
! 	/*
! 	 * Read archiver stats struct
! 	 */
! 	if (fread(&archiverStats, 1, sizeof(archiverStats), fpin) != sizeof(archiverStats))
! 	{
! 		ereport(pgStatRunningInCollector ? LOG : WARNING,
! 				(errmsg("corrupted statistics file (archiver) \"%s\"", statfile)));
  		goto done;
  	}
  
***************
*** 4159,4165 **** done:
   *	stats_timestamp value.
   *
   *	- if there's no db stat entry (e.g. for a new or inactive database),
!  *	there's no stat_timestamp value, but also nothing to write so we return
   *	the timestamp of the global statfile.
   * ----------
   */
--- 4224,4230 ----
   *	stats_timestamp value.
   *
   *	- if there's no db stat entry (e.g. for a new or inactive database),
!  *	there's no stats_timestamp value, but also nothing to write so we return
   *	the timestamp of the global statfile.
   * ----------
   */
***************
*** 4169,4174 **** pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
--- 4234,4240 ----
  {
  	PgStat_StatDBEntry dbentry;
  	PgStat_GlobalStats myGlobalStats;
+ 	PgStat_ArchiverStats myArchiverStats;
  	FILE	   *fpin;
  	int32		format_id;
  	const char *statfile = permanent ? PGSTAT_STAT_PERMANENT_FILENAME : pgstat_stat_filename;
***************
*** 4211,4216 **** pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
--- 4277,4294 ----
  		return false;
  	}
  
+ 	/*
+ 	 * Read archiver stats struct
+ 	 */
+ 	if (fread(&myArchiverStats, 1, sizeof(myArchiverStats),
+ 			  fpin) != sizeof(myArchiverStats))
+ 	{
+ 		ereport(pgStatRunningInCollector ? LOG : WARNING,
+ 				(errmsg("corrupted statistics file \"%s\"", statfile)));
+ 		FreeFile(fpin);
+ 		return false;
+ 	}
+ 
  	/* By default, we're going to return the timestamp of the global file. */
  	*ts = myGlobalStats.stats_timestamp;
  
***************
*** 4738,4743 **** pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
--- 4816,4827 ----
  		memset(&globalStats, 0, sizeof(globalStats));
  		globalStats.stat_reset_timestamp = GetCurrentTimestamp();
  	}
+ 	else if (msg->m_resettarget == RESET_ARCHIVER)
+ 	{
+ 		/* Reset the archiver statistics for the cluster. */
+ 		memset(&archiverStats, 0, sizeof(archiverStats));
+ 		archiverStats.stat_reset_timestamp = GetCurrentTimestamp();
+ 	}
  
  	/*
  	 * Presumably the sender of this message validated the target, don't
***************
*** 4868,4873 **** pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
--- 4952,4984 ----
  
  
  /* ----------
+  * pgstat_recv_archiver() -
+  *
+  *	Process a ARCHIVER message.
+  * ----------
+  */
+ static void
+ pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len)
+ {
+ 	if (msg->m_failed)
+ 	{
+ 		/* Failed archival attempt */
+ 		++archiverStats.failed_count;
+ 		memcpy(archiverStats.last_failed_wal, msg->m_xlog,
+ 			sizeof(archiverStats.last_failed_wal));
+ 		archiverStats.last_failed_timestamp = msg->m_timestamp;
+ 	}
+ 	else
+ 	{
+ 		/* Successful archival operation */
+ 		++archiverStats.archived_count;
+ 		memcpy(archiverStats.last_archived_wal, msg->m_xlog,
+ 			sizeof(archiverStats.last_archived_wal));
+ 		archiverStats.last_archived_timestamp = msg->m_timestamp;
+ 	}
+ }
+ 
+ /* ----------
   * pgstat_recv_bgwriter() -
   *
   *	Process a BGWRITER message.
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
***************
*** 87,92 **** extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
--- 87,94 ----
  extern Datum pg_stat_get_db_blk_read_time(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS);
  
+ extern Datum pg_stat_get_archiver(PG_FUNCTION_ARGS);
+ 
  extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS);
***************
*** 1712,1714 **** pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
--- 1714,1783 ----
  
  	PG_RETURN_VOID();
  }
+ 
+ Datum
+ pg_stat_get_archiver(PG_FUNCTION_ARGS)
+ {
+ 	TupleDesc	tupdesc;
+ 	Datum		values[7];
+ 	bool		nulls[7];
+ 	PgStat_ArchiverStats *archiver_stats;
+ 
+ 	/* 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(7, false);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "archived_count",
+ 					   INT8OID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 2, "last_archived_wal",
+ 					   TEXTOID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 3, "last_archived_time",
+ 					   TIMESTAMPTZOID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 4, "failed_count",
+ 					   INT8OID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 5, "last_failed_wal",
+ 					   TEXTOID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 6, "last_failed_time",
+ 					   TIMESTAMPTZOID, -1, 0);
+ 	TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stats_reset",
+ 					   TIMESTAMPTZOID, -1, 0);
+ 
+ 	BlessTupleDesc(tupdesc);
+ 
+ 	/* Get statistics about the archiver process */
+ 	archiver_stats = pgstat_fetch_stat_archiver();
+ 
+ 	/* Fill values and NULLs */
+ 	values[0] = Int64GetDatum(archiver_stats->archived_count);
+ 	if (archiver_stats->last_archived_wal == 0)
+ 		nulls[1] = true;
+ 	else
+ 		values[1] = CStringGetTextDatum(archiver_stats->last_archived_wal);
+ 
+ 	if (archiver_stats->last_archived_timestamp == 0)
+ 		nulls[2] = true;
+ 	else
+ 		values[2] = TimestampTzGetDatum(archiver_stats->last_archived_timestamp);
+ 
+ 	values[3] = Int64GetDatum(archiver_stats->failed_count);
+ 	if (archiver_stats->last_failed_wal == 0)
+ 		nulls[4] = true;
+ 	else
+ 		values[4] = CStringGetTextDatum(archiver_stats->last_failed_wal);
+ 
+ 	if (archiver_stats->last_failed_timestamp == 0)
+ 		nulls[5] = true;
+ 	else
+ 		values[5] = TimestampTzGetDatum(archiver_stats->last_failed_timestamp);
+ 
+ 	if (archiver_stats->stat_reset_timestamp == 0)
+ 		nulls[6] = true;
+ 	else
+ 		values[6] = TimestampTzGetDatum(archiver_stats->stat_reset_timestamp);
+ 
+ 	/* Returns the record as Datum */
+ 	PG_RETURN_DATUM(HeapTupleGetDatum(
+ 						heap_form_tuple(tupdesc, values, nulls)));
+ }
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2702,2707 **** DATA(insert OID = 2844 (  pg_stat_get_db_blk_read_time	PGNSP PGUID 12 1 0 0 0 f
--- 2702,2709 ----
  DESCR("statistics: block read time, in msec");
  DATA(insert OID = 2845 (  pg_stat_get_db_blk_write_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 701 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blk_write_time _null_ _null_ _null_ ));
  DESCR("statistics: block write time, in msec");
+ DATA(insert OID = 3195 (  pg_stat_get_archiver		PGNSP PGUID 12 1 0 0 0 f f f f f f s 0 0 2249 "" "{20,25,1184,20,25,1184,1184}" "{o,o,o,o,o,o,o}" "{archived_count,last_archived_wal,last_archived_time,failed_count,last_failed_wal,last_failed_time,stats_reset}" _null_ pg_stat_get_archiver _null_ _null_ _null_ ));
+ DESCR("statistics: information about WAL archiver");
  DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
  DESCR("statistics: number of timed checkpoints started by the bgwriter");
  DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ ));
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
***************
*** 17,22 ****
--- 17,23 ----
  #include "portability/instr_time.h"
  #include "utils/hsearch.h"
  #include "utils/relcache.h"
+ #include "access/xlog_internal.h"
  
  
  /* Values for track_functions GUC variable --- order is significant! */
***************
*** 44,49 **** typedef enum StatMsgType
--- 45,51 ----
  	PGSTAT_MTYPE_AUTOVAC_START,
  	PGSTAT_MTYPE_VACUUM,
  	PGSTAT_MTYPE_ANALYZE,
+ 	PGSTAT_MTYPE_ARCHIVER,
  	PGSTAT_MTYPE_BGWRITER,
  	PGSTAT_MTYPE_FUNCSTAT,
  	PGSTAT_MTYPE_FUNCPURGE,
***************
*** 102,107 **** typedef struct PgStat_TableCounts
--- 104,110 ----
  /* Possible targets for resetting cluster-wide shared values */
  typedef enum PgStat_Shared_Reset_Target
  {
+ 	RESET_ARCHIVER,
  	RESET_BGWRITER
  } PgStat_Shared_Reset_Target;
  
***************
*** 356,361 **** typedef struct PgStat_MsgAnalyze
--- 359,376 ----
  
  
  /* ----------
+  * PgStat_MsgArchiver			Sent by the archiver to update statistics.
+  * ----------
+  */
+ typedef struct PgStat_MsgArchiver
+ {
+ 	PgStat_MsgHdr	m_hdr;
+ 	bool			m_failed; /* Failed attempt */
+ 	char			m_xlog[MAXFNAMELEN];
+ 	TimestampTz		m_timestamp;
+ } PgStat_MsgArchiver;
+ 
+ /* ----------
   * PgStat_MsgBgWriter			Sent by the bgwriter to update statistics.
   * ----------
   */
***************
*** 502,507 **** typedef union PgStat_Msg
--- 517,523 ----
  	PgStat_MsgAutovacStart msg_autovacuum;
  	PgStat_MsgVacuum msg_vacuum;
  	PgStat_MsgAnalyze msg_analyze;
+ 	PgStat_MsgArchiver msg_archiver;
  	PgStat_MsgBgWriter msg_bgwriter;
  	PgStat_MsgFuncstat msg_funcstat;
  	PgStat_MsgFuncpurge msg_funcpurge;
***************
*** 518,524 **** typedef union PgStat_Msg
   * ------------------------------------------------------------
   */
  
! #define PGSTAT_FILE_FORMAT_ID	0x01A5BC9B
  
  /* ----------
   * PgStat_StatDBEntry			The collector's data per database
--- 534,540 ----
   * ------------------------------------------------------------
   */
  
! #define PGSTAT_FILE_FORMAT_ID	0x01A5BC9C
  
  /* ----------
   * PgStat_StatDBEntry			The collector's data per database
***************
*** 612,617 **** typedef struct PgStat_StatFuncEntry
--- 628,647 ----
  
  
  /*
+  * Archiver statistics kept in the stats collector
+  */
+ typedef struct PgStat_ArchiverStats
+ {
+ 	PgStat_Counter archived_count;		/* archival successes */
+ 	char last_archived_wal[MAXFNAMELEN];	/* last WAL file archived */
+ 	TimestampTz last_archived_timestamp;	/* last archival success time */
+ 	PgStat_Counter failed_count;		/* failed archival attempts */
+ 	char last_failed_wal[MAXFNAMELEN];	/* WAL file involved in last failure */
+ 	TimestampTz last_failed_timestamp;	/* last archival failure time */
+ 	TimestampTz stat_reset_timestamp;
+ } PgStat_ArchiverStats;
+ 
+ /*
   * Global statistics kept in the stats collector
   */
  typedef struct PgStat_GlobalStats
***************
*** 863,868 **** extern void pgstat_twophase_postcommit(TransactionId xid, uint16 info,
--- 893,899 ----
  extern void pgstat_twophase_postabort(TransactionId xid, uint16 info,
  						  void *recdata, uint32 len);
  
+ extern void pgstat_send_archiver(const char *xlog, bool failed);
  extern void pgstat_send_bgwriter(void);
  
  /* ----------
***************
*** 875,880 **** extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
--- 906,912 ----
  extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
  extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
  extern int	pgstat_fetch_stat_numbackends(void);
+ extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
  extern PgStat_GlobalStats *pgstat_fetch_global(void);
  
  #endif   /* PGSTAT_H */
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
***************
*** 1640,1645 **** pg_stat_all_tables| SELECT c.oid AS relid,
--- 1640,1653 ----
     LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
    WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
    GROUP BY c.oid, n.nspname, c.relname;
+ pg_stat_archiver| SELECT s.archived_wals,
+     s.last_archived_wal,
+     s.last_archived_wal_time,
+     s.failed_attempts,
+     s.last_failed_wal,
+     s.last_failed_wal_time,
+     s.stats_reset
+    FROM pg_stat_get_archiver() s(archived_wals, last_archived_wal, last_archived_wal_time, failed_attempts, last_failed_wal, last_failed_wal_time, stats_reset);
  pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
      pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
      pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
#17Fujii Masao
masao.fujii@gmail.com
In reply to: Michael Paquier (#15)
Re: [PATCH] Support for pg_stat_archiver view

On Sat, Jan 25, 2014 at 3:19 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Sat, Jan 25, 2014 at 5:41 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Thu, Jan 23, 2014 at 4:10 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Thu, Jan 9, 2014 at 6:36 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Il 08/01/14 18:42, Simon Riggs ha scritto:

Not sure I see why it needs to be an SRF. It only returns one row.

Attached is version 4, which removes management of SRF stages.

I have been looking at v4 a bit more, and found a couple of small things:
- a warning in pgstatfuncs.c
- some typos and format fixing in the sgml docs
- some corrections in a couple of comments
- Fixed an error message related to pg_stat_reset_shared referring
only to bgwriter and not the new option archiver. Here is how the new
message looks:
=# select pg_stat_reset_shared('popo');
ERROR: 22023: unrecognized reset target: "popo"
HINT: Target must be "bgwriter" or "archiver"
A new version is attached containing those fixes. I played also with
the patch and pgbench, simulating some archive failures and successes
while running pgbench and the reports given by pg_stat_archiver were
correct, so I am marking this patch as "Ready for committer".

I refactored the patch further.

* Remove ArchiverStats global variable to simplify pgarch.c.
* Remove stats_timestamp field from PgStat_ArchiverStats struct because
it's not required.

Thanks, pgstat_send_archiver is cleaner now.

I have some review comments:

+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,

The column names of pg_stat_archiver look not consistent at least to me.
What about the followings?

archive_count
last_archived_wal
last_archived_time
fail_count
last_failed_wal
last_failed_time

And what about archived_count and failed_count instead of respectively
archive_count and failed_count? The rest of the names are better now
indeed.

I think that it's time to rename all the variables related to pg_stat_bgwriter.
For example, it's better to change PgStat_GlobalStats to PgStat_Bgwriter.
I think that it's okay to make this change as separate patch, though.

Yep, this is definitely a different patch.

+    char last_archived_wal[MAXFNAMELEN];    /* last WAL file archived */
+    TimestampTz last_archived_wal_timestamp;    /* last archival success */
+    PgStat_Counter failed_attempts;
+    char last_failed_wal[MAXFNAMELEN];    /* last WAL file involved
in failure */
Some hackers don't like the increase of the size of the statsfile. In order to
reduce the size as much as possible, we should use the exact size of WAL file
here instead of MAXFNAMELEN?

The first versions of the patch used a more limited size length more
inline with what you say:
+#define MAX_XFN_CHARS 40
But this is inconsistent with xlog_internal.h.

Using MAX_XFN_CHARS instead of MAXFNAMELEN has a clear advantage
to reduce the size of the statistics file. Though I'm not sure how much this
change improve the performance of the statistics collector, basically
I'd like to
use MAX_XFN_CHARS here.

+ (errmsg("corrupted statistics file (global) \"%s\"",
statfile)));

I think that it's better to use the view name "pg_stat_bgwriter" instead of
internal name "global" here. The view name is easier-to-understand to a user.

+ (errmsg("corrupted statistics file (archiver)
\"%s\"", statfile)));

Same as above. What using "pg_stat_archiver" instead of just "archive"?

+    if (fread(&myArchiverStats, 1, sizeof(myArchiverStats),
+              fpin) != sizeof(myArchiverStats))
+    {
+        ereport(pgStatRunningInCollector ? LOG : WARNING,
+                (errmsg("corrupted statistics file \"%s\"", statfile)));

Same as above. Isn't it better to add something like
"pg_stat_archiver" even here?

Regards,

--
Fujii Masao

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#18Fujii Masao
masao.fujii@gmail.com
In reply to: Fujii Masao (#17)
Re: [PATCH] Support for pg_stat_archiver view

On Tue, Jan 28, 2014 at 3:42 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Sat, Jan 25, 2014 at 3:19 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Sat, Jan 25, 2014 at 5:41 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Thu, Jan 23, 2014 at 4:10 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Thu, Jan 9, 2014 at 6:36 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Il 08/01/14 18:42, Simon Riggs ha scritto:

Not sure I see why it needs to be an SRF. It only returns one row.

Attached is version 4, which removes management of SRF stages.

I have been looking at v4 a bit more, and found a couple of small things:
- a warning in pgstatfuncs.c
- some typos and format fixing in the sgml docs
- some corrections in a couple of comments
- Fixed an error message related to pg_stat_reset_shared referring
only to bgwriter and not the new option archiver. Here is how the new
message looks:
=# select pg_stat_reset_shared('popo');
ERROR: 22023: unrecognized reset target: "popo"
HINT: Target must be "bgwriter" or "archiver"
A new version is attached containing those fixes. I played also with
the patch and pgbench, simulating some archive failures and successes
while running pgbench and the reports given by pg_stat_archiver were
correct, so I am marking this patch as "Ready for committer".

I refactored the patch further.

* Remove ArchiverStats global variable to simplify pgarch.c.
* Remove stats_timestamp field from PgStat_ArchiverStats struct because
it's not required.

Thanks, pgstat_send_archiver is cleaner now.

I have some review comments:

+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,

The column names of pg_stat_archiver look not consistent at least to me.
What about the followings?

archive_count
last_archived_wal
last_archived_time
fail_count
last_failed_wal
last_failed_time

And what about archived_count and failed_count instead of respectively
archive_count and failed_count? The rest of the names are better now
indeed.

I think that it's time to rename all the variables related to pg_stat_bgwriter.
For example, it's better to change PgStat_GlobalStats to PgStat_Bgwriter.
I think that it's okay to make this change as separate patch, though.

Yep, this is definitely a different patch.

+    char last_archived_wal[MAXFNAMELEN];    /* last WAL file archived */
+    TimestampTz last_archived_wal_timestamp;    /* last archival success */
+    PgStat_Counter failed_attempts;
+    char last_failed_wal[MAXFNAMELEN];    /* last WAL file involved
in failure */
Some hackers don't like the increase of the size of the statsfile. In order to
reduce the size as much as possible, we should use the exact size of WAL file
here instead of MAXFNAMELEN?

The first versions of the patch used a more limited size length more
inline with what you say:
+#define MAX_XFN_CHARS 40
But this is inconsistent with xlog_internal.h.

Using MAX_XFN_CHARS instead of MAXFNAMELEN has a clear advantage
to reduce the size of the statistics file. Though I'm not sure how much this
change improve the performance of the statistics collector, basically
I'd like to
use MAX_XFN_CHARS here.

I changed the patch in this way, fixed some existing bugs (e.g.,
correct the column
names of pg_stat_archiver in rules.out), and then just committed it.

Regards,

--
Fujii Masao

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#19Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Fujii Masao (#18)
Re: [PATCH] Support for pg_stat_archiver view

Anybody knows about this patch?
/messages/by-id/508DFEC9.4020102@uptime.jp

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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

#20Michael Paquier
michael.paquier@gmail.com
In reply to: Fujii Masao (#18)
Re: [PATCH] Support for pg_stat_archiver view

On Wed, Jan 29, 2014 at 3:03 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Tue, Jan 28, 2014 at 3:42 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Sat, Jan 25, 2014 at 3:19 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Sat, Jan 25, 2014 at 5:41 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Thu, Jan 23, 2014 at 4:10 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Thu, Jan 9, 2014 at 6:36 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Il 08/01/14 18:42, Simon Riggs ha scritto:

Not sure I see why it needs to be an SRF. It only returns one row.

Attached is version 4, which removes management of SRF stages.

I have been looking at v4 a bit more, and found a couple of small things:
- a warning in pgstatfuncs.c
- some typos and format fixing in the sgml docs
- some corrections in a couple of comments
- Fixed an error message related to pg_stat_reset_shared referring
only to bgwriter and not the new option archiver. Here is how the new
message looks:
=# select pg_stat_reset_shared('popo');
ERROR: 22023: unrecognized reset target: "popo"
HINT: Target must be "bgwriter" or "archiver"
A new version is attached containing those fixes. I played also with
the patch and pgbench, simulating some archive failures and successes
while running pgbench and the reports given by pg_stat_archiver were
correct, so I am marking this patch as "Ready for committer".

I refactored the patch further.

* Remove ArchiverStats global variable to simplify pgarch.c.
* Remove stats_timestamp field from PgStat_ArchiverStats struct because
it's not required.

Thanks, pgstat_send_archiver is cleaner now.

I have some review comments:

+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,

The column names of pg_stat_archiver look not consistent at least to me.
What about the followings?

archive_count
last_archived_wal
last_archived_time
fail_count
last_failed_wal
last_failed_time

And what about archived_count and failed_count instead of respectively
archive_count and failed_count? The rest of the names are better now
indeed.

I think that it's time to rename all the variables related to pg_stat_bgwriter.
For example, it's better to change PgStat_GlobalStats to PgStat_Bgwriter.
I think that it's okay to make this change as separate patch, though.

Yep, this is definitely a different patch.

+    char last_archived_wal[MAXFNAMELEN];    /* last WAL file archived */
+    TimestampTz last_archived_wal_timestamp;    /* last archival success */
+    PgStat_Counter failed_attempts;
+    char last_failed_wal[MAXFNAMELEN];    /* last WAL file involved
in failure */
Some hackers don't like the increase of the size of the statsfile. In order to
reduce the size as much as possible, we should use the exact size of WAL file
here instead of MAXFNAMELEN?

The first versions of the patch used a more limited size length more
inline with what you say:
+#define MAX_XFN_CHARS 40
But this is inconsistent with xlog_internal.h.

Using MAX_XFN_CHARS instead of MAXFNAMELEN has a clear advantage
to reduce the size of the statistics file. Though I'm not sure how much this
change improve the performance of the statistics collector, basically
I'd like to
use MAX_XFN_CHARS here.

I changed the patch in this way, fixed some existing bugs (e.g.,
correct the column
names of pg_stat_archiver in rules.out), and then just committed it.

Thanks, after re-reading the code using MAX_XFN_CHARS makes sense. I
didn't look at the comments on top of pg_arch.c. Thanks as well for
fixing the regression output :)
Regards,
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#21Fujii Masao
masao.fujii@gmail.com
In reply to: Alvaro Herrera (#19)
Re: [PATCH] Support for pg_stat_archiver view

On Wed, Jan 29, 2014 at 3:07 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

Anybody knows about this patch?
/messages/by-id/508DFEC9.4020102@uptime.jp

Though I'm not sure whether Nagayasu is still working on that patch,
it's worth thinking to introduce that together with pg_stat_archiver.

Regards,

--
Fujii Masao

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#22Michael Paquier
michael.paquier@gmail.com
In reply to: Fujii Masao (#21)
Re: [PATCH] Support for pg_stat_archiver view

On Wed, Jan 29, 2014 at 10:55 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Wed, Jan 29, 2014 at 3:07 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

Anybody knows about this patch?
/messages/by-id/508DFEC9.4020102@uptime.jp

Though I'm not sure whether Nagayasu is still working on that patch,
it's worth thinking to introduce that together with pg_stat_archiver.

This patch uses globalStats to implement the new stat fields for
walwriter, I think that we should use a different structure for
clarity and to be in-line with what is done now with the archiver
part.

Btw, I agree that renaming the globalStats part to something more
appropriate related to bgwriter is a sane target for this CF, but
isn't it too late to late to consider this walwriter patch for this
release? It introduces a new feature as it tracks xlog dirty writes
and CF is already half way.
Regards,
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#23Michael Paquier
michael.paquier@gmail.com
In reply to: Fujii Masao (#14)
Re: [PATCH] Support for pg_stat_archiver view

On Sat, Jan 25, 2014 at 5:41 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

I think that it's time to rename all the variables related to pg_stat_bgwriter.
For example, it's better to change PgStat_GlobalStats to PgStat_Bgwriter.
I think that it's okay to make this change as separate patch, though.

Please find attached a simple patch renaming PgStat_GlobalStats to
PgStat_BgWriterStats. Its related variables and functions are renamed
as well and use the term "bgwriter" instead of "global". Comments are
updated as well. This patch also removes stats_timestamp from
PgStat_GlobalStats and uses instead a static variable in pgstat.c,
making all the structures dedicated to each component clearer.
Regards,
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#24Michael Paquier
michael.paquier@gmail.com
In reply to: Michael Paquier (#23)
1 attachment(s)
Re: [PATCH] Support for pg_stat_archiver view

On Wed, Jan 29, 2014 at 10:38 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Sat, Jan 25, 2014 at 5:41 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

I think that it's time to rename all the variables related to pg_stat_bgwriter.
For example, it's better to change PgStat_GlobalStats to PgStat_Bgwriter.
I think that it's okay to make this change as separate patch, though.

Please find attached a simple patch renaming PgStat_GlobalStats to
PgStat_BgWriterStats.

And of course I forgot the patch... Now attached.
--
Michael

Attachments:

bgwriter_stat_rename.patchtext/x-diff; charset=US-ASCII; name=bgwriter_stat_rename.patchDownload
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
***************
*** 221,228 **** static int	localNumBackends = 0;
   * Contains statistics that are not collected per database
   * or per table.
   */
  static PgStat_ArchiverStats archiverStats;
! static PgStat_GlobalStats globalStats;
  
  /* Write request info for each database */
  typedef struct DBWriteRequest
--- 221,229 ----
   * Contains statistics that are not collected per database
   * or per table.
   */
+ static TimestampTz global_stat_timestamp;
  static PgStat_ArchiverStats archiverStats;
! static PgStat_BgWriterStats bgwriterStats;
  
  /* Write request info for each database */
  typedef struct DBWriteRequest
***************
*** 2344,2361 **** pgstat_fetch_stat_archiver(void)
  
  /*
   * ---------
!  * pgstat_fetch_global() -
   *
   *	Support function for the SQL-callable pgstat* functions. Returns
!  *	a pointer to the global statistics struct.
   * ---------
   */
! PgStat_GlobalStats *
! pgstat_fetch_global(void)
  {
  	backend_read_statsfile();
  
! 	return &globalStats;
  }
  
  
--- 2345,2362 ----
  
  /*
   * ---------
!  * pgstat_fetch_stat_bgwriter() -
   *
   *	Support function for the SQL-callable pgstat* functions. Returns
!  *	a pointer to the bgwriter statistics struct.
   * ---------
   */
! PgStat_BgWriterStats *
! pgstat_fetch_stat_bgwriter(void)
  {
  	backend_read_statsfile();
  
! 	return &bgwriterStats;
  }
  
  
***************
*** 3594,3600 **** pgstat_write_statsfiles(bool permanent, bool allDbs)
  	/*
  	 * Set the timestamp of the stats file.
  	 */
! 	globalStats.stats_timestamp = GetCurrentTimestamp();
  
  	/*
  	 * Write the file header --- currently just a format ID.
--- 3595,3601 ----
  	/*
  	 * Set the timestamp of the stats file.
  	 */
! 	global_stat_timestamp = GetCurrentTimestamp();
  
  	/*
  	 * Write the file header --- currently just a format ID.
***************
*** 3604,3612 **** pgstat_write_statsfiles(bool permanent, bool allDbs)
  	(void) rc;					/* we'll check for error with ferror */
  
  	/*
! 	 * Write global stats struct
  	 */
! 	rc = fwrite(&globalStats, sizeof(globalStats), 1, fpout);
  	(void) rc;					/* we'll check for error with ferror */
  
  	/*
--- 3605,3613 ----
  	(void) rc;					/* we'll check for error with ferror */
  
  	/*
! 	 * Write bgwriter stats struct
  	 */
! 	rc = fwrite(&bgwriterStats, sizeof(bgwriterStats), 1, fpout);
  	(void) rc;					/* we'll check for error with ferror */
  
  	/*
***************
*** 3630,3636 **** pgstat_write_statsfiles(bool permanent, bool allDbs)
  		 */
  		if (allDbs || pgstat_db_requested(dbentry->databaseid))
  		{
! 			dbentry->stats_timestamp = globalStats.stats_timestamp;
  			pgstat_write_db_statsfile(dbentry, permanent);
  		}
  
--- 3631,3637 ----
  		 */
  		if (allDbs || pgstat_db_requested(dbentry->databaseid))
  		{
! 			dbentry->stats_timestamp = global_stat_timestamp;
  			pgstat_write_db_statsfile(dbentry, permanent);
  		}
  
***************
*** 3881,3898 **** pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
  						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
  
  	/*
! 	 * Clear out global and archiver statistics so they start from zero
  	 * in case we can't load an existing statsfile.
  	 */
! 	memset(&globalStats, 0, sizeof(globalStats));
  	memset(&archiverStats, 0, sizeof(archiverStats));
  
  	/*
  	 * Set the current timestamp (will be kept only in case we can't load an
  	 * existing statsfile).
  	 */
! 	globalStats.stat_reset_timestamp = GetCurrentTimestamp();
! 	archiverStats.stat_reset_timestamp = globalStats.stat_reset_timestamp;
  
  	/*
  	 * Try to open the stats file. If it doesn't exist, the backends simply
--- 3882,3899 ----
  						 HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT);
  
  	/*
! 	 * Clear out bgwriter and archiver statistics so they start from zero
  	 * in case we can't load an existing statsfile.
  	 */
! 	memset(&bgwriterStats, 0, sizeof(bgwriterStats));
  	memset(&archiverStats, 0, sizeof(archiverStats));
  
  	/*
  	 * Set the current timestamp (will be kept only in case we can't load an
  	 * existing statsfile).
  	 */
! 	bgwriterStats.stat_reset_timestamp = GetCurrentTimestamp();
! 	archiverStats.stat_reset_timestamp = bgwriterStats.stat_reset_timestamp;
  
  	/*
  	 * Try to open the stats file. If it doesn't exist, the backends simply
***************
*** 3925,3933 **** pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep)
  	}
  
  	/*
! 	 * Read global stats struct
  	 */
! 	if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats))
  	{
  		ereport(pgStatRunningInCollector ? LOG : WARNING,
  				(errmsg("corrupted statistics file \"%s\"", statfile)));
--- 3926,3934 ----
  	}
  
  	/*
! 	 * Read bgwriter stats struct
  	 */
! 	if (fread(&bgwriterStats, 1, sizeof(bgwriterStats), fpin) != sizeof(bgwriterStats))
  	{
  		ereport(pgStatRunningInCollector ? LOG : WARNING,
  				(errmsg("corrupted statistics file \"%s\"", statfile)));
***************
*** 4233,4239 **** pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
  								   TimestampTz *ts)
  {
  	PgStat_StatDBEntry dbentry;
! 	PgStat_GlobalStats myGlobalStats;
  	PgStat_ArchiverStats myArchiverStats;
  	FILE	   *fpin;
  	int32		format_id;
--- 4234,4240 ----
  								   TimestampTz *ts)
  {
  	PgStat_StatDBEntry dbentry;
! 	PgStat_BgWriterStats myBgWriterStats;
  	PgStat_ArchiverStats myArchiverStats;
  	FILE	   *fpin;
  	int32		format_id;
***************
*** 4266,4275 **** pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
  	}
  
  	/*
! 	 * Read global stats struct
  	 */
! 	if (fread(&myGlobalStats, 1, sizeof(myGlobalStats),
! 			  fpin) != sizeof(myGlobalStats))
  	{
  		ereport(pgStatRunningInCollector ? LOG : WARNING,
  				(errmsg("corrupted statistics file \"%s\"", statfile)));
--- 4267,4276 ----
  	}
  
  	/*
! 	 * Read bgwriter stats struct
  	 */
! 	if (fread(&myBgWriterStats, 1, sizeof(myBgWriterStats),
! 			  fpin) != sizeof(myBgWriterStats))
  	{
  		ereport(pgStatRunningInCollector ? LOG : WARNING,
  				(errmsg("corrupted statistics file \"%s\"", statfile)));
***************
*** 4290,4296 **** pgstat_read_db_statsfile_timestamp(Oid databaseid, bool permanent,
  	}
  
  	/* By default, we're going to return the timestamp of the global file. */
! 	*ts = myGlobalStats.stats_timestamp;
  
  	/*
  	 * We found an existing collector stats file.  Read it and look for a
--- 4291,4297 ----
  	}
  
  	/* By default, we're going to return the timestamp of the global file. */
! 	*ts = global_stat_timestamp;
  
  	/*
  	 * We found an existing collector stats file.  Read it and look for a
***************
*** 4812,4820 **** pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
  {
  	if (msg->m_resettarget == RESET_BGWRITER)
  	{
! 		/* Reset the global background writer statistics for the cluster. */
! 		memset(&globalStats, 0, sizeof(globalStats));
! 		globalStats.stat_reset_timestamp = GetCurrentTimestamp();
  	}
  	else if (msg->m_resettarget == RESET_ARCHIVER)
  	{
--- 4813,4821 ----
  {
  	if (msg->m_resettarget == RESET_BGWRITER)
  	{
! 		/* Reset the background writer statistics for the cluster. */
! 		memset(&bgwriterStats, 0, sizeof(bgwriterStats));
! 		bgwriterStats.stat_reset_timestamp = GetCurrentTimestamp();
  	}
  	else if (msg->m_resettarget == RESET_ARCHIVER)
  	{
***************
*** 4987,5002 **** pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len)
  static void
  pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
  {
! 	globalStats.timed_checkpoints += msg->m_timed_checkpoints;
! 	globalStats.requested_checkpoints += msg->m_requested_checkpoints;
! 	globalStats.checkpoint_write_time += msg->m_checkpoint_write_time;
! 	globalStats.checkpoint_sync_time += msg->m_checkpoint_sync_time;
! 	globalStats.buf_written_checkpoints += msg->m_buf_written_checkpoints;
! 	globalStats.buf_written_clean += msg->m_buf_written_clean;
! 	globalStats.maxwritten_clean += msg->m_maxwritten_clean;
! 	globalStats.buf_written_backend += msg->m_buf_written_backend;
! 	globalStats.buf_fsync_backend += msg->m_buf_fsync_backend;
! 	globalStats.buf_alloc += msg->m_buf_alloc;
  }
  
  /* ----------
--- 4988,5003 ----
  static void
  pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
  {
! 	bgwriterStats.timed_checkpoints += msg->m_timed_checkpoints;
! 	bgwriterStats.requested_checkpoints += msg->m_requested_checkpoints;
! 	bgwriterStats.checkpoint_write_time += msg->m_checkpoint_write_time;
! 	bgwriterStats.checkpoint_sync_time += msg->m_checkpoint_sync_time;
! 	bgwriterStats.buf_written_checkpoints += msg->m_buf_written_checkpoints;
! 	bgwriterStats.buf_written_clean += msg->m_buf_written_clean;
! 	bgwriterStats.maxwritten_clean += msg->m_maxwritten_clean;
! 	bgwriterStats.buf_written_backend += msg->m_buf_written_backend;
! 	bgwriterStats.buf_fsync_backend += msg->m_buf_fsync_backend;
! 	bgwriterStats.buf_alloc += msg->m_buf_alloc;
  }
  
  /* ----------
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
***************
*** 1412,1480 **** pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS)
  Datum
  pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_global()->timed_checkpoints);
  }
  
  Datum
  pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_global()->requested_checkpoints);
  }
  
  Datum
  pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_global()->buf_written_checkpoints);
  }
  
  Datum
  pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_global()->buf_written_clean);
  }
  
  Datum
  pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_global()->maxwritten_clean);
  }
  
  Datum
  pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS)
  {
  	/* time is already in msec, just convert to double for presentation */
! 	PG_RETURN_FLOAT8((double) pgstat_fetch_global()->checkpoint_write_time);
  }
  
  Datum
  pg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS)
  {
  	/* time is already in msec, just convert to double for presentation */
! 	PG_RETURN_FLOAT8((double) pgstat_fetch_global()->checkpoint_sync_time);
  }
  
  Datum
  pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_reset_timestamp);
  }
  
  Datum
  pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_global()->buf_written_backend);
  }
  
  Datum
  pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_global()->buf_fsync_backend);
  }
  
  Datum
  pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_global()->buf_alloc);
  }
  
  Datum
--- 1412,1480 ----
  Datum
  pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->timed_checkpoints);
  }
  
  Datum
  pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->requested_checkpoints);
  }
  
  Datum
  pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->buf_written_checkpoints);
  }
  
  Datum
  pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->buf_written_clean);
  }
  
  Datum
  pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->maxwritten_clean);
  }
  
  Datum
  pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS)
  {
  	/* time is already in msec, just convert to double for presentation */
! 	PG_RETURN_FLOAT8((double) pgstat_fetch_stat_bgwriter()->checkpoint_write_time);
  }
  
  Datum
  pg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS)
  {
  	/* time is already in msec, just convert to double for presentation */
! 	PG_RETURN_FLOAT8((double) pgstat_fetch_stat_bgwriter()->checkpoint_sync_time);
  }
  
  Datum
  pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_TIMESTAMPTZ(pgstat_fetch_stat_bgwriter()->stat_reset_timestamp);
  }
  
  Datum
  pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->buf_written_backend);
  }
  
  Datum
  pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->buf_fsync_backend);
  }
  
  Datum
  pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->buf_alloc);
  }
  
  Datum
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
***************
*** 642,652 **** typedef struct PgStat_ArchiverStats
  } PgStat_ArchiverStats;
  
  /*
!  * Global statistics kept in the stats collector
   */
! typedef struct PgStat_GlobalStats
  {
- 	TimestampTz stats_timestamp;	/* time of stats file update */
  	PgStat_Counter timed_checkpoints;
  	PgStat_Counter requested_checkpoints;
  	PgStat_Counter checkpoint_write_time;		/* times in milliseconds */
--- 642,651 ----
  } PgStat_ArchiverStats;
  
  /*
!  * BgWriter statistics kept in the stats collector
   */
! typedef struct PgStat_BgWriterStats
  {
  	PgStat_Counter timed_checkpoints;
  	PgStat_Counter requested_checkpoints;
  	PgStat_Counter checkpoint_write_time;		/* times in milliseconds */
***************
*** 658,664 **** typedef struct PgStat_GlobalStats
  	PgStat_Counter buf_fsync_backend;
  	PgStat_Counter buf_alloc;
  	TimestampTz stat_reset_timestamp;
! } PgStat_GlobalStats;
  
  
  /* ----------
--- 657,663 ----
  	PgStat_Counter buf_fsync_backend;
  	PgStat_Counter buf_alloc;
  	TimestampTz stat_reset_timestamp;
! } PgStat_BgWriterStats;
  
  
  /* ----------
***************
*** 907,912 **** extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
  extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
  extern int	pgstat_fetch_stat_numbackends(void);
  extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
! extern PgStat_GlobalStats *pgstat_fetch_global(void);
  
  #endif   /* PGSTAT_H */
--- 906,911 ----
  extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
  extern int	pgstat_fetch_stat_numbackends(void);
  extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
! extern PgStat_BgWriterStats *pgstat_fetch_stat_bgwriter(void);
  
  #endif   /* PGSTAT_H */
#25Fujii Masao
masao.fujii@gmail.com
In reply to: Michael Paquier (#22)
Re: [PATCH] Support for pg_stat_archiver view

On Wed, Jan 29, 2014 at 12:35 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Wed, Jan 29, 2014 at 10:55 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Wed, Jan 29, 2014 at 3:07 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

Anybody knows about this patch?
/messages/by-id/508DFEC9.4020102@uptime.jp

Though I'm not sure whether Nagayasu is still working on that patch,
it's worth thinking to introduce that together with pg_stat_archiver.

This patch uses globalStats to implement the new stat fields for
walwriter, I think that we should use a different structure for
clarity and to be in-line with what is done now with the archiver
part.

Is there clear reason why we should define separate structure for each
global stats view?
If we introduce something like pg_stat_walwriter later, it seems
better to use only one
structure, i.e., globalStats, for all global stats views. Which would
simplify the code and
can reduce the number of calls of fwrite()/fread().

Regards,

--
Fujii Masao

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#26Michael Paquier
michael.paquier@gmail.com
In reply to: Fujii Masao (#18)
Re: [PATCH] Support for pg_stat_archiver view

On Wed, Jan 29, 2014 at 3:03 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Tue, Jan 28, 2014 at 3:42 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Sat, Jan 25, 2014 at 3:19 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Sat, Jan 25, 2014 at 5:41 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Thu, Jan 23, 2014 at 4:10 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Thu, Jan 9, 2014 at 6:36 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Il 08/01/14 18:42, Simon Riggs ha scritto:

Not sure I see why it needs to be an SRF. It only returns one row.

Attached is version 4, which removes management of SRF stages.

I have been looking at v4 a bit more, and found a couple of small things:
- a warning in pgstatfuncs.c
- some typos and format fixing in the sgml docs
- some corrections in a couple of comments
- Fixed an error message related to pg_stat_reset_shared referring
only to bgwriter and not the new option archiver. Here is how the new
message looks:
=# select pg_stat_reset_shared('popo');
ERROR: 22023: unrecognized reset target: "popo"
HINT: Target must be "bgwriter" or "archiver"
A new version is attached containing those fixes. I played also with
the patch and pgbench, simulating some archive failures and successes
while running pgbench and the reports given by pg_stat_archiver were
correct, so I am marking this patch as "Ready for committer".

I refactored the patch further.

* Remove ArchiverStats global variable to simplify pgarch.c.
* Remove stats_timestamp field from PgStat_ArchiverStats struct because
it's not required.

Thanks, pgstat_send_archiver is cleaner now.

I have some review comments:

+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,

The column names of pg_stat_archiver look not consistent at least to me.
What about the followings?

archive_count
last_archived_wal
last_archived_time
fail_count
last_failed_wal
last_failed_time

And what about archived_count and failed_count instead of respectively
archive_count and failed_count? The rest of the names are better now
indeed.

I think that it's time to rename all the variables related to pg_stat_bgwriter.
For example, it's better to change PgStat_GlobalStats to PgStat_Bgwriter.
I think that it's okay to make this change as separate patch, though.

Yep, this is definitely a different patch.

+    char last_archived_wal[MAXFNAMELEN];    /* last WAL file archived */
+    TimestampTz last_archived_wal_timestamp;    /* last archival success */
+    PgStat_Counter failed_attempts;
+    char last_failed_wal[MAXFNAMELEN];    /* last WAL file involved
in failure */
Some hackers don't like the increase of the size of the statsfile. In order to
reduce the size as much as possible, we should use the exact size of WAL file
here instead of MAXFNAMELEN?

The first versions of the patch used a more limited size length more
inline with what you say:
+#define MAX_XFN_CHARS 40
But this is inconsistent with xlog_internal.h.

Using MAX_XFN_CHARS instead of MAXFNAMELEN has a clear advantage
to reduce the size of the statistics file. Though I'm not sure how much this
change improve the performance of the statistics collector, basically
I'd like to
use MAX_XFN_CHARS here.

I changed the patch in this way, fixed some existing bugs (e.g.,
correct the column
names of pg_stat_archiver in rules.out), and then just committed it.

"Depesz" mentioned here that it would be interesting to have as well
in this view the size of archive queue:
http://www.depesz.com/2014/01/29/waiting-for-9-4-add-pg_stat_archiver-statistics-view/
And it looks indeed interesting to have. What do you think about
adding it as a TODO item?
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#27Fujii Masao
masao.fujii@gmail.com
In reply to: Michael Paquier (#26)
Re: [PATCH] Support for pg_stat_archiver view

On Sat, Feb 1, 2014 at 9:32 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Wed, Jan 29, 2014 at 3:03 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Tue, Jan 28, 2014 at 3:42 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Sat, Jan 25, 2014 at 3:19 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Sat, Jan 25, 2014 at 5:41 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Thu, Jan 23, 2014 at 4:10 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Thu, Jan 9, 2014 at 6:36 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Il 08/01/14 18:42, Simon Riggs ha scritto:

Not sure I see why it needs to be an SRF. It only returns one row.

Attached is version 4, which removes management of SRF stages.

I have been looking at v4 a bit more, and found a couple of small things:
- a warning in pgstatfuncs.c
- some typos and format fixing in the sgml docs
- some corrections in a couple of comments
- Fixed an error message related to pg_stat_reset_shared referring
only to bgwriter and not the new option archiver. Here is how the new
message looks:
=# select pg_stat_reset_shared('popo');
ERROR: 22023: unrecognized reset target: "popo"
HINT: Target must be "bgwriter" or "archiver"
A new version is attached containing those fixes. I played also with
the patch and pgbench, simulating some archive failures and successes
while running pgbench and the reports given by pg_stat_archiver were
correct, so I am marking this patch as "Ready for committer".

I refactored the patch further.

* Remove ArchiverStats global variable to simplify pgarch.c.
* Remove stats_timestamp field from PgStat_ArchiverStats struct because
it's not required.

Thanks, pgstat_send_archiver is cleaner now.

I have some review comments:

+        s.archived_wals,
+        s.last_archived_wal,
+        s.last_archived_wal_time,
+        s.failed_attempts,
+        s.last_failed_wal,
+        s.last_failed_wal_time,

The column names of pg_stat_archiver look not consistent at least to me.
What about the followings?

archive_count
last_archived_wal
last_archived_time
fail_count
last_failed_wal
last_failed_time

And what about archived_count and failed_count instead of respectively
archive_count and failed_count? The rest of the names are better now
indeed.

I think that it's time to rename all the variables related to pg_stat_bgwriter.
For example, it's better to change PgStat_GlobalStats to PgStat_Bgwriter.
I think that it's okay to make this change as separate patch, though.

Yep, this is definitely a different patch.

+    char last_archived_wal[MAXFNAMELEN];    /* last WAL file archived */
+    TimestampTz last_archived_wal_timestamp;    /* last archival success */
+    PgStat_Counter failed_attempts;
+    char last_failed_wal[MAXFNAMELEN];    /* last WAL file involved
in failure */
Some hackers don't like the increase of the size of the statsfile. In order to
reduce the size as much as possible, we should use the exact size of WAL file
here instead of MAXFNAMELEN?

The first versions of the patch used a more limited size length more
inline with what you say:
+#define MAX_XFN_CHARS 40
But this is inconsistent with xlog_internal.h.

Using MAX_XFN_CHARS instead of MAXFNAMELEN has a clear advantage
to reduce the size of the statistics file. Though I'm not sure how much this
change improve the performance of the statistics collector, basically
I'd like to
use MAX_XFN_CHARS here.

I changed the patch in this way, fixed some existing bugs (e.g.,
correct the column
names of pg_stat_archiver in rules.out), and then just committed it.

"Depesz" mentioned here that it would be interesting to have as well
in this view the size of archive queue:
http://www.depesz.com/2014/01/29/waiting-for-9-4-add-pg_stat_archiver-statistics-view/
And it looks indeed interesting to have. What do you think about
adding it as a TODO item?

I think that it's OK to add that as TODO item. There might be
the system that the speed of WAL archiving is slower than
that of WAL generation, at peak time. The DBA of that system
might want to monitor the size of archive queue.

We can implement this by just counting the files with .ready
extension in pg_xlog/archive_status directory. Or we can also
implement that by adding new counter field in pg_stat_archiver
structure, incrementing it whenever creating .ready file, and
decrementing it whenever changing .ready to .done.

Regards,

--
Fujii Masao

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#28Gabriele Bartolini
gabriele.bartolini@2ndQuadrant.it
In reply to: Fujii Masao (#27)
Re: [PATCH] Support for pg_stat_archiver view

Hi Michael and Fujii,

Il 01/02/14 17:46, Fujii Masao ha scritto:

I think that it's OK to add that as TODO item. There might be
the system that the speed of WAL archiving is slower than
that of WAL generation, at peak time. The DBA of that system
might want to monitor the size of archive queue.

I agree that it is an interesting thing to do. The reason I didn't
introduce it in the first place was that I did not want to make too many
changes in this first attempt.

We can implement this by just counting the files with .ready
extension in pg_xlog/archive_status directory. Or we can also
implement that by adding new counter field in pg_stat_archiver
structure, incrementing it whenever creating .ready file, and
decrementing it whenever changing .ready to .done.

I would love to give it a shot at the next opportunity.

Thanks,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#29Michael Paquier
michael.paquier@gmail.com
In reply to: Gabriele Bartolini (#28)
Re: [PATCH] Support for pg_stat_archiver view

On Sun, Feb 2, 2014 at 2:43 AM, Gabriele Bartolini
<gabriele.bartolini@2ndquadrant.it> wrote:

Hi Michael and Fujii,

Il 01/02/14 17:46, Fujii Masao ha scritto:

I think that it's OK to add that as TODO item. There might be
the system that the speed of WAL archiving is slower than
that of WAL generation, at peak time. The DBA of that system
might want to monitor the size of archive queue.

I agree that it is an interesting thing to do. The reason I didn't
introduce it in the first place was that I did not want to make too many
changes in this first attempt.

For the time being I have added an item in "Statistics Collector" about that.

We can implement this by just counting the files with .ready
extension in pg_xlog/archive_status directory. Or we can also
implement that by adding new counter field in pg_stat_archiver
structure, incrementing it whenever creating .ready file, and
decrementing it whenever changing .ready to .done.

I would love to give it a shot at the next opportunity.

Will be happy to look at it once you get something.
Regards,
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers