Upgrade GCP hosted PostgreSQL while keeping existing user passwords

Mass GRANT/REVOKE is only available on PostgreSQL 9.0+.

Instead, you can create the functions pg_grant() and pg_revoke() found at http://blog.bhushangahire.net/2009/06/17/grant-privileges-to-all-tables-in-a-database-for-postgresql/

The parameters to both functions are:

1. User Role
2. Permissions
3. Object name, may be a pattern. "%" affects all objects
4. Schema name

You will need to run the command on each database and each schema. The syntax looks like this:

SELECT pg_grant('web_user','SELECT,INSERT,UPDATE,DELETE','%','public');

If you like, you may be able to change this line in the function definition:

nspname = $4 AND

to this:

nspname like $4 AND

in order to be able to specify schema as a wildcard, but I haven't tested it.

It's pretty hard to give you a step-by-step upgrade process that's tailored to your situation/environment.
I've tried to hit some of the highlights below, but you really need to tailor the process to your environment.

Step Zero, decide if you need to upgrade. Postgres 9.1 brings Synchronous Replication and a bunch of other cool stuff which is useful if you need it, but not a reason to upgrade right away if you don't.

Step 1, expect that this will take a long time if your database is big.
Even using pg_upgrade the simple process of copying the data for a backup can take a while: our 16GB databases take an hour or morefor a pg_dump/pg_restore, or about 10-20 minutes for a pg_upgrade).

Step 2, I suggest reading through the 9.1 Release Notes. Make sure nothing you need is broken and nothing you use has changed.
There is nothing like a database upgrade that destroys your production systems to really ruin your month.

Step 3, The upgrading section of the Postgres manual is required reading.

Step 4, (insert any Debian-specific stuff here, but that's really not my bag :-)

Step 5, Create an upgrade plan.

Step 6, Test the upgrade plan (technically optional, but strongly recommended).

Step 7, Do the upgrade on your production systems.

Standard caveats apply:

• Upgrading could destroy your database. Make a backup.
  • Be sure you can restore it.
  • Be sure you can restore the binaries for the database system too.
    (Hang on to your old Debian packages if you can - I've seen people unable to back out of a failed upgrade because they don't have the old database binaries anymore, and it's a very sad sight indeed.)
    
    

• It will take longer than you expect. Plan for this.
  My company's DB upgrade policy is "Start at 6PM on Friday. If it's not working by 9AM on Sunday roll back."

• Mount a scratch monkey
  If your data is really important restore a copy of it elsewhere and test your upgrade path.
  Yeah, I do this with 16GB databases. I'll still do it with 160GB databases if I can find a way to make it work :-)