On Fri, 14 Jan 2005, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@postgresql.org> 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: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664