Обсуждение: Importing PostgreSQL data from another database
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.
Thanks,
Chris Ewing
Visit our website at http://www.halcrow.com ------------------------------------------------------------------------ Halcrow Group Limited. Registered office: Vineyard House, 44 Brook Green
The contents of this email are confidential, for the sole use
of the intended recipient at the email address to which it has
been addressed and do not give rise to any binding legal
obligation upon Halcrow companies unless subsequently confirmed
on headed business notepaper sent by fax, letter or as an email
attachment. Whilst reasonable care has been taken to avoid virus
transmission, no responsibility for viruses is taken and it is
your responsibility to carry out such checks as you feel
appropriate. Emails supplied are as found and there's no
guarantee that the messages contained within the body of the
email have not been edited after receipt. If you receive this
email in error, please contact the sender immediately and delete
the message from your system.
Thank you.
London, W6 7BY. Registered in England and Wales, Number 3415971.
-------------------------------------------------------------------------
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@myemma.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
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