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

Patch pg_is_in_backup()

From: Gilles Darold <gilles(dot)darold(at)dalibo(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Patch pg_is_in_backup()
Date: 2012-01-30 18:33:53
Message-ID: 4F26E291.9020605@dalibo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello,

Yesterday I was facing a little issue with a backup software (NetBackup)
that do not report error when a post backup script is run. The problem
is that this script execute pg_stop_backup() and if there's any failure
PostgreSQL keeps running in on-line backup mode. So the backup is not
completed and the next one too because the call to pg_start_backup()
will fail.

After some time searching for a Pg system administration function like
pg_is_in_recovery(), let's say pg_is_in_backup(), I couldn't find one.
The minor patch attached here adds this administrative function that can
be used with others backup control functions. This is a very little
patch outside documentation because the function is only a wrapper to
the internal BackupInProgress() function, just like pg_is_in_recovery()
is calling RecoveryInProgress().

I hope it could be included as it could help to save time for some
sysadmin who want to monitor on-line backup process and that do not have
SQL knowledge. Saying that it's always possible to check if the
backup_label file exist under PGDATA but this need to be checked on the
host with postgres priviledge. The other way is to create a plpgsql
function with security definer that will have the same effect than the
patch above except that it need some SQL knowledge. I've give it here
for web search, thanks to Guillaume and Marc.

CREATE OR REPLACE FUNCTION pg_is_in_backup ( )
RETURNS BOOLEAN AS $$
DECLARE is_exists BOOLEAN;
BEGIN
        -- Set a secure search_path: trusted schemas, then 'pg_temp'.
        PERFORM pg_catalog.set_config('search_path', 'pg_temp', true);
        SELECT ((pg_stat_file('backup_label')).modification IS NOT NULL)
        INTO is_exists;
        RETURN is_exists;
EXCEPTION
WHEN undefined_file THEN
  RETURN false;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Regards,

-- 
Gilles Darold
http://dalibo.com - http://dalibo.org


Attachment: postgresql-is_in_backup-patch.diff
Description: text/x-patch (3.2 KB)

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2012-01-30 18:35:21
Subject: Re: [GENERAL] Why extract( ... from timestamp ) is not immutable?
Previous:From: Robert HaasDate: 2012-01-30 18:27:50
Subject: Re: [COMMITTERS] pgsql: Make group commit more effective.

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