Thread: pg_dump: preserving oids in system tables?
Can someone confirm something for me? It seems that pg_dump with the -o option preserves OIDS for user objects, but does not preserve the OIDs for objects in system tables. Is that correct?
Is there any other way to preserve the OIDs in a system table? It would make sense if that is not possible, but if it *is* possible, I would love to know about it; it would make administering one of our databases easier.
(We have an in-house application that was developed in 2008 maybe? The app has a table that associates a user (role) with a "department" by inserting a row into a table with the values 'usesysid' from pg_user and a department. It *works*, until you have to dump and restore. Then that table ends up with garbage because the usesysids for each role is different in the new database. At least that's what I have seen.)
I have requested that the developer make a change in the application for future versions to use role names instead of the role's usesysid. In the meantime, database major version updates require some manual intervention, which is fine. I just wanted to ask in case there is a way to preserve those oids that I'm just not seeing.
Thanks!
Natalie
Can someone confirm something for me? It seems that pg_dump with the -o option preserves OIDS for user objects, but does not preserve the OIDs for objects in system tables. Is that correct?
[...]
The app has a table that associates a user (role) with a "department" by inserting a row into a table with the values 'usesysid' from pg_user and a department
"pg_dump" doesn't dump roles. They are global and handled via "pg_dumpall".
David J.