Re: Importing PostgreSQL data from another database

On Jan 9, 2008, at 8:06 AM, Ewing, Chris wrote:

> Hi Everyone,
>
> I am trying to import data from a backed up PostgreSQL tablespace.
> The server which the original data was on has been wiped.
>
> 1. I saved the tablespace onto a portable harddrive from the old
> server. This contains the tablespace folder (with PG_VERSION file)
> and a folder named 225809. This folder contains about 300gb of info.
>
> 2. Now I want to view the data on another computer. I am not sure
> of the best way to do this. I have tried to create a new tablespace
> and copy the data into it - but the OID is different and so the
> data cannot be read.
>
> 3. I realise now that I should have used the backup/restore
> function in PGAdmin III. I did not do this. Is there anyway to
> retrieve the data so that I can view it again?
>
> I hope you can help and I hope this makes sense.
>

I'm sorry to say, but I think you're SOL. Without the catalogs that
had the info on the tables in that tablespace, another pg cluster has
no way of knowing what's in the tablespace. Filesystem backups/
xfers are possible, but that requires copying the entire cluster
directory. If all you want is a specific tablespace, then pg_dump is
your friend.

Lesson to take from this: NEVER delete the original without
validating the backup.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com