pg_dump: preserving oids in system tables?

From: Natalie Wenz <nataliewenz(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: pg_dump: preserving oids in system tables?
Date: 2015-05-06 18:11:08
Message-ID: CAON+zeMbtqe2jccRFjrAoroLA8akn=ODMj_FZ1yR+OXYO8KM2w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Peter Geoghegan 2015-05-06 18:27:49 Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
Previous Message Andres Freund 2015-05-06 15:20:07 Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0