What is a recommended way to upgrade a medium size (3.1Tb) production database from PG12/Postgis3 to PG15/Postgis3 with a reasonable (up to an hour) downtime?
pg_upgrade fails to perform the task with the following error: "old and new pg_controldata maximum TOAST chunk sizes are invalid or do not match".
The maximum TOAST chunk size is defined by a constant in the Postgres source code, and changing it will require you to rebuild the software.
Your options are:
Build PostgreSQL 15 from sources, defining the TOAST_MAX_CHUNK_SIZE constant to match your PostgreSQL 12 binary, which you can determine by running pg_controldata. This option is not for the faint of heart though, unless you build Postgres from sources for breakfast. You will also have to do this for every future upgrade. This, however, might be your only option if whoever set up your database had a really good reason to use a non-default TOAST chunk size value.
Initalise the new database with its default TOAST chunk size, then use pg_dump and pg_restore to move your data. This will likely require more time than your one hour downtime constraint.
Initalise the new database with its default TOAST chunk size, then set up logical replication from the old database to the new one and wait for it to catch up. This requires slightly more effort but will have minimal downtime.
