[RFC] Cluster Upgrade helper

[lucernae]

I would like to add functionality to upgrade cluster from previous major version to current major version.

I imagine we can use a stateless container to perform the upgrade like this:

services:
  pg-upgrade:
    image: kartoza/postgis:12
    entrypoint: ""
    command: "/scripts/cluster_upgrade.sh"
    volumes:
      - previous-data-dir:/var/lib/postgresql/11
      - previous-config-dir:/etc/postgresql/11
      - data-dir:/opt/data/upgraded
    environment:
      PGVERSIONOLD: "11"
      PGVERSIONNEW: "12"
      PGBINOLD: "/usr/lib/postgresql/11/bin"
      PGBINNEW: "/usr/lib/postgresql/12/bin"
      PGDATAOLD: "/var/lib/postgresql/11/main"
      PGDATANEW: "/opt/data/upgraded"

Proposed implementations

We are going to use postgresql common upgrade procedure, which is using pg_upgradecluster. The upgrade process is going to be wrapped in /scripts/cluster_upgrade.sh. What the script will do:

1. Make sure that binaries of target old version and new version exists (using apt install at run time)
2. Avoid name clash of default cluster main by doing pg_renamecluster. This should be ok and stateless since the containers only modifies internal data.
3. Perform cluster upgrade into the target directory PGDATANEW. This directory should be mounted to a volume.

Output:

1. New postgresql cluster with targeted version in the new volume (without the conf data)

Extensibility: Users might want to perform some extra initialization/post upgrade steps. We provide a file hook called /upgrade.d/pre.sh and upgrade.d/post.sh to put a script hook. pre.sh will be executed before the upgrade. post.sh will be executed after a successful upgrade. Upgrade error can be detected and handled directly from stdout logs.

CC @NyakudyaA

[NyakudyaA]

Looks good for me @lucernae .

Just a note that they are various upgrade options.

I haven't done a comparison of what the disadvantages and advantages are.

I have also used the postgis_restore.pl to do a DB upgrade

[lucernae]

I see, so upgrading the database doesn't necessarily upgrade postgis related objects?

[NyakudyaA]

I see, so upgrading the database doesn't necessarily upgrade PostGIS related objects?

If you upgrade the cluster it does everything.

https://www.postgresql.org/docs/12/upgrading.html

postgis_restore.pl uses a normal pgdump which might work well with the pg_backup containers that already produce the .dmp. So I assume we could have two recipes.

• Using pg_clusterupgrade
• Using postgis_restore.pl which uses the dump from the pgbackup images

[lucernae]

Some update on my part.

• pg_clusterupgrade does actually upgrade the cluster with postgis in it.
• pg_clusterupgrade does not upgrade postgis version directly, so we can auto upgrade it to add that functionality via extension update
• for some weird reasons, pg_clusterupgrade does not migrate pg_cron tables. Since the cluster only available after the upgrade process finished, we have no way of injecting some code that help this process.
• pg_clusterupgrade doesn't handle postgis and postgis_raster separation. so table with raster data in postgis v2.x is not migrated for the same reason above like pg_cron. I have no idea to inject this functionality. If there is a way to do clusterupgrade to an existing cluster, we might have a solutions.