Thread: Restore roles with same oid
We currently use the 'usesysid' column from pg_shadow (which is really pg_authid.oid I assume) for a trigger-based auditing mechanism. We are about to do a dump from an 8.2 database into 8.4 and would like to preserve the usesysid/oid when restoring. No matter what options I throw ad pg_dumpall, it gives me 'create role' commands...and obviously no preservation of the oids. Any suggestions on how I can manage this?
On 8 June 2010 15:59, Jeff Amiel <jamiel@istreamimaging.com> wrote: > We currently use the 'usesysid' column from pg_shadow (which is really > pg_authid.oid I assume) for a trigger-based auditing mechanism. > > We are about to do a dump from an 8.2 database into 8.4 and would like to > preserve the usesysid/oid when restoring. > > No matter what options I throw ad pg_dumpall, it gives me 'create role' > commands...and obviously no preservation of the oids. > > Any suggestions on how I can manage this? > Can't you switch to using role names? I don't think oids are intended to be used by anything other than PostgreSQL. Thom
On 6/8/10 10:30 AM, "Thom Brown" <thombrown@gmail.com> wrote: > Can't you switch to using role names? I don't think oids are intended > to be used by anything other than PostgreSQL. :( If only I could....massive audit tables contain these IDs with years of data.... We have a plan to change to sequence values stored in one of our tables (one for each of our postgresql users) but don't want to pull the trigger on that plan as part of this upgrade if we can help it.
* Jeff Amiel (jamiel@istreamimaging.com) wrote: > On 6/8/10 10:30 AM, "Thom Brown" <thombrown@gmail.com> wrote: > > Can't you switch to using role names? I don't think oids are intended > > to be used by anything other than PostgreSQL. > > :( If only I could....massive audit tables contain these IDs with years of > data.... > > We have a plan to change to sequence values stored in one of our tables (one > for each of our postgresql users) but don't want to pull the trigger on that > plan as part of this upgrade if we can help it. I'm afriad you're not going to have a choice.. I would recommend creating a mapping from the old IDs to the new ones as part of this upgrade, to keep the historical information. Guess it's not nice to point this out- but you really shouldn't have ever used OIDs for something external to PG (or even internally, really). :) Thanks! Stephen
Attachment
On 6/8/10 10:39 AM, "Stephen Frost" <sfrost@snowman.net> wrote: > I'm afriad you're not going to have a choice.. I would recommend > creating a mapping from the old IDs to the new ones as part of this > upgrade, to keep the historical information. Guess it's not nice to > point this out- but you really shouldn't have ever used OIDs for > something external to PG (or even internally, really). :) To be honest...this was done early in our postgresql careers (back in the 7.X days). We knew so little. :)
* Jeff Amiel (jamiel@istreamimaging.com) wrote: > On 6/8/10 10:39 AM, "Stephen Frost" <sfrost@snowman.net> wrote: > > I'm afriad you're not going to have a choice.. I would recommend > > creating a mapping from the old IDs to the new ones as part of this > > upgrade, to keep the historical information. Guess it's not nice to > > point this out- but you really shouldn't have ever used OIDs for > > something external to PG (or even internally, really). :) > > To be honest...this was done early in our postgresql careers (back in the > 7.X days). We knew so little. :) Then you've been through this before.. Perhaps you should go check out what you did then. Back before 8.1, we didn't use OIDs for users/groups. :) Changing to OIDs was part of the work that I did to add role support. Thanks, Stephen
Attachment
On 6/8/10 10:47 AM, "Stephen Frost" <sfrost@snowman.net> wrote: > Then you've been through this before.. Perhaps you should go check out > what you did then. Back before 8.1, we didn't use OIDs for > users/groups. :) Changing to OIDs was part of the work that I did to > add role support. Hmmm...this code has been in place since March 2005..... using the usesysid from pg_shadow. 8.1 wsn't released until november of 2005. Doesn't matter....I still have some work to do.