Postgres major upgrade poor experience

[Zsolt Ero]

Hello,

I have been spending a lot of time figuring out PG 9.6 -> 12 upgrade. Here are my findings:

1. There is no upgrade supported by the platform, you have to create a new server and somehow figure out how to move data over.

2. The built-in export/import functionality is not usable, since it strips all owner information from the dump. It's using --no-owner and --no-acl, so you cannot use it to export your database and import it, it just becomes dumb data. I'm quite surprised about this, and see no reason why should it be like this.

3. The only way to migrate is to set up a temporary compute engine instance with a really big SSD and run the pg_dump and pg_restore commands manually.

4. pg_dumpall doesn't run on Cloud SQL, it terminates with errors. It means that database and users have to be created manually, before the pg_restore command.

5. OK, so if someone has figured all of this out and not given up on Google Cloud SQL yet, then they can run the pg_dump and pg_restore process, which took 20 hours for ~500 GB of data in our case (gzipped dump 150 GB). Same datacenter, fast local SSDs. I cannot even imagine how are bigger servers supposed to be migrated over! 

I'd like to conclude that it's extremely tedious and troublesome to do something as simple as a major version upgrade of PG. On a managed database this should be a built-in feature!

Just have a look at AWS docs about upgrading Postgres major versions! That's how I imagine a professional, managed DB service.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html

Zsolt

[James Richardson]

I just want to say that this analysis is completely on point.

I want to also mention that it is not possible to export a list of tables using the documented "tables" parameter.

This is a bug and has been reported and is supposedly being looked at by engineers at Google.

I agree that the current functionality is very limited and almost useless.

Best regards

James

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/0c73f3ea-f21f-447d-8826-c310d8111a12n%40googlegroups.com.

[Aref Amiri (Cloud Platform Support)]

Thanks for sharing all your feedback on the newly released Cloud SQL Postgres 12. We continue to improve our products and services and make sure it meets customer requirements. To inform the product team of your required changes and functionality, I'd suggest to post them as a Feature Request on our Public Issue Tracker, and your feedback will be relayed to the team for further review and possible implementation in the future.

[Zsolt Ero]

Thanks, I've submitted it.

On Mon, 7 Sep 2020 at 16:16, 'Aref Amiri (Cloud Platform Support)' via
Google Cloud SQL discuss <google-cloud...@googlegroups.com>
wrote:

> You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/v-_9U6kTGmQ/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to google-cloud-sql-d...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/ca252a7f-8a3d-45e6-aa0e-39a1963342ecn%40googlegroups.com.