Upgrade GCP hosted PostgreSQL while keeping existing user passwords

[john m]

I have been tasked with upgrading our existing GCP hosted PostgreSQL databases from version 9.6 to version 13.

The instructions I found (https://cloud.google.com/sql/docs/postgres/upgrade-db)

say that we need to create a new database and then

"Make sure the target instance has... The same user accounts, with the same PostgreSQL privileges and passwords"

We have a number of databases with dozens of users and roles, we allow users to connect directly and set their own passwords.

We would like to perform the database upgrade without changing all the passwords.

I've looked at using pg_dumpall to copy the users to a new database, but it fails as I don't have permission to read pg_authid.

It seems that we need a superuser account to be able to read pg_authid, and GCP does not permit superuser accounts.

Is there any way to upgrade the databases and keep the existing passwords?

[Davis Rad]

My database for Lil Peep's rare merch suffers from the same problem. I have read all documentation, but cannot resolve the issue.

[Henrik Söderlund]

Did any of you manage to resolve this? I am in the same situation now and it doesn't look like a good solution exists. I am particularly concerned about the part about passwords. Some of the user accounts in the source database I need to migrate belonged to users who have left the company now. I don't know what their passwords were. Others are "machine" users, used by other parts of the company that I do not have access to. Getting hold of those passwords will require someone to sign off on my permission to do so, i.e. hoops to jump through. It feels a bit crazy that moving from postgres vX to vY on Google Cloud should be this complicated.

Just out of curiosity, do you know what will happen if I recreate the user accounts with the same name and permissions, but with different passwords? Will that work, or block the migration?

[Lluis Munoz Ladron de Guevara]

Hi,

I was searching for a way to migrate the users from one Cloud SQL instance to another, in this case a Cloud SQL Postgres 9.6 instance to a Cloud SQL Postgres 13, and I found this feature request.

If you think that feature would help you solve your issue I'd like to encourage you to write a message in that thread explaining your situation and how that feature would help you. 

As you can see here, users feedback is an important metric for feature requests. 

[William Graig]

Can I just add something? So Basically I had the same issue with my website a few days ago, my website was based on Downloading ThopTv Apk, I have been tasked with upgrading our GCP hosted PostgreSQL databases from version 9.6 to version 13.
I found some material on the internet to solve it, in which the instructions were that we need to create a new database and then "Make sure the target instance has, The same user accounts, with the same PostgreSQL privileges and passwords". We have a number of databases with dozens of users and roles, we allow users to connect directly and set their own passwords. We would like to perform the database upgrade without changing all the passwords. I've looked at using pg_dumpall to copy the users to a new database. I'm just what i know and the things that I found, i hope this was helpful mates! Peace!

[William Graig]

Please Help me with my ThopTv Live Unlimited Apk Downloading website! 

On Thursday, January 13, 2022 at 4:13:56 PM UTC+5 llu...@google.com wrote:

[Aref Amiri (Cloud Platform Support)]

William,

Please refer to the response from Lluis for more information on keeping the same user accounts, with the same PostgreSQL privileges and passwords during upgrade. 

This discussion group is oriented more towards general opinions, trends, and issues of general nature touching Cloud SQL. For coding and programming architecture or help with your website,  you may be better served in dedicated forums such as stackoverflow, where experienced programmers are within reach and ready to help.

[William Graig]

Oh! Thank you so much Aref Amiri!  I appreciate your concern! I'll try those forums!

[john m]

It seems that there wasn't a way to do this at the time I asked the question, but things have moved on since then.

Google Cloud SQL now provides in-place major version upgrades, which will keep the existing user passwords.

https://cloud.google.com/sql/docs/postgres/upgrade-major-db-version-inplace