Restoring data from TABLESPACE files

From: "Temp key: basic, via spamcop" <pg-gts(at)snkmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Restoring data from TABLESPACE files
Date: 2012-11-11 12:47:08
Message-ID: 22511-1352638029-694539@sneakemail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Context:
OSX LIon Server 10.7.5; system supplied PostgreSQL 9.0.5;
user data on dedicated drive via TABLESPACE,
i.e., keeping my stuff away from the OS data tables
Backup via TimeMachine on hourly basis (i.e., file system backup)
TODO: included determining a "better way" of backing up but
delayed/inhibited by concerns that restoring pg_dump files would
have to restore the OS defined tables which did not seem to be the
correct thing to do.

Scenario:
Dedicated drive (Mac Mini Server 2x 500G: internal drive) failed,
and computer returned to Apple for warranty repair.
Another computer was reconfigured by restoring from backups, and all files returned,
Except I had trouble getting a suitable drive for the TABLESPACE data (rural location).
Once the TABLESPACE files were restored and the database
stopped/started etc. they were not mounted/recognized by postgres.

Checked:
entry in postres.db_tablespace.splocation column is correct (note with approval 9.2 change)
symlink is correct, i.e., same as above
header folder: PG_9.0_201008051 present and populated
Permissions correct and all files owned by _postgres:_postgres
PgAdmin3 reports the TABLESPACE as being present, but none of its defined roles or tables

Questions:
1. What other entries do I need to check/adjust to get the data files mounted?
2. Is there a tool that can read the system files and reconstruct the data they contain?
3. Which commercial services offer data recovery in this context?

So far I have:
- Taken on board all the on-line advice about a better backup system,
thank you in advance if you feel the need to repeat any/all of this, I
have no basis for complaint;
- Searched for strategies via Google and the mailing list archives, and my
impression is this is not going to be easy;
- Found a passing mention of something like "Tom Lane's data reader
unsure of name" but have been unable to locate this utility.
Recasting Q2 above: Does it exist?
- Started a 9.2 installation on the dedicated drive to house the
restored/recreated data, which leads to a "good practice"
supplementary question:
Is there a convention for the second port number, e.g., 6543 as per examples?

Thank you for your time.

Regards
Gavan Schneider

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2012-11-11 16:39:27 Re: Restoring data from TABLESPACE files
Previous Message Temp key: basic, via spamcop 2012-11-11 04:55:51 Restoring data from TABLESPACE files