Skip site navigation (1) Skip section navigation (2)

Re: Patch pg_is_in_backup()

From: Gilles Darold <gilles(dot)darold(at)dalibo(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Magnus Hagander <magnus(at)hagander(dot)net>, Fujii Masao <masao(dot)fujii(at)gmail(dot)com>, Bernd Helmle <mailings(at)oopsware(dot)de>, Euler Taveira de Oliveira <euler(at)timbira(dot)com>, Marti Raudsepp <marti(at)juffo(dot)org>
Subject: Re: Patch pg_is_in_backup()
Date: 2012-03-02 15:05:20
Message-ID: 4F50E1B0.90808@dalibo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Le 03/02/2012 10:52, Magnus Hagander a écrit :
> On Fri, Feb 3, 2012 at 10:47, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> On Fri, Feb 3, 2012 at 6:10 PM, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
>>>
>>> --On 3. Februar 2012 13:21:11 +0900 Fujii Masao <masao(dot)fujii(at)gmail(dot)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


Attachment: postgresql-exclusive_backup_start_time-patch.diff
Description: text/x-patch (5.1 KB)
Attachment: postgresql-is_in_exclusive_backup-patch.diff
Description: text/x-patch (3.4 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2012-03-02 15:07:41
Subject: Re: review: CHECK FUNCTION statement
Previous:From: Alvaro HerreraDate: 2012-03-02 14:46:27
Subject: Re: review: CHECK FUNCTION statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group