Causes for 'postgis.backend' is already set

The error 'postgis.backend' is already set comes up every so often in PostGIS mailing list. The issue arises often during or after an upgrade.

The question goes something like this

After upgrading to Postgis 2.3 from 2.1, my server log is filled with these messages: WARNING ‘postgis.backend’ is already set and cannot be changed until you reconnect

Possible Cause 1: Legacy Functions

If you installed the legacy.sql file, particularly before the PostGIS version 3+ releases, the old legacy functions may be still pointing at the location of your old PostGIS library.

The fix for this is to run the old legacy_uninstall.sql and then reinstall the new legacy.sql file..

The legacy.sql scripts are generally located in your PostgreSQL share\contrib\postgis directory. For example, with a 9.4 PostgreSQL and 2.1 PostGIS:

# Remove the old functions
psql -d your_db -f /usr/share/postgresql/9.4/contrib/postgis-2.1/uninstall_legacy.sql

# If you want still need legacy functions
psql -d your_db -f /usr/share/postgresql/9.4/contrib/postgis-3.2/legacy.sql

In general, you probably don’t want to use the legacy functions, so check and see if your applications are all happy once you have removed them, maybe you do not need to install them again.

Possible Cause 2: An Old postgis_sfcgal Lingers

If your postgis_sfcgal version was installed with extensions. Which would be postgis 2.2 or above, you can run:

ALTER EXTENSION postgis_sfcgal UPDATE;

If you have multiple versions of PostGIS, and don’t want the last installed, you might want to do this instead. Make sure the version matches your postgis version since both extensions use the same postgis library.

ALTER EXTENSION postgis_sfcgal UPDATE TO "2.3.2";

Possible Cause 3: An Old postgis_sfcgal Lingers

You are running an out-dated micro-version.

Way back when we first introduced GUCs in PostGIS, there was a bug during upgrade that would trigger when going between minor versions. This issue has long been fixed, so make sure you have the latest micro versions installed for your minor and then do.

ALTER EXTENSION postgis UPDATE;

Possible Cause 4: Mixed Manual and Extension Install

Perhaps you did something unorthodox and managed to install an old PostGIS version without extensions and a new one with extensions. Or perhaps you’ve got some functions left over from an incomplete upgrade or install.

So to fix, you’ve got to find these and update them. Here is a query to find how many postgis library versions you have installed and how many functions you have of each

SELECT probin, count(*)
FROM pg_catalog.pg_proc
WHERE probin LIKE '%/postgis%'
GROUP BY probin
ORDER BY probin;

In a healthy install, you should have only one row that looks something like this. Your version number might be different and count might be different.

|   probin            | count |
|---------------------|-------|
|$libdir/postgis-2.4  |   446 |

If you have more than one row you have mixed library versions:

| probin             | count
---------------------+-------
$libdir/postgis-2.4  |   446
$libdir/postgis-2.1  |   4

Run this query to replace the postgis-2.1 with the newest version you have listed.

UPDATE pg_catalog
SET pg_proc = '$libdir/postgis-2.4'
WHERE probin LIKE '%/postgis-2.1%';