CODEWITHSUNDEEP
google-cloud-sql
Alex Gonopolskiy
Alex Gonopolskiy

Reputation: 11

Gcloud SQL upgrade postgres 9.6 to 11

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 doesn't feel nice.

I am wondering if anyone faced this before and has had any other solutions?

Upvotes: 1

Views: 2833

Answers (1)

maniSidhu98
maniSidhu98

Reputation: 537

Postgres 11 on Cloud SQL is still in Beta. It is not recommended to be using a product that is in Beta on a production environment.

However, should you choose to proceed, you must export the data by either creating a SQL dump or putting the data into a .csv file (depending on your needs)(best practices) create a Postgres 11 instance, and then import the data.

For the data that won’t be in the dump, you can either:

a) Do what you have suggested by logging the queries and then re-run the inserts

b) Create a dump, import it onto the new instance make it live and then take another dump of the old one again, compare to remove duplicates and import the differences. This will be difficult if you have auto-incrementing primary keys.

c) Create the schema on the Postgres 11 instance and deploy it. Then create the dump and import at a later time. If you have primary keys as auto incrementing, alter the schema to start at a value that you would like.

Upvotes: 3

Related Questions