From: sgccdeb@citec.oz.au (Darren Bock 231 9716)
Subject: Re: database locations/areas in UNIX
Organization: CITEC
Date: Wed, 30 Sep 1992 00:37:54 GMT

cs_mj@gsbacd.uchicago.edu (Mark Jaeger) writes:

>RFI: how to configure locations/areas for UNIX

>I am looking for some guidance on how to configure INGRES database
>locations and areas under UNIX.  I think we have a pretty good scheme
>for VMS, but UNIX is quite different.

>We use a scheme where we create new locations for every database.
>Database "foo" might have these locations (and areas under VMS):

>	type		location	area		logical translation
>	===========	===========	===========	====================
>	data		db_foo		db_foo		disk1:[xxingres.]
>	data		db_foo_1	db_foo_1	disk2:[xxingres.]
>	data		db_foo_2	db_foo_2	disk3:[xxingres.]
>	checkpoint	ckp_foo		ckp_foo		disk4:[xxingres.]
>	journal		jnl_foo		jnl_foo		disk4:[xxingres.]
>	dump		dmp_foo		dmp_foo		disk4:[xxingres.]

>For example, the actual files for the database tables reside in a
>directory called "disk1:[xxingres.ingres.data.foo]".

>What schemes are people using?


We have a medium size Ingres installation here and have implemented a similar
scheme under UNIX. When we first came up with the idea Ingres was horrified.
After a while they clarified exactly where a symbolic link would work.
( Ingres uses '..' to move around within a database location )

Unofficially from Ingres :

	If a symbolic link points to the top level of a database location
	it is ok. This is not advisable for "non-experts" to try.

eg:   in iidbdb the database location db_foo_1 is located in unix directory
      /ingres6/locations/db_foo_1

      /ingres6/locations/db_foo_1 is a symbolic link pointing to the normal
      directory tree for a location

   > ls -lL /ingres6/locations/db_foo_1
   lrwxrwxrwx   1 ingres   ingres         0 Sep 15 11:25 db_foo_1 -> /db_foo_1
   > ls -la /db_foo_1
   total 3
   drwxr-xr-x   3 ingres   ingres       768 Feb  6  1992 .
   drwxr-xr-x   8 root     root        2048 Mar 11  1992 ..
   drwxr-xr-x   3 ingres   ingres       768 Feb  6  1992 ingres
   > ls -l /db_foo_1/ingres
   total 1
   drwx------   3 ingres   ingres       768 Feb  6  1992 data
   > ls -l /db_foo_1/ingres/data
   total 1
   drwxrwxrwx   5 ingres   ingres      1280 Sep 15 12:02 default
   > ls -l /db_foo_1/ingres/data/default
   total 2
   drwxrwxrwx   2 ingres   ingres     28160 Sep 15 12:04 foo


To explain the example, 
	
	1)  We have adopted the convention of having all of our symbolic
	    links for locations in our ingres installation
		Ingres is installed under /ingres6
		The symbolic link directory is /ingres6/locations

	2)  Each database area has its own filesystem

	    This seen above as the filesystem /db_foo_1, which contains the 
	    database foo, data area 1

	3)  Similarly for extra data locations, journal, checkpoint and dump
	    locations


Points of interest:

	1)  Each filesystem is allocated to a single database
		- cascading problems cannot happen
		  (eg: a minor production database fills the data area,
		       causing a prominent database to become corrupted)
		- any set of database users can corrupt their own database
		  as often as they like
		    - they can only affect themselves
		    - they only have themselves to blame
		- recovery of their database can be scheduled depending on how
		  urgent it is (in the overall scheme of things)
		- it forces people to plan ahead and request/pay for disk
		  space based on the growth of their data
	2)  We wished to use dump & restore for our backups
	    With each database location on a separate filesystem, backup and
	    recovery using dump/restore is simplified
		- it is not very meaningful (in our case) to make an
		  incremental backup of a data area
		- incremental backups can be made of other non-database areas
		  if necessary
	3)  This system requires a very flexible way of configuring disk
	    partitions to non-standard sizes (or wasting a lot of disk space)

This scheme has worked quite well for us and we have made use of it to test
recovery of our backups. On occasion we have even had to use it to keep a
database alive and running after a disk crash.

One idea that has made our life easier after a disk crash is to have a parallel
copy of the directory structure of each database location (with no files).
When a disk crashes and a new disk is installed all I have to do is a 'newfs'
then copy the directories across and restore the checkpoint and we are back
up and running.


					Hope this is of some use ;-)

							Darren Bock
--
Darren Bock			|
Dept DEVETIR, Qld, Australia	|  The mind is its own illusion
sgccdeb@citecuc.citec.oz.au	|   it creates far more problems than
ph:  612 07 227 5726		|    it could possibly solve in one lifetime
fax: 612 07 221 1002		|