Integrated Backup Service?

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;