Re: [google-cloud-sql-discuss] Postgres major upgrade poor experience

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

On Thu, Sep 3, 2020 at 12:02 AM Zsolt Ero <zsolt.ero@gmail.com> wrote:

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

--
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-discuss+unsubscribe@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.

--
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-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/CAP_cTFyeSQbzOF4bQM4n0oVj_j8o%2BEGYax-xpQv3gEoW8rVncw%40mail.gmail.com.