pg_retainxlog.diff

diff --git a/contrib/Makefile b/contrib/Makefile
index fcd7c1e..cd7306e 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -32,6 +32,7 @@ SUBDIRS = \
 		pg_archivecleanup \
 		pg_buffercache	\
 		pg_freespacemap \
+		pg_retainxlog	\
 		pg_standby	\
 		pg_stat_statements \
 		pg_test_fsync	\
diff --git a/contrib/pg_retainxlog/.gitignore b/contrib/pg_retainxlog/.gitignore
new file mode 100644
index 0000000..e14b1a5
--- /dev/null
+++ b/contrib/pg_retainxlog/.gitignore
@@ -0,0 +1 @@
+/pg_retainxlog
diff --git a/contrib/pg_retainxlog/Makefile b/contrib/pg_retainxlog/Makefile
new file mode 100644
index 0000000..bd12b1b
--- /dev/null
+++ b/contrib/pg_retainxlog/Makefile
@@ -0,0 +1,21 @@
+# contrib/pg_archivecleanup/Makefile
+
+PGFILEDESC = "pg_retainxlog - blocks xlog recycling when using streaming replicatoin"
+PGAPPICON = win32
+
+PROGRAM = pg_retainxlog
+OBJS	= pg_retainxlog.o
+
+PG_CPPFLAGS = -I$(libpq_srcdir)
+PG_LIBS = $(libpq_pgport)
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_retainxlog
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/pg_retainxlog/pg_retainxlog.c b/contrib/pg_retainxlog/pg_retainxlog.c
new file mode 100644
index 0000000..55ba1a2
--- /dev/null
+++ b/contrib/pg_retainxlog/pg_retainxlog.c
@@ -0,0 +1,222 @@
+/*
+ * contrib/pg_retainxlog/pg_retainxlog.c
+ *
+ * pg_retainxlog.c - check if a PostgreSQL xlog file is ready to be
+ *					 recycled using archive_command
+ *
+ * Author: Magnus Hagander <magnus@hagander.net>
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres_fe.h"
+#include <unistd.h>
+#include <getopt.h>
+
+#include <libpq-fe.h>
+
+/* prototypes */
+static void usage(char *prog) __attribute__((noreturn));
+
+/* commandline arguments */
+static char *appname = NULL;
+static char *appquery = NULL;
+static int sleeptime = 10;
+static int initialsleep = 0;
+
+
+static void
+usage(char *prog)
+{
+	printf("Usage: %s [options] <filename> <connectionstr> \n", prog);
+	printf("  -a, --appname    Application name to look for\n");
+	printf("  -q, --query      Custom query result to look for\n");
+	printf("  -s, --sleep      Sleep time between attempts (seconds, default=10)\n");
+	printf("  -i, --initialsleep\n"
+           "                   Sleep time before first attempt (seconds, default=0)\n");
+	printf("  --verbose        Verbose output\n");
+	printf("  --help           Show help\n");
+	exit(1);
+}
+
+int
+main(int argc, char *argv[])
+{
+	static struct option long_options[] = {
+		{"appname", required_argument, NULL, 'a'},
+		{"query", required_argument, NULL, 'q'},
+		{"sleep", required_argument, NULL, 's'},
+		{"initialsleep", required_argument, NULL, 'i'},
+		{"help", no_argument, NULL, '?'},
+		{"verbose", no_argument, NULL, 'v'},
+		{NULL, 0, NULL, 0}
+	};
+	int c;
+	int option_index;
+	int verbose = 0;
+	PGconn *conn;
+	PGresult *res;
+	char *connstr, *filename;
+	int firstloop = 1;
+
+	if (argc > 1)
+	{
+		if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
+			usage(argv[0]);
+	}
+
+	while ((c = getopt_long(argc, argv, "va:i:s:q:?", long_options, &option_index)) != -1)
+	{
+		switch (c)
+		{
+			case 'a':
+				appname = strdup(optarg);
+				break;
+			case 's':
+				sleeptime = atoi(optarg);
+				if ((sleeptime == 0 && strcmp(optarg, "0") != 0) || sleeptime < 0)
+				{
+					fprintf(stderr, "%s: sleep time must be given as a positive integer!\n", argv[0]);
+					exit(1);
+				}
+				break;
+			case 'i':
+				initialsleep = atoi(optarg);
+				if ((initialsleep == 0 && strcmp(optarg, "0") != 0) || initialsleep < 0)
+				{
+					fprintf(stderr, "%s: initial sleep time must be given as a positive integer!\n", argv[0]);
+					exit(1);
+				}
+				break;
+			case 'q':
+				appquery = strdup(optarg);
+				break;
+			case 'v':
+				verbose = 1;
+				break;
+			case '?':
+				usage(argv[0]);
+			default:
+				/* getopt_long already emitted complaint */
+				exit(1);
+		}
+	}
+
+	if (argc - optind != 2)
+		usage(argv[0]);
+
+	if (appname != NULL && appquery != NULL)
+	{
+		fprintf(stderr, "%s: cannot specify both appname and query!",
+				argv[0]);
+		usage(argv[0]);
+	}
+
+	connstr = argv[optind+1];
+	filename = argv[optind];
+
+	/* Now get a connection and run the query */
+	conn = PQconnectdb(connstr);
+	if (!conn || PQstatus(conn) != CONNECTION_OK)
+	{
+		fprintf(stderr, "%s: could not connect to server: %s\n",
+				argv[0], PQerrorMessage(conn));
+		exit(1);
+	}
+
+
+	while (1)
+	{
+		if (!firstloop)
+			/*
+			 * We don't want to sleep on the first iteration, in case
+			 * we are catching up from being behind.
+			 */
+			sleep(sleeptime);
+		else
+		{
+			sleep(initialsleep);
+			firstloop = 0;
+		}
+
+
+		if (appquery)
+			res = PQexec(conn, appquery);
+		else
+		{
+			char query[1024];
+			snprintf(query,
+					 sizeof(query),
+					 "SELECT write_location, pg_xlogfile_name(write_location) FROM pg_stat_replication WHERE application_name='%s'",
+					 appname ? appname : "pg_receivexlog"
+				);
+			res = PQexec(conn, query);
+		}
+
+
+		if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
+		{
+			/* A failed query is a critical error, so exit */
+			fprintf(stderr, "%s: could not query for replication status: %s\n",
+					argv[0], PQerrorMessage(conn));
+			PQclear(res);
+			PQfinish(conn);
+			exit(1);
+		}
+
+		if (PQntuples(res) == 0)
+		{
+			if (verbose)
+				fprintf(stderr, "%s: no replication clients active.\n",
+						argv[0]);
+			PQclear(res);
+			continue;
+		}
+
+		if (PQntuples(res) > 1)
+		{
+			/* Too many clients indicates a configuration error, so exit */
+			fprintf(stderr, "%s: %i replication clients found, can only work with 1.\n",
+					argv[0], PQntuples(res));
+			PQclear(res);
+			PQfinish(conn);
+			exit(1);
+		}
+
+		if (PQnfields(res) != 2)
+		{
+			/* Can only happen for custom queries, and is a configuration error */
+			fprintf(stderr, "%s: custom query returned %i fields, must be 2!\n",
+					argv[0], PQnfields(res));
+			PQclear(res);
+			PQfinish(conn);
+			exit(1);
+		}
+
+		/*
+		 * Ok, we've got useful data back. Time to do our checks.
+		 * Compare the returned filename with the one that we have been asked
+		 * about. If the one we've been asked to archive is the same or newer
+		 * than what's seen on the slave, it's not safe to archive it.
+		 */
+		if (strcmp(filename, PQgetvalue(res, 0, 1)) >= 0)
+		{
+			if (verbose)
+				fprintf(stderr, "%s: current streamed position (%s, file %s) is older than archive file (%s), not ready to archive\n",
+						argv[0], PQgetvalue(res, 0, 0), PQgetvalue(res, 0, 1), filename);
+			PQclear(res);
+			continue;
+		}
+
+		/*
+		 * The file is old enough that it's ready to be archived
+		 */
+		if (verbose)
+			printf("%s: file %s is ok to archive (current streaming pos is %s, file %s)\n",
+				   argv[0], filename, PQgetvalue(res, 0, 0), PQgetvalue(res, 0, 1));
+
+		PQclear(res);
+		break;
+	}
+	PQfinish(conn);
+	return 0;
+}
diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index 6b13a0a..917b45d 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -202,6 +202,7 @@ pages.
   </para>
 
  &pgarchivecleanup;
+ &pgretainxlog;
  &pgstandby;
  &pgtestfsync;
  &pgtesttiming;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 368f932..040a563 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -126,6 +126,7 @@
 <!ENTITY pgbuffercache   SYSTEM "pgbuffercache.sgml">
 <!ENTITY pgcrypto        SYSTEM "pgcrypto.sgml">
 <!ENTITY pgfreespacemap  SYSTEM "pgfreespacemap.sgml">
+<!ENTITY pgretainxlog    SYSTEM "pgretainxlog.sgml">
 <!ENTITY pgrowlocks      SYSTEM "pgrowlocks.sgml">
 <!ENTITY pgstandby       SYSTEM "pgstandby.sgml">
 <!ENTITY pgstatstatements SYSTEM "pgstatstatements.sgml">
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index e834285..298aa22 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -759,6 +759,13 @@ archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'
    </para>
 
    <para>
+    An alternative to setting <varname>wal_keep_segments</> is to use the
+    <application>pg_retainxlog</> utility which is designed to keep just
+    enough WAL available on the master as necessary, see
+    <xref linkend="pgretainxlog">.
+   </para>
+
+   <para>
     To use streaming replication, set up a file-based log-shipping standby
     server as described in <xref linkend="warm-standby">. The step that
     turns a file-based log-shipping standby into streaming replication
diff --git a/doc/src/sgml/pgretainxlog.sgml b/doc/src/sgml/pgretainxlog.sgml
new file mode 100644
index 0000000..657d5e7
--- /dev/null
+++ b/doc/src/sgml/pgretainxlog.sgml
@@ -0,0 +1,199 @@
+<!-- doc/src/sgml/pgretainxlog.sgml -->
+
+<refentry id="pgretainxlog">
+ <refmeta>
+  <refentrytitle><application>pg_retainxlog</application></refentrytitle>
+  <manvolnum>1</manvolnum>
+  <refmiscinfo>Application</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>pg_retainxlog</refname>
+  <refpurpose>block <productname>PostgreSQL</productname> WAL file recycling</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="pgretainxlog">
+  <primary>pg_retainxlog</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+  <cmdsynopsis>
+   <command>pg_retainxlog</command>
+   <arg rep="repeat"><replaceable>option</replaceable></arg>
+   <arg choice="plain"><replaceable>filename</replaceable></arg>
+   <arg choice="plain"><replaceable>connectionstring</replaceable></arg>
+  </cmdsynopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   <application>pg_retainxlog</application> is designed to be used as an
+   <varname>archive_command</varname> in situations where there is no
+   traditional <varname>archive_command</varname> configured. When configured,
+   it will block WAL file recycling until the WAL has arrived at one or more
+   replication or <application>pg_receivexlog</application> clients, to make
+   sure the clients cannot fall so far behind that they stop working.
+  </para>
+
+  <para>
+   <application>pg_retainxlog</application> connects to the database using
+   a regular connection and typically runs a query against the
+   <literal>pg_stat_replication</literal> view to get the status of the
+   clients. If the query indicates that there are clients who still need
+   the WAL that is currently being archived, it will loop with a
+   configurable sleep until this WAL has been sent. Only then will the
+   command return, thereby allowing <productname>PostgreSQL</productname>
+   to recycle the WAL file.
+  </para>
+
+  <para>
+   By default, <application>pg_retainxlog</application> will query the
+   <literal>pg_stat_replication</literal> view for connections made by
+   <application>pg_receivexlog</application>. The default query only
+   supports a single instance of <application>pg_receivexlog</application>
+   connected to the system, and will allow the WAL to be removed as soon
+   as this instance has received it. In a scenarios where there are multiple
+   instances of <application>pg_receivexlog</application>, when using
+   regular replication slaves or a combination thereof, a custom query
+   can be specified using the <literal>-q</literal> option. This query
+   must return exactly one row with exactly two fields, being the oldest
+   WAL location that it's OK to remove and the name of the current WAL file.
+   The default query is:
+<programlisting>
+SELECT write_location, pg_xlogfile_name(write_location)
+   FROM pg_stat_replication
+   WHERE application_name='pg_receivexlog'
+</programlisting>
+   In a custom scenario this query will typically need to take into account
+   a list of registered replication slaves, so it does not return
+   incorrect data in case all slaves are not connected.
+   </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Options</title>
+
+   <para>
+    <application>pg_retainxlog</application> accepts the following command-line arguments:
+
+    <variablelist>
+
+     <varlistentry>
+      <term><option>-a <replaceable>appname</replaceable></option></term>
+      <term><option>--appname <replaceable>appname</replaceable></option></term>
+      <listitem>
+       <para>
+        Set the application name to filter
+        <literal>pg_stat_replication</literal> for. If not specified,
+        <application>pg_retainxlog</application> will look for connections
+        from <application>pg_receivexlog</application>.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-q <replaceable>query</replaceable></option></term>
+      <term><option>--query <replaceable>query</replaceable></option></term>
+      <listitem>
+       <para>
+        Set a custom query to run. This query must return a single row
+        with two columns. These two fields must be the oldest log position
+        that it's ok to remove, and the name of the current xlog file.
+        </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-s <replaceable>sleep</replaceable></option></term>
+      <term><option>--sleep <replaceable>sleep</replaceable></option></term>
+      <listitem>
+       <para>
+        Sleep time between attempts, specified in seconds. Default value
+        is 10 seconds.
+        </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-i <replaceable>initialsleep</replaceable></option></term>
+      <term><option>--initialsleep <replaceable>initialsleep</replaceable></option></term>
+      <listitem>
+       <para>
+        Sleep time before first attempt, specified in seconds. Default value
+        is 0 seconds, meaning the first attempt is run immediately upon startup.
+        </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-v</></term>
+      <term><option>--verbose</></term>
+      <listitem>
+       <para>
+        Show verbose output.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-?</></term>
+      <term><option>--help</></term>
+      <listitem>
+       <para>
+        Show help about <application>pg_retainxlog</application> command line
+        arguments, and exit.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Examples</title>
+
+  <para>
+   In it's simplest form, with a single
+   <application>pg_receivexlog</application> connected to the system, you
+   might use:
+   <programlisting>
+archive_command = '/path/to/pg_retainxlog -i 1 %f "user=postgres"'
+   </programlisting>
+  </para>
+
+  <para>
+   When used for replication slaves, to remove the need to configure
+   <varname>wal_keep_segments</varname>, you might use something like:
+   <programlisting>
+archive_command = '/path/to/pg_retainxlog -i 1 -q "SELECT min(write_location), pg_xlogfile_name(min(write_location)) FROM pg_stat_replication WHERE application_name='walreceiver'" %f "user=postgres"
+   </programlisting>
+  </para>
+  <note>
+   <para>
+    For this scenario to work in case all replication slaves are not always
+    connected, you need to create a table listing all your replication slaves,
+    and make the query return zero rows (for example) when all slaves are not
+    connected, to make sure that no WAL is recycled until they have all had
+    a chance to catch up.
+   </para>
+  </note>
+ </refsect1>
+
+ <refsect1>
+  <title>Author</title>
+
+  <para>
+   Magnus Hagander <email>magnus@hagander.net</email>
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="app-pgreceivexlog"></member>
+  </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/ref/pg_receivexlog.sgml b/doc/src/sgml/ref/pg_receivexlog.sgml
index d06dd1f..1bbd501 100644
--- a/doc/src/sgml/ref/pg_receivexlog.sgml
+++ b/doc/src/sgml/ref/pg_receivexlog.sgml
@@ -289,6 +289,7 @@ archive_command = 'sleep 5 &amp;&amp; test -f /mnt/server/archivedir/%f'
 
   <simplelist type="inline">
    <member><xref linkend="APP-PGBASEBACKUP"></member>
+   <member><xref linkend="pgretainxlog"></member>
   </simplelist>
  </refsect1>