Postgres as backend to Backup System

["Kent L. Nasveschuk" <knasveschuk@xxxxxxxxxxxxxxxxx>]

Hello,
I don't know if this is the forum for this but here goes.

I am interested in using Postgres as the backend to a backup system.
Does anyone have any experiences or ideas on this? I want to use
Postgres to store information about files, directories, archives etc
written to tape. This is the typical types of information that I feel
need to be stored in postgres:

Tape ID
Location of tape in autoloader magazine
Directory file sizes
Total Archives on tape
Total bytes in archive
Archive location of a file or directory on a tape
Total bytes on tape
Date archive was written to tape
Server associated with an archive
Absolute path to file or directory on tape


My log files are generated by using the "v" option of the "tar" command.
These create daily log files that are 6-8 mb that list every file that
is backed up. This comes out to 75,000 lines per day. If you had an
autoloader that you cycled through with 10 tapes for example, that could
contain 750,000 entries.

My system backups up anything that can run rsync. For me right now that
is Linux servers, Novell servers, MAC running OSX, and Windows servers.
Because there are many types of servers the database should be able to
store which server,archive number a file or directory is in. 

If you were to search in the database for a file or directory, it would
return a list that gave you the tape(s), date(s), archive(s) number on
tape, etc.

Commercial systems use backend SQL servers. I believe Veritas Backup
Exec uses MSSQL, Arcserve uses a backend database (don't know the type).

Any ideas would be appreciated.

This is what I have so far:

# Database for backup system

create sequence ftid_seq start 1 increment 1;
create sequence did_seq start 1 increment 1;
create sequence archiveid_seq start 1 increment 1;
create sequence deviceid_seq start 1 increment 1;
create sequence tid_seq start 1 increment 1;
create table tapedevice(
        deviceid integer not null default nextval('deviceid_seq'::text),
        server varchar(20) not null,
        devicename varchar(20),
	numtapes integer,
	drivename varchar(20),
	autoloader boolean,
        constraint deviceid_pk primary key(deviceid)
);

create table tapes(
        tid integer not null default nextval('tid_seq'::text),
        tapeid varchar(20) not null,
        numwrittento integer,
	currslot integer,
	deviceid integer references tapedevice(deviceid)
		match full
		on update cascade
		on delete cascade,
	totalbytes int8,
	numberarchives integer,
        constraint tid_pk primary key(tid)
);

create table filetable(
        ftid int8 not null default nextval('ftid_seq'::text),
        archiveid integer not null references tapes(tid)
		match full
		on update cascade
		on delete cascade,
        absolutepath varchar(200),
        constraint ftid_pk primary key(ftid)
);

create table directorytable(
        did int8 not null default nextval('did_seq'::text),
        archiveid integer not null references tapes(tid)
		match full
		on update cascade
		on delete cascade,
        absolutepath varchar(200),
        constraint did_pk primary key(did)
);

create table archives(
        archiveid integer not null default
nextval('archiveid_seq'::text),
	tid integer references tapes(tid)
		match full
		on update cascade
		on delete cascade,
        totalbytes int8 not null,
        totalfiles int8,
	archivestartdir varchar(100),
	fromserver varchar(20),
	datewritten date,
        constraint archiveid_pk primary key(archiveid)
);


-- 
Kent L. Nasveschuk <knasveschuk@xxxxxxxxxxxxxxxxx>