Thread: pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index
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
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
On Thu, May 31, 2012 at 4:28 PM, Jeff Davis <pgsql@j-davis.com> wrote:
Here's the requested data: https://gist.github.com/2852014
On Thu, 2012-05-31 at 15:55 -0500, Bryan Murphy wrote:Please send /srv/pg_upgrade_dump_globals.sql
> I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3. Here's
> the error:
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
On Fri, Jun 1, 2012 at 8:07 AM, Bryan Murphy <bmurphy1976@gmail.com> wrote:
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
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:Please send /srv/pg_upgrade_dump_globals.sql
> I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3. Here's
> the error:
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/2852014I 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 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
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