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

"Temp key: basic, via spamcop" <pg-gts@snkmail.com> writes:
> 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.

Yeah, that's not gonna work.  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.

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.

Tablespaces are handy for spreading your data across more than one
physical filesystem, but they're no substitute for proper backup
procedures.  However, given that you said you had Time Machine backups,
you might be able to get someplace by restoring both your own tablespace
and the system database files from those backups.  Be sure to restore
all of this stuff from the same TM snapshot, and if possible pick a
snapshot where the database would've been totally idle while TM was
running.  The key thing to make this work is that all files belonging
to the database have to be in sync, no matter which tablespace they were
in.  You don't have to overwrite the system database files if you'd
rather not: just restore all these files somewhere where you have enough
space, fix the tablespace symlink appropriately, and then fire up a
second 9.0.x postmaster pointing it at the restored main data directory.
That should get you to a point where you can pg_dumpall and restore into
your new 9.2 postmaster.  (For safety's sake I'd recommend a dump and
restore rather than trying to do something like an upgrade-in-place.)

> -   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?

+1 for running a second installation as a better practice for this.
I've heard 5433 (one more than the default) as a common
second-postmaster port, but there's not really any convention.

            regards, tom lane