Re: Restoring data from TABLESPACE files

"Temp key: basic, via spamcop" <pg-gts@snkmail.com> writes:
> Since "CREATE TABLESPACE" is a PostgreSQL extension I would love
> to see the concept fully populated with statements along the
> lines of:
>      MOUNT TABLESPACE <name> [AT <file system location>]
> (default is prev. known location)
>      UNMOUNT TABLESPACE <name> [IF EXISTS] (data is preserved)

That is quite unlikely to happen, unfortunately.  For that to work,
tablespaces would have to be completely independent, ie each with its
own system catalogs, WAL stream and XID counter.  Quite aside from any
efficiency problems with that, it'd be impossible to guarantee atomic
commit for any transaction that changed data in more than one
tablespace, since there's no way to be sure that commit records in
multiple WAL streams would all reach disk together.  This is pretty much
the same reason why we don't have anything like mount/dismount for
databases --- a database does have its own system catalogs, which would
address the first part of the problem, but it still shares WAL and XID
infrastructure with the other databases in the installation.

> I really can't fully understand what is happening here. I have
> walked my system through time with both "surgical" restoration
> of the main data directory and contemporaneous tablespace files;
> and, full "point in time" restoration of the entire system. No
> joy. Files present, fully restored but no recognition of the
> data in the TABLESPACE by postgres.

Hard to tell what's going wrong on the basis of that much information.
If you have matching backup copies of the main data directory and the
tablespace directory, then in principle it should work, but I can't
debug "no joy".

            regards, tom lane