I got this error trying to upgrade one of our database clusters (happily in testing) from 9.2 to 9.4:
Old and new cluster install users have different values for pg_authid.oid
Important background here is that we used to run the database as the postgres unix user, but recently we had changed it to run as a different user (because we have several different databases all running as the postgres user on different machines and we wanted each logically separate database to run as a different extra for that purpose unix user -- this simplified internal administration management).
We had done this by adding a new superuser to the database (with the name of the unix user it will run as in the future). turning off the database, chown -R <new-user> databasedir, starting the database
(and adapting the startup scripts accordingly).
What I wasn't aware of is that postgres knows which user was used to run pg_init.
So my first attempt at upgrading by running the below as the new user
old_loc=/mnt/dbc03-d1/proddb/postgres
new_loc=/mnt/dbc03-d1/proddb-94/postgres
rm -rf $new_loc/*
/usr/pgsql-9.4/bin/initdb $new_loc
self-service stop postgresql-9.2
/usr/pgsql-9.4/bin/pg_upgrade \
-k \
-j 8 \
--old-datadir $old_loc \
--new-datadir $new_loc \
--old-bindir /usr/pgsql-9.2/bin \
--new-bindir /usr/pgsql-9.4/bin
Failed with the above "Old and new cluster ..."
In my next attempt I tried adding the bold to the initdb command
/usr/pgsql-9.4/bin/initdb $new_loc -U postgres
But that eventually fails during pg_upgrade with:
connection to database failed: FATAL role "<new-user>" does not exist
could not connect to new postmaster started with the command:
/usr/pgsql-9.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/mnt/dbc03-d1/proddb-94/...
last thing it prints before that is
Creating dump of database schemas
<databases>
ok
So I would love to know what the recommended way to go forward is. Ideally it avoids using the old postgres unix
and database user (we want to completely get rid of it eventually, but if I have to do some additional one off work this
time to get past this hurdle using the user that is of course an acceptable trade off).
Thanks in advance,
Bene