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

Integrated Backup Service?

From: "Richard Sydney-Smith" <richard(at)ibisaustralia(dot)com>
To: <pgsql-hackers-win32(at)postgresql(dot)org>
Subject: Integrated Backup Service?
Date: 2004-04-09 05:56:14
Message-ID: 000d01c41df7$60479c90$48cd8690@athlon2000 (view raw or flat)
Thread:
Lists: pgsql-hackers-win32
I included some routines to enforce backups after the postmaster was halted by a windows crash and the clog files were damaged and I was not able to restore.

In an effort to make the application environment as robust as possible I created two procedures: one  to backup , one to restore data to directory %PGHOME%/bkp/<databasename>. Presently these procedure are driven by code within the various applications.

However backups are a fundamental requirement. Especially if a system halt can cause irrecoverable damage to the datacluster.

Would people consider it a terrible idea to build this sort of functionality into the w32 service?

My suggestion would be to include a text file in each directory with the data/time of the last backup and the system administrator can set a maximum allowable "days between backup". If the interval is exceeded by any particular database then the server would perform the backup transparently on startup. Ideally the dbschema should also be saved but I do not know how to do that yet.

Procedure code is attached in case this is of interest:

best regards

Richard.

==========================================================================================

CREATE OR REPLACE FUNCTION public.backup_database(text)
  RETURNS text AS
'
declare 
tblname record;
cnt record;
dirchar varchar := \'/\'; --char(92); --\'/\'; -- directory separator character, char 92 is backslash or / for windows

tname varchar :=\'\';
tquery varchar :=\'\';
filename varchar :=\'\';

begin
if $1 <> \'\' then
   tname := dirchar||$1||dirchar;
else
   tname := dirchar;
end if;

for tblname in select tablename from pg_tables WHERE not(tablename like \'pg_%\') and not(tablename like \'t_%\') 
and not(tablename like \'%_list\') order by tablename  loop
   filename := tname|| lower(tblname.tablename)||\'.dat\';
   tquery := \'copy \' || tblname.tablename || \' to \' || quote_literal(filename)||\' with binary \';
   execute tquery;
end loop;

return tquery;
end;
'
  LANGUAGE 'plpgsql' VOLATILE;


CREATE OR REPLACE FUNCTION public.restore_database(text)
  RETURNS text AS
'
declare 
tblname record;
cnt record;
tname varchar :=\'\';
tquery varchar :=\'\';
filename varchar :=\'\';

begin
tname := \'\';
for tblname in select tablename from pg_tables WHERE not(tablename like \'pg_%\') and not(tablename like \'t_%\') 
and not(tablename like \'%_list\') order by tablename  loop
   filename := \'/\'||$1||\'/\'|| lower(tblname.tablename)||\'.dat\';
   tquery := \'copy \' || tblname.tablename || \' from \' || quote_literal(filename)||\' with binary \';
   execute tquery;
end loop;

return tquery;
end;
'
  LANGUAGE 'plpgsql' VOLATILE;

pgsql-hackers-win32 by date

Next:From: Bruce MomjianDate: 2004-04-09 14:45:19
Subject: Re: pg_ctl porting
Previous:From: Claudio NatoliDate: 2004-04-09 05:05:38
Subject: Re: pg_ctl porting

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