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