Upgrading postgis_sfcgal to 3.1 or higher

As of PostGIS 3.1, the PostGIS sfcgal support library is no longer part of the postgis core library, but instead spun off into a new library postgis_sfcgal-3.

This change is not an issue for people doing regular, soft-upgrades from a PostGIS < 3.1 compiled with SFCGAL to a PostGIS >= 3.1 with SFCGAL using ALTER EXTENSION postgis_sfcgal UPDATE; or SELECT postgis_extensions_upgrade();. However if you are using pg_upgrade, you might get errors like postgis-3 does not contain function postgis_sfcgal_version() (which is part of the postgis_sfcgal extension).

The three main reasons for this break were:

• We wanted postgis-3 library to have the same exposed functions regardless if you are compiling with SFCGAL or not. This change was planned in PostGIS 3.0, but only the backend switching plumbing was removed and not the complete detachment.

• It makes it possible for packagers to offer postgis_sfcgal (perhaps as a separate package), without requiring other users who just want postgis to have to have boost and CGAL.

• In the past postgis_sfcgal and postgis extensions were hooked together at the hip in the same underlying library, because their were a few functions overlapping in name such as ST_3DIntersects and ST_Intersects. Trying to explain to people how this whole thing worked, to switch the backend to sfcgal if they wanted extended 3D functionality, not to mention the added annoyance GUC backend of notices during upgrade was more of a pain than it was worth. So moving forward, we will not be reusing function names between the two extensions, and will have only non-overlapping function names.

The easiest fix when using pg_upgrade to upgrade PostGIS < 3.1 postgis_sfcgal is to first drop the postgis_sfcgal extension before running pg_upgrade.

This can be done without data loss because the postgis_sfcgal extension only contains functions and no datatypes. After you have dropped it in your old cluster with:

In old cluster before attempting pg_upgrade

-- this step only needed for PostGIS < 3.1.0
DROP EXTENSION postgis_sfcgal;

The above DROP EXTENSION will fail if you have views. SQL functions, or constraints bound to postgis_sfcgal functions. In those cases, you can either drop those dependencies too prior to pg_upgrade and make sure you have the code to recreate. Alternatively you can instead do Option 2.

After you have dropped the extension in your old cluster, you can then run pg_upgrade.

After pg_upgrade, then in the new cluster, readd it back.

CREATE EXTENSION postgis_sfcgal;

The create extension should error out if you don’t install in the same schema as postgis extension. If you get an error, then specify the schema you installed postgis in expplicitly:

CREATE EXTENSION postgis_sfcgal SCHEMA postgis;

On old cluster, make sure you have installed postgis binaries for 3.1 or higher, then do:

ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();

Then run pg_upgrade as usual.