Re: Restoring data from TABLESPACE files - Mailing list pgsql-novice

Thank you Tom, for your reply on Sunday, November 11, 2012.

Tom Lane wrote:
>
>...  There are two big things in your way (and maybe some smaller ones
>I'm forgetting):
>
>1. The catalog information describing your tables would not have been in
>the tablespace directory; it would've been in the system catalog tables,
>which were on the main drive in the scenario you've described.  So even
>after doing a CREATE TABLESPACE, the database server wouldn't know
>what's out there.
>
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)

(Or the TABLESPACE concept could get folded back onto the
DATABASE syntax along the lines above, so they have a robust
representation in the file-system which can be moved/removed/returned.)

I feel the TABLESPACE data instance should be robust and much
more self-contained. With this in place you could get radical
and be able to walk data around on a (thumb) drive, and remove
it after use. Restoration and back-up strategies also become a
lot less complicated, and should be much more reliable. (I do
have some insight into how many things "under the hood" would
have to change so I'm not holding my breath on this. :)

Is this a sufficient mention for a "feature request" or do I
need to repeat it in a more formal manner?

>2. The XID counter of the new installation is nowhere near what it
>likely was in the old one.  So even if you got past #1, all the tables
>would probably appear to be empty because their contents are all "in the
>future" according to the MVCC rules.
>
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.

I have learnt a lot about the OSX installation of PostgreSQL.
(And I need to get a faster backup drive, each full restoration
cycle took ages :-(

As a simple person I still can't figure why the db engine
doesn't use the TABLESPACE when the relevant OID is well in the
past since it would seem to me that the main table already has
all the defining information. Or does the engine drop this
information if inconsistencies are found?

>Tablespaces are handy ...
>
but, to my mind are still too fragile.


Anyway, my last chance before recreating the data from scratch...

Is there an extraction tool that could pull data directly from
the TABLESPACE files?

Any other ideas?


Thanks again everyone for your time.

Regards
Gavan Schneider