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(at)gmail(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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