From: Gilles Darold <gilles.darold@dalibo.com>
To: pgsql-hackers@postgresql.org
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Fujii Masao <masao.fujii@gmail.com>
Cc: Bernd Helmle <mailings@oopsware.de>
Cc: Euler Taveira de Oliveira <euler@timbira.com>
Cc: Marti Raudsepp <marti@juffo.org>
Subject: Re: Patch pg_is_in_backup()
Date: Fri, 02 Mar 2012 16:05:20 +0100
Message-ID: <4F50E1B0.90808@dalibo.com> (raw)
In-Reply-To: <CABUevEzi=Fa5xUDRYUkCX3jeE26Y3F3AsO9T_yZQPUzYANdPpg@mail.gmail.com>
References: <4F26E291.9020605@dalibo.com>
<CABRT9RAo_xTOVdBAEDqzcP+QvoHa0SZW1Ud1p3vjmA5tJUnYLw@mail.gmail.com>
<CABUevExm4_a-Yw3pt=weppb5ckzAYZwK3e79m__sznQNjjDveg@mail.gmail.com>
<4F2B470F.5070107@timbira.com>
<CAHGQGwEE5Zk05cPeTVWjd+TYce10zemMs_Li7ekkroiFKPj9kw@mail.gmail.com>
<691525170451D3F76CEF7ED1@apophis.local>
<CAHGQGwFuqazEaT+YCDUoZe-HwQ-txcq=djgisayyWnHhWn+1aQ@mail.gmail.com>
<CABUevEzi=Fa5xUDRYUkCX3jeE26Y3F3AsO9T_yZQPUzYANdPpg@mail.gmail.com>
Le 03/02/2012 10:52, Magnus Hagander a écrit :
> On Fri, Feb 3, 2012 at 10:47, Fujii Masao <masao.fujii@gmail.com> wrote:
>> On Fri, Feb 3, 2012 at 6:10 PM, Bernd Helmle <mailings@oopsware.de> wrote:
>>>
>>> --On 3. Februar 2012 13:21:11 +0900 Fujii Masao <masao.fujii@gmail.com>
>>> wrote:
>>>
>>>> It seems to be more user-friendly to introduce a view like pg_stat_backup
>>>> rather than the function returning an array.
>>>
>>> I like this idea. A use case i saw for monitoring backup_label's in the
>>> past, was mainly to discover a forgotten exclusive pg_stop_backup() (e.g.
>>> due to broken backup scripts). If the view would be able to distinguish
>>> both, exclusive and non-exclusive backups, this would be great.
>> Agreed. Monitoring an exclusive backup is very helpful. But I wonder
>> why we want to monitor non-exclusive backup. Is there any use case?
> Actually, we can already monitor much of the non-exclusive one through
> pg_stat_replication. Including the info on when it was started (at
> least in almost every case, that will be more or less the
> backend_start time for that one)
>
>> If we want to monitor non-exclusive backup, why not pg_dump backup?
> .. which we can also monitor though pg_stat_activity by looking at
> application_name (which can be faked of course, but still)
>
>> If there is no use case, it seems sufficient to implement the function
>> which reports the information only about exclusive backup.
> Yeah, thinking more of it, i think I agree. But the function should
> then probably be named in such a way that it's clear that we're
> talking about exclusive backups, e.g. not pg_is_in_backup() but
> instead pg_is_in_exclusive_backup() (renamed if we change it to return
> the timestamp instead, of course, but you get the idea)
Agreed and sorry for the response delay. I've attached 2 patches here,
the first one is the same as before with just the renaming of the
function into pg_is_in_exclusive_backup(). Maybe this patch should be
abandoned in favor of the second one which introduce a new function
called pg_exclusive_backup_start_time() that return the backup_label
START TIME information as a timestamp with timezone.
Sample usage/result:
postgres=# select pg_start_backup('Online backup');
pg_start_backup
-----------------
0/2000020
(1 ligne)
postgres=# select pg_exclusive_backup_start_time();
pg_exclusive_backup_start_time
--------------------------------
2012-03-02 14:52:49+01
(1 ligne)
postgres=# select now() - pg_exclusive_backup_start_time() as
backup_started_since;
backup_started_since
------------------------
00:12:24.569226
(1 ligne)
postgres=# select pg_stop_backup();
pg_stop_backup
----------------
0/2000098
(1 ligne)
postgres=# select pg_exclusive_backup_start_time();
pg_exclusive_backup_start_time
--------------------------------
(1 ligne)
Regards,
--
Gilles Darold
http://dalibo.com - http://dalibo.orgAttachments:download all (2 files, .tar.gz)postgresql-is_in_exclusive_backup-patch.diff (text/x-patch, 3.4K) download
diff -ru postgresql/doc/src/sgml/func.sgml postgresql-is_in_exclusive_backup-patch//doc/src/sgml/func.sgml
--- postgresql/doc/src/sgml/func.sgml 2012-01-26 23:01:31.956613398 +0100+++ postgresql-is_in_exclusive_backup-patch//doc/src/sgml/func.sgml 2012-03-01 10:58:20.556472776 +0100@@ -14371,6 +14371,9 @@
<primary>pg_current_xlog_location</primary>
</indexterm>
<indexterm>
+ <primary>pg_is_in_exclusive_backup</primary>+ </indexterm>+ <indexterm>
<primary>pg_start_backup</primary>
</indexterm>
<indexterm>
@@ -14424,6 +14427,14 @@
</row>
<row>
<entry>
+ <literal><function>pg_is_in_exclusive_backup()</function></literal>+ </entry>+ <entry><type>bool</type></entry>+ <entry>True if an on-line exclusive backup is still in progress.+ </entry>+ </row>+ <row>+ <entry>
<literal><function>pg_start_backup(<parameter>label</> <type>text</> <optional>, <parameter>fast</> <type>boolean</> </optional>)</function></literal>
</entry>
<entry><type>text</type></entry>
diff -ru postgresql/src/backend/access/transam/xlogfuncs.c postgresql-is_in_exclusive_backup-patch//src/backend/access/transam/xlogfuncs.c
--- postgresql/src/backend/access/transam/xlogfuncs.c 2012-01-26 23:01:32.032613398 +0100+++ postgresql-is_in_exclusive_backup-patch//src/backend/access/transam/xlogfuncs.c 2012-03-01 10:48:48.056473056 +0100@@ -465,3 +465,12 @@
{
PG_RETURN_BOOL(RecoveryInProgress());
}
++/*+ * Returns bool with current on-line backup mode, a global state.+ */+Datum+pg_is_in_exclusive_backup(PG_FUNCTION_ARGS)+{+ PG_RETURN_BOOL(BackupInProgress());+}
diff -ru postgresql/src/include/access/xlog_internal.h postgresql-is_in_exclusive_backup-patch//src/include/access/xlog_internal.h
--- postgresql/src/include/access/xlog_internal.h 2012-01-26 23:01:32.332613398 +0100+++ postgresql-is_in_exclusive_backup-patch//src/include/access/xlog_internal.h 2012-03-01 10:48:48.076473056 +0100@@ -281,5 +281,6 @@
extern Datum pg_xlog_replay_pause(PG_FUNCTION_ARGS);
extern Datum pg_xlog_replay_resume(PG_FUNCTION_ARGS);
extern Datum pg_is_xlog_replay_paused(PG_FUNCTION_ARGS);
+extern Datum pg_is_in_exclusive_backup(PG_FUNCTION_ARGS);
#endif /* XLOG_INTERNAL_H */
diff -ru postgresql/src/include/catalog/pg_proc.h postgresql-is_in_exclusive_backup-patch//src/include/catalog/pg_proc.h
--- postgresql/src/include/catalog/pg_proc.h 2012-01-26 23:01:32.340613398 +0100+++ postgresql-is_in_exclusive_backup-patch//src/include/catalog/pg_proc.h 2012-03-01 10:48:48.068473056 +0100@@ -2899,6 +2899,8 @@
DESCR("prepare for taking an online backup");
DATA(insert OID = 2173 ( pg_stop_backup PGNSP PGUID 12 1 0 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_stop_backup _null_ _null_ _null_ ));
DESCR("finish taking an online backup");
+DATA(insert OID = 3882 ( pg_is_in_exclusive_backup PGNSP PGUID 12 1 0 0 0 f f f t f v 0 0 16 "" _null_ _null_ _null_ _null_ pg_is_in_backup _null_ _null_ _null_ ));+DESCR("true if server is in on-line backup");
DATA(insert OID = 2848 ( pg_switch_xlog PGNSP PGUID 12 1 0 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_switch_xlog _null_ _null_ _null_ ));
DESCR("switch to new xlog file");
DATA(insert OID = 3098 ( pg_create_restore_point PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 25 "25" _null_ _null_ _null_ _null_ pg_create_restore_point _null_ _null_ _null_ ));
postgresql-exclusive_backup_start_time-patch.diff (text/x-patch, 5.1K) download
diff -ru postgresql/doc/src/sgml/func.sgml postgresql-exclusive_backup_start_time-patch/doc/src/sgml/func.sgml
--- postgresql/doc/src/sgml/func.sgml 2012-01-26 23:01:31.956613398 +0100+++ postgresql-exclusive_backup_start_time-patch/doc/src/sgml/func.sgml 2012-03-02 10:56:56.986487006 +0100@@ -14371,6 +14371,9 @@
<primary>pg_current_xlog_location</primary>
</indexterm>
<indexterm>
+ <primary>pg_exclusive_backup_start_time</primary>+ </indexterm>+ <indexterm>
<primary>pg_start_backup</primary>
</indexterm>
<indexterm>
@@ -14424,6 +14427,14 @@
</row>
<row>
<entry>
+ <literal><function>pg_exclusive_backup_start_time()</function></literal>+ </entry>+ <entry><type>timestamp with time zone</type></entry>+ <entry>Get start time of an on-line exclusive backup in progress.+ </entry>+ </row>+ <row>+ <entry>
<literal><function>pg_start_backup(<parameter>label</> <type>text</> <optional>, <parameter>fast</> <type>boolean</> </optional>)</function></literal>
</entry>
<entry><type>text</type></entry>
diff -ru postgresql/src/backend/access/transam/xlogfuncs.c postgresql-exclusive_backup_start_time-patch/src/backend/access/transam/xlogfuncs.c
--- postgresql/src/backend/access/transam/xlogfuncs.c 2012-03-02 15:21:42.278479256 +0100+++ postgresql-exclusive_backup_start_time-patch/src/backend/access/transam/xlogfuncs.c 2012-03-02 15:17:50.390479369 +0100@@ -28,6 +28,7 @@
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/timestamp.h"
+#include "storage/fd.h"
/*
* pg_start_backup: set up for taking an on-line backup dump
@@ -465,3 +466,71 @@
{
PG_RETURN_BOOL(RecoveryInProgress());
}
+++/*+ * Returns start time of an online exclusive backup.+ *+ * When there's no exclusive backup in progress, the function+ * returns NULL.+ */+Datum+pg_exclusive_backup_start_time(PG_FUNCTION_ARGS)+{+ TimestampTz xtime;+ FILE *lfp;+ char fline[MAXPGPATH];+ char backup_start_time[30];++ /*+ * See if label file is present+ */+ lfp = AllocateFile(BACKUP_LABEL_FILE, "r");+ if (lfp == NULL)+ {+ if (errno != ENOENT)+ ereport(ERROR,+ (errcode_for_file_access(),+ errmsg("could not read file \"%s\": %m",+ BACKUP_LABEL_FILE)));+ PG_RETURN_NULL();+ }++ /*+ * Parse the file to find the the START TIME line.+ */+ backup_start_time[0] = '\0';+ while (fgets(fline, sizeof(fline), lfp) != NULL)+ {+ if (sscanf(fline, "START TIME: %25[^\n]\n", backup_start_time) == 1)+ break;+ }++ /*+ * Close the backup label file.+ */+ if (ferror(lfp) || FreeFile(lfp)) {+ ereport(ERROR,+ (errcode_for_file_access(),+ errmsg("could not read file \"%s\": %m",+ BACKUP_LABEL_FILE)));+ }++ if (strlen(backup_start_time) == 0) {+ ereport(ERROR,+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),+ errmsg("invalid data in file \"%s\"", BACKUP_LABEL_FILE)));+ }++ /*+ * Convert the time string read from file to TimestampTz form.+ */+ xtime = DatumGetTimestampTz(+ DirectFunctionCall3(timestamptz_in, CStringGetDatum(backup_start_time),+ ObjectIdGetDatum(InvalidOid),Int32GetDatum(-1))+ );+++ PG_RETURN_TIMESTAMPTZ(xtime);+}+
diff -ru postgresql/src/include/access/xlog_internal.h postgresql-exclusive_backup_start_time-patch/src/include/access/xlog_internal.h
--- postgresql/src/include/access/xlog_internal.h 2012-01-26 23:01:32.332613398 +0100+++ postgresql-exclusive_backup_start_time-patch/src/include/access/xlog_internal.h 2012-03-02 11:04:48.378486777 +0100@@ -281,5 +281,6 @@
extern Datum pg_xlog_replay_pause(PG_FUNCTION_ARGS);
extern Datum pg_xlog_replay_resume(PG_FUNCTION_ARGS);
extern Datum pg_is_xlog_replay_paused(PG_FUNCTION_ARGS);
+extern Datum pg_exclusive_backup_start_time(PG_FUNCTION_ARGS);
#endif /* XLOG_INTERNAL_H */
diff -ru postgresql/src/include/catalog/pg_proc.h postgresql-exclusive_backup_start_time-patch/src/include/catalog/pg_proc.h
--- postgresql/src/include/catalog/pg_proc.h 2012-01-26 23:01:32.340613398 +0100+++ postgresql-exclusive_backup_start_time-patch/src/include/catalog/pg_proc.h 2012-03-02 11:02:45.346486836 +0100@@ -2899,6 +2899,8 @@
DESCR("prepare for taking an online backup");
DATA(insert OID = 2173 ( pg_stop_backup PGNSP PGUID 12 1 0 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_stop_backup _null_ _null_ _null_ ));
DESCR("finish taking an online backup");
+DATA(insert OID = 3882 ( pg_exclusive_backup_start_time PGNSP PGUID 12 1 0 0 0 f f f t f s 0 0 1184 "" _null_ _null_ _null_ _null_ pg_exclusive_backup_start_time _null_ _null_ _null_ ));+DESCR("start time of an online backup");
DATA(insert OID = 2848 ( pg_switch_xlog PGNSP PGUID 12 1 0 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_switch_xlog _null_ _null_ _null_ ));
DESCR("switch to new xlog file");
DATA(insert OID = 3098 ( pg_create_restore_point PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 25 "25" _null_ _null_ _null_ _null_ pg_create_restore_point _null_ _null_ _null_ ));
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: gilles.darold@dalibo.com, magnus@hagander.net, masao.fujii@gmail.com, mailings@oopsware.de, euler@timbira.com, marti@juffo.org
Subject: Re: Patch pg_is_in_backup()
In-Reply-To: <4F50E1B0.90808@dalibo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox