Erik Jones wrote:
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.
Just the tablespace folder or the whole data folder?
If you have the whole data folder then there will be a few other folders
base
global
pg_log
pg_tblspc
pg_twophase
pg_xlog
and probably a few config files - pg_hba.conf postgresql.conf ...
If you do have the *whole* data folder then it will be rather easy, if
you don't - then in theory you can, but in practice.... there *might* be
a *small* chance of getting it if you want to play around a bit, chances
are you will give up in frustration.
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.
If you only have the data files then you need to reconstruct the
structure data to match the old db. The order and type of the columns
needs to match the old db. The oid's should match the old db. In theory
you could change the oid's in the catalog entries to point to the data
files you have. Toast table oid need to match up as well.
You will want a good understanding of how the pg_catalog tables data
links together and ties into the data files etc...
How bad do you want the data???
To start with you will need the same pg version as the old db. Whether
it was 32 or 64 bit *must* match up. I don't think it is as important
but you might want the system to be close to the same.
If you play around enough to get it to read then you will want to do a
dump and then remove it all and start clean.
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
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
