Re: [SQL] Postgres as backend to Backup System

Hello,

I use mtx and mt commands to load and unload tapes from the autoloader
and the drive unit. The system has evolved and is fairly elaborate. This
is an automated cron job that rotates through the magazine, writing to
tape sending me the log files that describes what archives, files and
directories are on any particular tape.

For the MACs OSX I run a cron job that creates tar archives. Rsync comes
on OSX. You can't use the normal tar command because it deletes the
resource forks that the MAC file system uses. There is a hacked version
available on the Internet that is called hfstar and works exactly the
same way. I grab these archives off hours using rsync and send them to
tape.

A version of cron and rsync also works on Netware 5.1. I use cron to
shutdown services and restart after they have been backed up. Rsync
works the same as on Linux.

The problem that I ran into is that all of thes log files are mailed to
me on a continual basis. I am familiar with how everything goes
together, but it isn't accessible to anyone else. I wanted to develop a
web based basckup system with backend database using Postgres to store
information about the location of files on tapes.

I also wanted to make the scope of this broad enough so that if you had
different types of autoloaders or stand alone tape drive at multiple
locations you could store that information in there also.

On Tue, 2004-04-27 at 10:15, Theodore Petrosky wrote:
> Kent,
>
> I am very interested in this project. I am setting up
> a job tracking system (postgresql as the backend) for
> an ad agency and I have been looking at archiving the
> work files. (All Mac OS X) How are you talking to the
> tape juke box?
>
> Postgresql would be great for your project.
>
> JMHO
>
> Ted
>
> --- "Kent L. Nasveschuk"
> <knasveschuk(at)wareham(dot)k12(dot)ma(dot)us> wrote:
> > 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(at)wareham(dot)k12(dot)ma(dot)us>
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> > send an appropriate
> > subscribe-nomail command to
> > majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Win a $20,000 Career Makeover at Yahoo! HotJobs
> http://hotjobs.sweepstakes.yahoo.com/careermakeover
--
Kent L. Nasveschuk <knasveschuk(at)wareham(dot)k12(dot)ma(dot)us>