pg_retainxlog for inclusion in 9.3?
So, it turns out the reason I got no feedback on this tool, was that I
forgot both to email about and to actually push the code to github :O
So this is actually code that's almost half a year old and that I was
supposed to submit for the first or second commitfest. Oops.
So, the tool and a README for it right now can be found at
https://github.com/mhagander/pg_retainxlog for the time being.
The idea behind the tool is to plug a hole in the case when
pg_receivexlog is used for log archiving, which is that you still need
a "blocking" archive_command in order to make sure that files aren't
recycled on the master. So for 9.2 you can do this with an
archive_command that checks if the file has appeared properly on the
slave - but that usually means you're back at requiring ssh
connectivity between the machines, for example. Even though this
information is actually avialable on the master...
This can also be of use to pure replication scenarios, where you don't
know how to tune wal_keep_segments, but using actual live feedback
instead of guessing.
When pg_retainxlog is used as an archive_command, it will check the
pg_stat_replication view instead of checking the slave. It will then
only return ok once the requested logfile has been replicated to the
slave. By default it will look for a replication client named
pg_receivexlog, but it supports overriding the query with anything -
so you can say things like "needs to have arrived on at least two
replication slaves before we consider it archived". Or if used instead
of wal_keep_segmnets, needs to have arrived at *all* replication
slaves.
Is this a tool that people would like to see included in the general
toolchain? If so, I'll reformat it to work in the general build
environment and submit it for the last commitfest.
(comments on the code itself are of course also welcome)
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 01/01/2013 04:10 PM, Magnus Hagander wrote:
So, it turns out the reason I got no feedback on this tool, was that I
forgot both to email about and to actually push the code to github :O
So this is actually code that's almost half a year old and that I was
supposed to submit for the first or second commitfest. Oops.So, the tool and a README for it right now can be found at
https://github.com/mhagander/pg_retainxlog for the time being.The idea behind the tool is to plug a hole in the case when
pg_receivexlog is used for log archiving, which is that you still need
a "blocking" archive_command in order to make sure that files aren't
recycled on the master. So for 9.2 you can do this with an
archive_command that checks if the file has appeared properly on the
slave - but that usually means you're back at requiring ssh
connectivity between the machines, for example. Even though this
information is actually avialable on the master...This can also be of use to pure replication scenarios, where you don't
know how to tune wal_keep_segments, but using actual live feedback
instead of guessing.When pg_retainxlog is used as an archive_command, it will check the
pg_stat_replication view instead of checking the slave. It will then
only return ok once the requested logfile has been replicated to the
slave. By default it will look for a replication client named
pg_receivexlog, but it supports overriding the query with anything -
so you can say things like "needs to have arrived on at least two
replication slaves before we consider it archived". Or if used instead
of wal_keep_segmnets, needs to have arrived at *all* replication
slaves.Is this a tool that people would like to see included in the general
toolchain? If so, I'll reformat it to work in the general build
environment and submit it for the last commitfest.
+1
------------------------
Hannu Krosing
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Magnus Hagander <magnus@hagander.net> writes:
Is this a tool that people would like to see included in the general
toolchain? If so, I'll reformat it to work in the general build
environment and submit it for the last commitfest.
Please do.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jan 1, 2013 at 10:10 AM, Magnus Hagander <magnus@hagander.net> wrote:
So, it turns out the reason I got no feedback on this tool, was that I
forgot both to email about and to actually push the code to github :O
So this is actually code that's almost half a year old and that I was
supposed to submit for the first or second commitfest. Oops.So, the tool and a README for it right now can be found at
https://github.com/mhagander/pg_retainxlog for the time being.The idea behind the tool is to plug a hole in the case when
pg_receivexlog is used for log archiving, which is that you still need
a "blocking" archive_command in order to make sure that files aren't
recycled on the master. So for 9.2 you can do this with an
archive_command that checks if the file has appeared properly on the
slave - but that usually means you're back at requiring ssh
connectivity between the machines, for example. Even though this
information is actually avialable on the master...This can also be of use to pure replication scenarios, where you don't
know how to tune wal_keep_segments, but using actual live feedback
instead of guessing.When pg_retainxlog is used as an archive_command, it will check the
pg_stat_replication view instead of checking the slave. It will then
only return ok once the requested logfile has been replicated to the
slave. By default it will look for a replication client named
pg_receivexlog, but it supports overriding the query with anything -
so you can say things like "needs to have arrived on at least two
replication slaves before we consider it archived". Or if used instead
of wal_keep_segmnets, needs to have arrived at *all* replication
slaves.Is this a tool that people would like to see included in the general
toolchain? If so, I'll reformat it to work in the general build
environment and submit it for the last commitfest.(comments on the code itself are of course also welcome)
I would tend to vote for putting this in contrib rather than src/bin.
But apart from that I have no objection to the idea.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 3, 2013 at 3:13 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Jan 1, 2013 at 10:10 AM, Magnus Hagander <magnus@hagander.net> wrote:
So, it turns out the reason I got no feedback on this tool, was that I
forgot both to email about and to actually push the code to github :O
So this is actually code that's almost half a year old and that I was
supposed to submit for the first or second commitfest. Oops.So, the tool and a README for it right now can be found at
https://github.com/mhagander/pg_retainxlog for the time being.The idea behind the tool is to plug a hole in the case when
pg_receivexlog is used for log archiving, which is that you still need
a "blocking" archive_command in order to make sure that files aren't
recycled on the master. So for 9.2 you can do this with an
archive_command that checks if the file has appeared properly on the
slave - but that usually means you're back at requiring ssh
connectivity between the machines, for example. Even though this
information is actually avialable on the master...This can also be of use to pure replication scenarios, where you don't
know how to tune wal_keep_segments, but using actual live feedback
instead of guessing.When pg_retainxlog is used as an archive_command, it will check the
pg_stat_replication view instead of checking the slave. It will then
only return ok once the requested logfile has been replicated to the
slave. By default it will look for a replication client named
pg_receivexlog, but it supports overriding the query with anything -
so you can say things like "needs to have arrived on at least two
replication slaves before we consider it archived". Or if used instead
of wal_keep_segmnets, needs to have arrived at *all* replication
slaves.Is this a tool that people would like to see included in the general
toolchain? If so, I'll reformat it to work in the general build
environment and submit it for the last commitfest.(comments on the code itself are of course also welcome)
I would tend to vote for putting this in contrib rather than src/bin.
But apart from that I have no objection to the idea.
Any particular reason? It goes pretty tightly together with
pg_receivexlog, which is why I'd prefer putting it alongside that one.
But if you have a good argument against it, I can change my mind :)
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 3, 2013 at 11:32 AM, Magnus Hagander <magnus@hagander.net> wrote:
Any particular reason? It goes pretty tightly together with
pg_receivexlog, which is why I'd prefer putting it alongside that one.
But if you have a good argument against it, I can change my mind :)
Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 1/3/13 12:30 PM, Robert Haas wrote:
On Thu, Jan 3, 2013 at 11:32 AM, Magnus Hagander <magnus@hagander.net> wrote:
Any particular reason? It goes pretty tightly together with
pg_receivexlog, which is why I'd prefer putting it alongside that one.
But if you have a good argument against it, I can change my mind :)Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.
It does seem like a hack. Couldn't this be implemented with a backend
switch instead?
Also, as a small practical matter, since this is a server-side program
(since it's being used as archive_command), we shouldn't put it into the
pg_basebackup directory, because that would blur the lines about what to
install where, in particular for the translations.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.
Do you have the specs of such better way? Would it be a problem to have
both pg_retainxlog and the new way?
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jan 4, 2013 at 7:13 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
On 1/3/13 12:30 PM, Robert Haas wrote:
On Thu, Jan 3, 2013 at 11:32 AM, Magnus Hagander <magnus@hagander.net> wrote:
Any particular reason? It goes pretty tightly together with
pg_receivexlog, which is why I'd prefer putting it alongside that one.
But if you have a good argument against it, I can change my mind :)Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.It does seem like a hack. Couldn't this be implemented with a backend
switch instead?
It definitely is a bit of a hack.
I assume by backend switch you mean guc, right? If so, no, not easily
so. Because it's the archiver process that does the deleting. And this
process does not have access to a "full backend interface", e.g. the
ability to run a query. We could make it look at the same data that's
currently shown in pg_stat_replicatoin through shared memory, but thta
would *only* work in the very most simple cases (e.g. a single
pg_receivexlog and no other replication). The ability to run a custom
SQL query is going to be necessary for anything a bit more advanced.
Also, as a small practical matter, since this is a server-side program
(since it's being used as archive_command), we shouldn't put it into the
pg_basebackup directory, because that would blur the lines about what to
install where, in particular for the translations.
Good argument. That along with the being a hack, and the comment from
Robert, means that maybe contrib/ is a better place for it, yes.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jan 1, 2013 at 10:10 AM, Magnus Hagander <magnus@hagander.net> wrote:
So, it turns out the reason I got no feedback on this tool, was that I
forgot both to email about and to actually push the code to github :O
So this is actually code that's almost half a year old and that I was
supposed to submit for the first or second commitfest. Oops.So, the tool and a README for it right now can be found at
https://github.com/mhagander/pg_retainxlog for the time being.The idea behind the tool is to plug a hole in the case when
pg_receivexlog is used for log archiving, which is that you still need
a "blocking" archive_command in order to make sure that files aren't
recycled on the master. So for 9.2 you can do this with an
archive_command that checks if the file has appeared properly on the
slave - but that usually means you're back at requiring ssh
connectivity between the machines, for example. Even though this
information is actually avialable on the master...This can also be of use to pure replication scenarios, where you don't
know how to tune wal_keep_segments, but using actual live feedback
instead of guessing.When pg_retainxlog is used as an archive_command, it will check the
pg_stat_replication view instead of checking the slave. It will then
only return ok once the requested logfile has been replicated to the
slave. By default it will look for a replication client named
pg_receivexlog, but it supports overriding the query with anything -
so you can say things like "needs to have arrived on at least two
replication slaves before we consider it archived". Or if used instead
of wal_keep_segmnets, needs to have arrived at *all* replication
slaves.Is this a tool that people would like to see included in the general
toolchain? If so, I'll reformat it to work in the general build
environment and submit it for the last commitfest.(comments on the code itself are of course also welcome)
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
+1 to this concept, however it may be implemented.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sat, Jan 5, 2013 at 3:11 PM, Magnus Hagander <magnus@hagander.net> wrote:
On Fri, Jan 4, 2013 at 7:13 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
On 1/3/13 12:30 PM, Robert Haas wrote:
On Thu, Jan 3, 2013 at 11:32 AM, Magnus Hagander <magnus@hagander.net> wrote:
Any particular reason? It goes pretty tightly together with
pg_receivexlog, which is why I'd prefer putting it alongside that one.
But if you have a good argument against it, I can change my mind :)Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.It does seem like a hack. Couldn't this be implemented with a backend
switch instead?It definitely is a bit of a hack.
I assume by backend switch you mean guc, right? If so, no, not easily
so. Because it's the archiver process that does the deleting. And this
process does not have access to a "full backend interface", e.g. the
ability to run a query. We could make it look at the same data that's
currently shown in pg_stat_replicatoin through shared memory, but thta
would *only* work in the very most simple cases (e.g. a single
pg_receivexlog and no other replication). The ability to run a custom
SQL query is going to be necessary for anything a bit more advanced.Also, as a small practical matter, since this is a server-side program
(since it's being used as archive_command), we shouldn't put it into the
pg_basebackup directory, because that would blur the lines about what to
install where, in particular for the translations.Good argument. That along with the being a hack, and the comment from
Robert, means that maybe contrib/ is a better place for it, yes.
Here's a version for inclusion in /contrib.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Attachments:
pg_retainxlog.diffapplication/octet-stream; name=pg_retainxlog.diffDownload
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 && test -f /mnt/server/archivedir/%f'
<simplelist type="inline">
<member><xref linkend="APP-PGBASEBACKUP"></member>
+ <member><xref linkend="pgretainxlog"></member>
</simplelist>
</refsect1>
On Fri, Jan 4, 2013 at 4:55 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.Do you have the specs of such better way? Would it be a problem to have
both pg_retainxlog and the new way?
Well, I think in the long term we are likely to want the master to
have some kind of ability to track the positions of its slaves, even
when they are disconnected. And, optionally, to retain the WAL that
they need, again even when they are disconnected. If such an ability
materializes, this will be moot (even as I think that pg_standby is
now largely moot, at least for new installations, now that we have
standby_mode=on).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jan 14, 2013 at 5:56 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Jan 4, 2013 at 4:55 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.Do you have the specs of such better way? Would it be a problem to have
both pg_retainxlog and the new way?Well, I think in the long term we are likely to want the master to
have some kind of ability to track the positions of its slaves, even
when they are disconnected. And, optionally, to retain the WAL that
they need, again even when they are disconnected. If such an ability
materializes, this will be moot (even as I think that pg_standby is
now largely moot, at least for new installations, now that we have
standby_mode=on).
I agree. But just as we had pg_standby for quite a while before we got
standby_mode=on, I believe we should have pg_retainxlog (or something
like it) until we have something more integrated.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jan 15, 2013 at 2:51 AM, Magnus Hagander <magnus@hagander.net> wrote:
On Mon, Jan 14, 2013 at 5:56 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Jan 4, 2013 at 4:55 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.Do you have the specs of such better way? Would it be a problem to have
both pg_retainxlog and the new way?Well, I think in the long term we are likely to want the master to
have some kind of ability to track the positions of its slaves, even
when they are disconnected. And, optionally, to retain the WAL that
they need, again even when they are disconnected. If such an ability
materializes, this will be moot (even as I think that pg_standby is
now largely moot, at least for new installations, now that we have
standby_mode=on).I agree. But just as we had pg_standby for quite a while before we got
standby_mode=on, I believe we should have pg_retainxlog (or something
like it) until we have something more integrated.
Yep, not disagreeing.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
After reviewing this, it appears to me that this is really just a very
verbose version of
archive_command = 'sleep $initialsleep; while test $(psql -AtX -c "select pg_xlogfile_name(something) < $$%f$$ collate \"C\";") = t; sleep $sleep; done'
I think it might be better to just document this as an example. I don't
quite see the overhead of maintaining another tool justified.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 24, 2013 at 6:04 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
After reviewing this, it appears to me that this is really just a very
verbose version ofarchive_command = 'sleep $initialsleep; while test $(psql -AtX -c "select pg_xlogfile_name(something) < $$%f$$ collate \"C\";") = t; sleep $sleep; done'
I think it might be better to just document this as an example. I don't
quite see the overhead of maintaining another tool justified.
Well, obviously I don't entirely agree ;)
Yes, it's a convenience command. Like pg_standby was. And like many
other commands that we maintain as part of *core*, such as createuser,
vacuumdb, etc. Those can all be done with an even *simpler* command
than the one you suggest above. So I don't see that as an argument why
it wouldn't be useful.
Also, the command you suggest above does not work on Windows. You can
probably write a .BAT file to do it for you, but I'm pretty sure it's
impossible to do it as an archive_command there.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Magnus Hagander <magnus@hagander.net> writes:
On Thu, Jan 24, 2013 at 6:04 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
I think it might be better to just document this as an example. I don't
quite see the overhead of maintaining another tool justified.
Well, obviously I don't entirely agree ;)
Yes, it's a convenience command. Like pg_standby was. And like many
other commands that we maintain as part of *core*, such as createuser,
vacuumdb, etc. Those can all be done with an even *simpler* command
than the one you suggest above. So I don't see that as an argument why
it wouldn't be useful.
We've discussed removing a lot of those tools, too. Not breaking
backwards compatibility is probably the only reason they're still there.
In the case at hand, I seem to recall from upthread that we expect
this'd be obsolete in a release or two. If that's true then I think
a para or two of documentation is a better idea than a tool we'll be
essentially condemned to keep maintaining forever.
Also, the command you suggest above does not work on Windows. You can
probably write a .BAT file to do it for you, but I'm pretty sure it's
impossible to do it as an archive_command there.
Perhaps we could whip up such a .BAT file and put it in the docs?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 24, 2013 at 6:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
On Thu, Jan 24, 2013 at 6:04 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
I think it might be better to just document this as an example. I don't
quite see the overhead of maintaining another tool justified.Well, obviously I don't entirely agree ;)
Yes, it's a convenience command. Like pg_standby was. And like many
other commands that we maintain as part of *core*, such as createuser,
vacuumdb, etc. Those can all be done with an even *simpler* command
than the one you suggest above. So I don't see that as an argument why
it wouldn't be useful.We've discussed removing a lot of those tools, too. Not breaking
backwards compatibility is probably the only reason they're still there.In the case at hand, I seem to recall from upthread that we expect
this'd be obsolete in a release or two. If that's true then I think
a para or two of documentation is a better idea than a tool we'll be
essentially condemned to keep maintaining forever.
Not really sure there is such an expectation - any more than there was
such an expectation when we initially put pg_standby in there. It
would be *possible* to do it, certainly. But it's not like we have an
actual plan. And AFAIK the stuff that was discussed upthread was a
simplified version of it - not the full flexibility.
That said, it's certainly a point that we'd have to maintain it. But I
don't see why we'd have to maintain it beyond the point where we
included the same functionality in core, if we did.
Also, the command you suggest above does not work on Windows. You can
probably write a .BAT file to do it for you, but I'm pretty sure it's
impossible to do it as an archive_command there.Perhaps we could whip up such a .BAT file and put it in the docs?
That would probably work, yes.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sat, Jan 5, 2013 at 11:11 PM, Magnus Hagander <magnus@hagander.net> wrote:
On Fri, Jan 4, 2013 at 7:13 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
On 1/3/13 12:30 PM, Robert Haas wrote:
On Thu, Jan 3, 2013 at 11:32 AM, Magnus Hagander <magnus@hagander.net> wrote:
Any particular reason? It goes pretty tightly together with
pg_receivexlog, which is why I'd prefer putting it alongside that one.
But if you have a good argument against it, I can change my mind :)Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.It does seem like a hack. Couldn't this be implemented with a backend
switch instead?It definitely is a bit of a hack.
I assume by backend switch you mean guc, right? If so, no, not easily
so. Because it's the archiver process that does the deleting.
The process which deletes the old WAL files is the checkpointer. The
checkpointer can access to the shared memory and know the location
of the WAL record which has been already replicated to the standby.
ISTM it's not difficult to implement the logic which pg_retainxlog provides
into the checkpointer. How about just changing the checkpointer so
that it checks whether the WAL file to delete has been already not
only archived but also replicated if GUC flag is enabled?
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
On 2013-01-26 02:21:00 +0900, Fujii Masao wrote:
On Sat, Jan 5, 2013 at 11:11 PM, Magnus Hagander <magnus@hagander.net> wrote:
On Fri, Jan 4, 2013 at 7:13 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
On 1/3/13 12:30 PM, Robert Haas wrote:
On Thu, Jan 3, 2013 at 11:32 AM, Magnus Hagander <magnus@hagander.net> wrote:
Any particular reason? It goes pretty tightly together with
pg_receivexlog, which is why I'd prefer putting it alongside that one.
But if you have a good argument against it, I can change my mind :)Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.It does seem like a hack. Couldn't this be implemented with a backend
switch instead?It definitely is a bit of a hack.
I assume by backend switch you mean guc, right? If so, no, not easily
so. Because it's the archiver process that does the deleting.The process which deletes the old WAL files is the checkpointer. The
checkpointer can access to the shared memory and know the location
of the WAL record which has been already replicated to the standby.
ISTM it's not difficult to implement the logic which pg_retainxlog provides
into the checkpointer. How about just changing the checkpointer so
that it checks whether the WAL file to delete has been already not
only archived but also replicated if GUC flag is enabled?
The problem with that is that to implement it robustly we would need
persistent state about the replicas.
Greetings,
Andres Freund
--
Andres Freund 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
On 1/25/13 12:24 PM, Andres Freund wrote:
On 2013-01-26 02:21:00 +0900, Fujii Masao wrote:
The process which deletes the old WAL files is the checkpointer. The
checkpointer can access to the shared memory and know the location
of the WAL record which has been already replicated to the standby.
ISTM it's not difficult to implement the logic which pg_retainxlog provides
into the checkpointer. How about just changing the checkpointer so
that it checks whether the WAL file to delete has been already not
only archived but also replicated if GUC flag is enabled?
That makes sense.
The problem with that is that to implement it robustly we would need
persistent state about the replicas.
Well, pg_retainxlog kind of handwaves around that. If you use it in the
default mode, it assumes that the pg_receivexlog entries in
pg_stat_replication are that state. And then it says, if you use other
kinds of clients, you need to keep track of that state yourself. But
that seems to assume that pg_receivexlog never disconnects (thus losing
its entries from pg_stat_replication). (pg_receivexlog is designed to
automatically reconnect on connection loss, so this possibility was
obviously thought about.)
So it seems to me this just doesn't work (this way).
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers