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.org