[craigkerstiens]

Whether you should use replication for upgrade is a bit more of a loaded question. But dump/restore should definitely NOT be the mechanism for upgrades. Postgres has support for in place upgrades with pg_upgrade for some time now. This is the mechanism used on RDS, and the same mechanism we'll use on Crunchy Bridge, and what Heroku Postgres uses as well. I'm not sure that Azure supports in place upgrade yet, and GCP does not per their docs.

A dump and restore is simply not viable for a database of any size. 100GB datababase which is not at all in the "large" territory would be down for at least an hour if not longer.

Pg_upgrade is generally the right shape of thing for this problem. One could debate whether replication is a better approach for even reducing that down time. (Pg_upgrade is on the order of minutes, it is not a size of data operation but rather a size of catalog operation). But that dump/restore is acceptable and the best option isn't really the case these days.

[gkop]

Did parent ninja edit? Currently, it doesn’t mention upgrading, so your response looks like a non sequitur.

[craigkerstiens]

I was reading a bit between the lines, original was about migrating in, another was saying about a proper synchronization service or dump. In the case of migrating from something outside of Cloud SQL into Cloud SQL you could basically do this today. A dump wouldn't be recommended in my opinion (having done a lot of migrations across cloud providers including several multi-TB databases).

In that case as long as the source has some form of decoding (test decoding plugin or pgoutput) it should work.

That a dump is always the best process anything isn't really true these days (I know someone will show up with a case of why dump is 100% for what they need in moving data around). But, the combination of logical decoding, and pg_upgrade cover most cases.

Admit I was jumping a bit with upgrades, but that is where dump/restore does most often come up, not with migrations.