On Mon, Nov 23, 2015 at 11:12:25AM +0000, Benedikt Grundmann wrote: > 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 Your description is very clear. In 9.4 and earlier, Postgres checks that the user running upgrade has the same pg_authid.oid in the old and new clusters. In 9.5 we check that the user is the BOOTSTRAP_SUPERUSERID (10) on both the old and new cluster. Therefore, what I suggest you do, before running pg_upgrade, is to rename the pg_authid.oid = 10 row to be your new install user instead of 'postgres', and make your new user row equal 'postgres', e.g. something like: -- You already did this first one --> test=> create user my_new_install_user; --> CREATE ROLE select oid from pg_authid where rolname = 'my_new_install_user'; oid ------- 16385 (1 row) select oid from pg_authid where rolname = 'postgres'; oid ----- 10 (1 row) -- 'XXX' prevents duplicate names update pg_authid set rolname = 'XXX' where oid = 10; UPDATE 1 update pg_authid set rolname = 'postgres' where oid = 16385; UPDATE 1 update pg_authid set rolname = 'my_new_install_user' where oid = 10; UPDATE 1 What this does it to make your new install user the bootstrap user, which is a requirement for 9.5 pg_upgrade. You would do this _before_ running pg_upgrade as my_new_install_user. However, keep in mind that once you do this, everthing owned by my_new_install_user and postgres are now swapped. This is basically what you need to do after changing the ownership of the Postgres file system files. You can see the 9.5 requirements in the pg_upgrade function check_is_install_user(). You might as well just honor what that requires as you will eventually be moving to 9.5. -- Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
