Subject: Re: [SQL] Postgres as backend to Backup System
From: "Kent L. Nasveschuk" <knasveschuk@wareham.k12.ma.us>
To: Michael Satterwhite <michael@weblore.com>
Cc: Postgres SQL <pgsql-sql@postgresql.org>,
	Postgres PHP <pgsql-php@postgresql.org>
In-Reply-To: <200404270938.18291.michael@weblore.com>
References: <1083055182.2106.16.camel@1134>
	<200404270938.18291.michael@weblore.com>
Content-Type: text/plain
Organization: 
Message-Id: <1083092352.2081.47.camel@1134>
Mime-Version: 1.0
Date: 27 Apr 2004 14:59:12 -0400
Content-Transfer-Encoding: 7bit

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>