On Fri, 14 Jan 2005, Tom Lane wrote:
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:course that won't work, since its link'd to the oid of the table name :( whose idea was this "let's name the files by the OID" again? :(
Actually, I think you can make this work, if you are sure of the schema of the old database. Try something like this:
* Continue to work in the same installation; don't initdb. If you did initdb then old transaction numbers would be wrong. Just create a new database beside the old one (or maybe better, physically copy the old one someplace and then drop and re-createdb it).
* Rebuild the schema. Now you have a lot of empty tables and you just have to get the old data into them. That means you have to find out the mapping from old table filenode numbers to new ones.
* To find out the old numbers, make a user table that has the identical schema to pg_class (probably easiest to do this with the LIKE clause of CREATE TABLE). Check its relfilenode number in pg_class, then copy the old database's pg_class file over that relfilenode. Now you can query this table to see the contents of the old pg_class.
* Join the new and old pg_class together to get corresponding relfilenode numbers.
* Copy old table files into new database per the above. (I'd make a script to do this instead of doing it by hand...) Also you'll need to copy corresponding TOAST tables. Don't copy indexes though.
'k, this is looking promising ... but I'm a bit confused on the TOAST tables ... I can't match on 'relname', since they aren't the same ... the old has, for instance:
pg_toast_5773565
while the new has:
pg_toast_8709712
is there some sort of 'linkage' in pg_class that I'm not seeing? since new is finding 21 rows, and old is only finding 20, I can't imagine its safe to assume that the 'order of creation' will be safe to match on ...
---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
