backup tableoids (pg_class.oid) ?

On Mon, Feb 04, 2008 at 04:14:12PM +0100, fkater@googlemail.com wrote:

Hi,

my db structure relays on the OIDs of tables (stored as OIDs of
pg_class).

However, 'pg_dumpall -o ...' seems to save the oids of all data but not
the tableoids, so, when I restore data I get different tableoids than I
had before!

Is there anything I could do to backup and restore even the tableoids
(besides stopping server and copying the files) ?

No. When creating a table there is no way to specify the OID, hence
when restoring a dump there is no way to preserve them. This was never
guarenteed, the OID saving was for large objects and rows, not table
oids.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

fkater@googlemail.com wrote:

Hi,

my db structure relays on the OIDs of tables (stored as OIDs of
pg_class).

Probably it would be a better idea to store that as type regclass
instead of OIDs. I think that will take care of the dump part of the
problem, at least. It's likely that there will be an additional hurdle
when trying to restore, because the referenced table would not have been
created in the first place.

That said, IMHO the idea of storing metadata as part of data is a bad
idea.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:

fkater@googlemail.com wrote:

my db structure relays on the OIDs of tables (stored as OIDs of
pg_class).

Probably it would be a better idea to store that as type regclass
instead of OIDs. I think that will take care of the dump part of the
problem, at least.

+1

It's likely that there will be an additional hurdle
when trying to restore, because the referenced table would not have been
created in the first place.

I think you'd probably be able to get away with it, because pg_dump
always creates all tables before loading any data. If you were storing
index OIDs, maybe it wouldn't work.

regards, tom lane