Re: Tablespaces

Oliver Elphick wrote:
> On Wed, 2004-03-03 at 04:59, Tom Lane wrote:
> 
>>   What might make sense is some sort of marker file in a
>>tablespace directory that links back to the owning $PGDATA directory.
>>CREATE TABLESPACE should create this, or reject if it already exists.
> 
> 
> It will not be enough for the marker to list the path of the parent
> $PGDATA, since that path might get changed by system administration
> action.  The marker should contain some sort of unique string which
> would match the same string somewhere in $PGDATA.  Then, if either
> tablespace or $PGDATA were moved, it would be possible to tie the two
> back together.  It wouldn't be an issue on most normal systems, but
> might be of crucial importance for an ISP running numerous separate
> clusters.

Taking this one step further would be to do something like Oracle does.  Every datafile in Oracle (because the Oracle
storagemanager stores 
 
multiple objects inside datafiles, one could say there is some 
similarity between Oracle datafiles and the proposed pg tablespaces), 
has meta info that tells it which database instance it belongs to and 
the last checkpoint that occured (It might actually be more granular 
than checkpoint, such that on a clean shutdown you can tell that all 
datafiles are consistent with each other and form a consistent database 
instance).  So Oracle on every checkpoint updates all datafiles with an 
identifier.  Now you might ask why is this useful.  Well in normal day 
to day operation it isn't, but it can be usefull in disaster recovery. 
If you loose a disk and need to restore the entire database from backups 
it can be difficult to make sure you have done it all correctly (do I 
have all the necessary files/directories? did I get the right ones from 
the right tapes?)  Especially if you have directories spread across 
various different disks that might be backed up to different tapes.  So 
by having additional information stored in each datafile Oracle can 
provide additional checks that the set of files that are being used when 
the database starts up are consistent and all belong together.  Oracle 
also ensures that all the datafiles that are suposed to exist actually 
do as well.

So what might this mean for postgres and tablespaces?  It could mean 
that on startup the database checks to verify that all the tablespaces 
that are registered actually exist.  And that the data in each 
tablespace is consistent with the current WAL status.  (i.e. someone 
didn't restore a tablespace from backup while the database was down that  is old and needs recovery.

A lot of what I am talking about here become PITR issues.  But since 
PITR and tablespaces are both potential features for 7.5, how they 
interact probably should be thought about in the designs for each.

thanks,
--Barry