Postgres Pro
Downloads
Home   >   mailing lists

Thread: backup tableoids (pg_class.oid) ?

backup tableoids (pg_class.oid) ?

From
"fkater@googlemail.com"
Date:
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) ?

Thank you

Felix

Re: backup tableoids (pg_class.oid) ?

From
Martijn van Oosterhout
Date:
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/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy
Attachment

Re: backup tableoids (pg_class.oid) ?

From
Alvaro Herrera
Date:
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

Re: backup tableoids (pg_class.oid) ?

From
Tom Lane
Date:
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