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 storage manager 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