Thread

1. pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index

   Bryan Murphy <bmurphy1976@gmail.com> — 2012-05-31T20:55:45Z

   I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3.  Here's the
   error:
   
   psql:/srv/pg_upgrade_dump_globals.sql:54: ERROR:  duplicate key value
   violates unique constraint "pg_authid_oid_index"
   DETAIL:  Key (oid)=(10) already exists.
   
   Any ideas what I'm doing wrong?
   
   Here's the verbose output from pg_upgrade:
   
   $ /opt/postgresql-9.1/bin/pg_upgrade --link --verbose
   --old-datadir=/srv/postgresql/pg_data --new-datadir=/srv/postgres-9.1
   --old-bindir=/opt/postgresql-9.0/bin --new-bindir=/opt/postgresql-9.1/bin
   Running in verbose mode
   Performing Consistency Checks
   -----------------------------
   Checking current, bin, and data directories                 ok
   Checking cluster versions                                   ok
   "/opt/postgresql-9.0/bin/pg_ctl" -w -l "/dev/null" -D
   "/srv/postgresql/pg_data" -o "-p 5432 -c autovacuum=off -c
   autovacuum_freeze_max_age=2000000000" start >> "/dev/null" 2>&1
   Checking database user is a superuser                       ok
   Checking for prepared transactions                          ok
   Checking for reg* system oid user data types                ok
   Checking for contrib/isn with bigint-passing mismatch       ok
   Creating catalog dump
   "/opt/postgresql-9.1/bin/pg_dumpall" --port 5432 --username "postgres"
   --schema-only --binary-upgrade > "/srv/pg_upgrade_dump_all.sql"
   ok
   "/opt/postgresql-9.0/bin/pg_ctl" -w -l "/dev/null" -D
   "/srv/postgresql/pg_data"  stop >> "/dev/null" 2>&1
   "/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1"
   -o "-p 5432 -b" start >> "/dev/null" 2>&1
   Checking for prepared transactions                          ok
   Checking for presence of required libraries                 ok
   
   | If pg_upgrade fails after this point, you must
   | re-initdb the new cluster before continuing.
   | You will also need to remove the ".old" suffix
   | from /srv/postgresql/pg_data/global/pg_control.old.
   
   Performing Upgrade
   ------------------
   Adding ".old" suffix to old global/pg_control               ok
   Analyzing all rows in the new cluster
   "/opt/postgresql-9.1/bin/vacuumdb" --port 5432 --username "postgres" --all
   --analyze >> "/dev/null" 2>&1
   ok
   Freezing all rows on the new cluster
    "/opt/postgresql-9.1/bin/vacuumdb" --port 5432 --username "postgres" --all
   --freeze >> "/dev/null" 2>&1
   ok
   "/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1"
    stop >> "/dev/null" 2>&1
   Deleting new commit clogs                                   ok
   Copying old commit clogs to new server                      cp -Rf
   "/srv/postgresql/pg_data/pg_clog" "/srv/postgres-9.1/pg_clog"
   ok
   Setting next transaction id for new cluster
   "/opt/postgresql-9.1/bin/pg_resetxlog" -f -x 743542427 "/srv/postgres-9.1"
   > /dev/null
   ok
   Resetting WAL archives
    "/opt/postgresql-9.1/bin/pg_resetxlog" -l 1,829,15 "/srv/postgres-9.1" >>
   "/dev/null" 2>&1
   ok
   "/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1"
   -o "-p 5432 -b" start >> "/dev/null" 2>&1
   Setting frozenxid counters in new cluster                   ok
   Creating databases in the new cluster
   "/opt/postgresql-9.1/bin/psql" --set ON_ERROR_STOP=on --no-psqlrc --port
   5432 --username "postgres" -f "/srv/pg_upgrade_dump_globals.sql" --dbname
   template1 >> "/dev/null"
   psql:/srv/pg_upgrade_dump_globals.sql:54: ERROR:  duplicate key value
   violates unique constraint "pg_authid_oid_index"
   DETAIL:  Key (oid)=(10) already exists.
   
   There were problems executing "/opt/postgresql-9.1/bin/psql" --set
   ON_ERROR_STOP=on --no-psqlrc --port 5432 --username "postgres" -f
   "/srv/pg_upgrade_dump_globals.sql" --dbname template1 >> "/dev/null"
   Failure, exiting
   "/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1"
   -m fast stop >> "/dev/null" 2>&1
   
   Thanks,
   Bryan
   

2. Re: pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index

   Jeff Davis <pgsql@j-davis.com> — 2012-05-31T21:28:54Z

   On Thu, 2012-05-31 at 15:55 -0500, Bryan Murphy wrote:
   > I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3.  Here's
   > the error:
   
   Please send /srv/pg_upgrade_dump_globals.sql
   
   Also, can you restart the old system (by removing the ".old" suffix, as
   the message suggests), and then do a "SELECT oid,* FROM pg_authid" and
   send the output along?
   
   Regards,
   	Jeff Davis
   
   
   
   
   

3. Re: pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index

   Bryan Murphy <bmurphy1976@gmail.com> — 2012-06-01T13:07:42Z

   On Thu, May 31, 2012 at 4:28 PM, Jeff Davis <pgsql@j-davis.com> wrote:
   
   > On Thu, 2012-05-31 at 15:55 -0500, Bryan Murphy wrote:
   > > I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3.  Here's
   > > the error:
   >
   > Please send /srv/pg_upgrade_dump_globals.sql
   >
   > Also, can you restart the old system (by removing the ".old" suffix, as
   > the message suggests), and then do a "SELECT oid,* FROM pg_authid" and
   > send the output along?
   >
   
   Here's the requested data: https://gist.github.com/2852014
   
   I had to censor some of it because it contained sensitive information,
   hopefully the censoring is obvious and I don't believe I touched any of the
   functional information.
   
   Thanks,
   Bryan
   

4. Re: pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index

   Bryan Murphy <bmurphy1976@gmail.com> — 2012-06-01T13:45:41Z

   On Fri, Jun 1, 2012 at 8:07 AM, Bryan Murphy <bmurphy1976@gmail.com> wrote:
   
   > On Thu, May 31, 2012 at 4:28 PM, Jeff Davis <pgsql@j-davis.com> wrote:
   >
   >> On Thu, 2012-05-31 at 15:55 -0500, Bryan Murphy wrote:
   >> > I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3.  Here's
   >> > the error:
   >>
   >> Please send /srv/pg_upgrade_dump_globals.sql
   >>
   >> Also, can you restart the old system (by removing the ".old" suffix, as
   >> the message suggests), and then do a "SELECT oid,* FROM pg_authid" and
   >> send the output along?
   >>
   >
   > Here's the requested data: https://gist.github.com/2852014
   >
   > I had to censor some of it because it contained sensitive information,
   > hopefully the censoring is obvious and I don't believe I touched any of the
   > functional information.
   >
   
   OK, I seem to have figured it out. Your questions pointed me in the right
   direction.
   
   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.
   
   I renamed the postgres user in the old 9.0 cluster to pg, renamed the
   ubuntu user to postgres, and then re-ran pg_upgrade and it appears to have
   worked correctly this time.
   
   Bryan
   

5. Re: pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-06-01T13:52:59Z

   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.
   
   			regards, tom lane
   
   

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

   Bruce Momjian <bruce@momjian.us> — 2012-06-02T20:41:53Z

   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. +
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-06-02T21:10:03Z

   Bruce Momjian <bruce@momjian.us> writes:
   > On Fri, Jun 01, 2012 at 09:52:59AM -0400, Tom Lane wrote:
   >> It seems that pg_upgrade needs a check to make sure that the bootstrap
   >> superuser is named the same in old and new clusters.
   
   > The attached patch adds checks to verify the the old/new servers have
   > the same install-user oid.
   
   That may or may not be a useful check to make, but it's got
   approximately nothing to do with what I was complaining about.
   
   In particular, supposing that the user has given you a username that
   isn't the bootstrap superuser in the new cluster, this patch is not
   going to stop the update script from failing.  Because the script is
   then going to try to replace the bootstrap superuser, and that is
   certainly going to give an error.
   
   I see the point of worrying about the install user as well as the
   bootstrap superuser, but wouldn't it be best to insist they be the same?
   Particularly in the new cluster, where if they aren't the same it means
   the user has manually created at least one role in the new cluster,
   which is likely to lead to OID conflicts or worse.
   
   Furthermore, if the bootstrap superusers aren't named the same, your
   patch fails to handle the original complaint.  In the case the
   OP mentioned, the old cluster had
   	OID 10: "ubuntu"
   	some user-defined OID: "postgres"
   and the new cluster had
   	OID 10: "postgres"
   If the user tells pg_upgrade to use username postgres, your check will
   not fail AFAICS, but nonetheless things are going to be messed up after
   the upgrade, because some objects and privileges that used to belong to
   the bootstrap superuser will now belong to a non-default superuser,
   whereas what used to belong to the non-default superuser will now belong
   to the bootstrap superuser.  That cannot be thought desirable.  For one
   reason, in the old installation the postgres role could have been
   dropped (possibly after dropping a few non-builtin objects) whereas the
   "ubuntu" role was pinned.  In the new installation, "postgres" is pinned
   and "ubuntu" won't be.
   
   I think the checks that are actually needed here are (1) bootstrap
   superusers are named the same, and (2) there are no roles other than the
   bootstrap superuser in the new cluster.
   
   			regards, tom lane
   
   

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

   Bruce Momjian <bruce@momjian.us> — 2012-06-04T14:16:45Z

   On Sat, Jun 02, 2012 at 05:10:03PM -0400, Tom Lane wrote:
   > Bruce Momjian <bruce@momjian.us> writes:
   > > On Fri, Jun 01, 2012 at 09:52:59AM -0400, Tom Lane wrote:
   > >> It seems that pg_upgrade needs a check to make sure that the bootstrap
   > >> superuser is named the same in old and new clusters.
   > 
   > > The attached patch adds checks to verify the the old/new servers have
   > > the same install-user oid.
   > 
   > That may or may not be a useful check to make, but it's got
   > approximately nothing to do with what I was complaining about.
   > 
   > In particular, supposing that the user has given you a username that
   > isn't the bootstrap superuser in the new cluster, this patch is not
   > going to stop the update script from failing.  Because the script is
   > then going to try to replace the bootstrap superuser, and that is
   > certainly going to give an error.
   > 
   > I see the point of worrying about the install user as well as the
   > bootstrap superuser, but wouldn't it be best to insist they be the same?
   > Particularly in the new cluster, where if they aren't the same it means
   > the user has manually created at least one role in the new cluster,
   > which is likely to lead to OID conflicts or worse.
   > 
   > Furthermore, if the bootstrap superusers aren't named the same, your
   > patch fails to handle the original complaint.  In the case the
   > OP mentioned, the old cluster had
   > 	OID 10: "ubuntu"
   > 	some user-defined OID: "postgres"
   > and the new cluster had
   > 	OID 10: "postgres"
   > If the user tells pg_upgrade to use username postgres, your check will
   > not fail AFAICS, but nonetheless things are going to be messed up after
   > the upgrade, because some objects and privileges that used to belong to
   > the bootstrap superuser will now belong to a non-default superuser,
   > whereas what used to belong to the non-default superuser will now belong
   > to the bootstrap superuser.  That cannot be thought desirable.  For one
   > reason, in the old installation the postgres role could have been
   > dropped (possibly after dropping a few non-builtin objects) whereas the
   > "ubuntu" role was pinned.  In the new installation, "postgres" is pinned
   > and "ubuntu" won't be.
   > 
   > I think the checks that are actually needed here are (1) bootstrap
   > superusers are named the same, and (2) there are no roles other than the
   > bootstrap superuser in the new cluster.
   
   You are right that it is more complex than I stated, but given the
   limited feedback I got on the pg_upgrade/plplython, I figured people
   didn't want to hear the details.  Here they are:
   
   There are three failure modes for pg_upgrade:
   
   1.  check failure
   2.  schema restore failure
   3.  silent failure/corruption
   
   Of course, the later items are worse than the earlier ones.  The
   reporter got a "schema restore failure" while still following the
   pg_upgrade instructions.  My initial patch changed that #2 error to a #1
   error.  Tom is right that creating users in the new cluster (against
   instructions), can still generate a #2 error if a new/old pg_authid.oid
   match, and they are not the install user, but seeing that is something
   that is against the instructions, I was going to leave that as a #2.
   
   However, since Tom feels we should check that and make it a #1 failure,
   I have added that test to the attached patch.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + It's impossible for everything to be true. +
   

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

   Bruce Momjian <bruce@momjian.us> — 2012-06-13T16:20:21Z

   On Mon, Jun 04, 2012 at 10:16:45AM -0400, Bruce Momjian wrote:
   > > I think the checks that are actually needed here are (1) bootstrap
   > > superusers are named the same, and (2) there are no roles other than the
   > > bootstrap superuser in the new cluster.
   > 
   > You are right that it is more complex than I stated, but given the
   > limited feedback I got on the pg_upgrade/plplython, I figured people
   > didn't want to hear the details.  Here they are:
   > 
   > There are three failure modes for pg_upgrade:
   > 
   > 1.  check failure
   > 2.  schema restore failure
   > 3.  silent failure/corruption
   > 
   > Of course, the later items are worse than the earlier ones.  The
   > reporter got a "schema restore failure" while still following the
   > pg_upgrade instructions.  My initial patch changed that #2 error to a #1
   > error.  Tom is right that creating users in the new cluster (against
   > instructions), can still generate a #2 error if a new/old pg_authid.oid
   > match, and they are not the install user, but seeing that is something
   > that is against the instructions, I was going to leave that as a #2.
   
   Applied and back-patched to Postgres 9.1.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + It's impossible for everything to be true. +