Re: [GENERAL] pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index

On Fri, Jun 01, 2012 at 09:52:59AM -0400, Tom Lane wrote:
> Bryan Murphy <bmurphy1976@gmail.com> writes:
> > The old 9.0 cluster was created by ubuntu.  In this cluster there was an
> > ubuntu user with an oid of 10 and a postgres user with an oid of 16386.
>
> > The new 9.1 cluster was created with a custom build of postgres 9.1. This
> > did not have an ubuntu user, and it had a postgres user with an oid of 10.
>
> OID 10 is the bootstrap superuser, which is created with the name of the
> operating system user that ran initdb.  So the above does not sound like
> anything to do with custom vs stock builds, but with who did initdb.
>
> It seems that pg_upgrade needs a check to make sure that the bootstrap
> superuser is named the same in old and new clusters.

[ Thread moved to hackers.]

OK, I have studied this.  First we preserve pg_authid.oid because oids
are stored in pg_largeobject_metadata.  Second, we dumpall all users,
even the install user because (from pg_dumpall.c):

         * We dump CREATE ROLE followed by ALTER ROLE to ensure that the role
         * will acquire the right properties even if it already exists (ie, it
         * won't hurt for the CREATE to fail).  This is particularly important
         * for the role we are connected as, since even with --clean we will
         * have failed to drop it.

So, pg_upgrade has to strip out restoring the install user because that
would cause an error on restore.  That is done in
dump.c::split_old_dump().

The problem is if the old and new install users have different oids, as
the reporter verified.

The attached patch adds checks to verify the the old/new servers have
the same install-user oid.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +