Thread: Postgres as backend to Backup System
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@wareham.k12.ma.us>
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@wareham.k12.ma.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@wareham.k12.ma.us>
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> > send an appropriate
> > subscribe-nomail command to
> > majordomo@postgresql.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@wareham.k12.ma.us>
Michael, Thanks for responding. On Tue, 2004-04-27 at 10:38, Michael Satterwhite wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Tuesday 27 April 2004 03:39, Kent L. Nasveschuk 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. > > > > I'm doing this now using MySQL (I'm converting it to Postgres). A couple of > thoughts on your structure. > Good idea I'll eliminate it. > You really don't need to store total archives or total bytes as this can be > retrieved at any time by select sum(). > Now, do you keep any information on tapes that have been overwritten? Are you writing to more than one tape in a backup session? What else does your controls table store? > I have a controls table that gives me (among other things) the maximum number > of backup sets to keep. That allows me to automatically cycle through the > sets. > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) > > iD8DBQFAjnBZjeziQOokQnARAgMVAKCXRuMJYTpvAp6w6xeCePdt1AG+sQCeL0ij > 2Jg64Fhsu8FIstI8Rm2Tuio= > =Yid4 > -----END PGP SIGNATURE----- -- Kent L. Nasveschuk <knasveschuk@wareham.k12.ma.us>