Обсуждение: 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

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

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.