Upgrading Postgres from 9.6 to 11

[Alex Gonopolskiy]

I'd like to be able to upgrade my existing cloudsql postgres 9.6 instance to 11 to use some new pg 11 features.

I've been trying to figure out a good migration plan but it seems like the only option available is sql dump and restore.  The database is 100Gig+ so this will take quite some time, and I'd like to avoid downtime as much as possible.  Are there any options available? I was considering enabling statement logging: log_statement=mod, creating a dump, importing it into a pg-11 instance taking down the db + then scraping the logs to reply the latest updates into the pg-11 instance by downloading the logs and writing a script to re-run the inserts.  Seems doable but I am wondering if anyone faced this before and has had any other solutions?

[George (Cloud Platform Support)]

Hello Alex, 

You may also consider creating a replica and performing the export operation from that replica. There are various strategies to reduce impact on users when migrating your database; you may find relevant information on the Web, for instance the "Time reduction battle while migrating databases from hosted PostgreSQL to Google Cloud SQL" page. 

It may be worthwhile noting here that Google Groups are reserved for general product discussions. We cannot provide you any advice on your system’s architecture, and related best solutions. For programming, database management, system architecture, you may publish detailed information in forums such as stackoverflow, where experienced programmers are happy to help.

[Lior Kogot]

George, I think this is a legit question by Alex! I have a few database with lots and lots of securitie  around them. Migrating the existing 9.6 to 11 manually could break the system depending on the DB. I think there should be a supported way by GCP to do in-place upgrade.

Thoughts?

[Lior Kogot]

I am facing the same problem. need to upgrade 9.6 to 11 and I think that manual migration is BAD!