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. I'll go over causes for this I am aware of and how to fix.

The question goes something like this

"WARNING 'postgis.backend' is already set and cannot be changed until you reconnect"

REASON 1: You installed legacy.sql a while back say in 2.1, and the old legacy functions are pointing at your old PostGIS library.

These scripts are generally located in your PostgreSQL share\contrib\postgis-replace-with-your-version

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

So for example, if I had 2.1 legacy scripts installed and I'm on ubuntu. I would connect to by database using psql:

1\connect gisdb
2\i /usr/share/postgresql/9.4/contrib/postgis-2.1/uninstall_legacy.sql
3
4# only if you want still need legacy functions
5\i /usr/share/postgresql/9.4/contrib/postgis-2.3/legacy.sql

The legacy functions haven't changed in a while, so if you don't have the old folder (in this example 2.1), it's generally good enough to just use the new uninstall_legacy.sql.

REASON 2: You have an older postgis_sfcgal installed. If your postgis_sfcgal version was installed with extensions. Which would be postgis 2.2 or above, you can run:

1ALTER 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.

1ALTER EXTENSION postgis_sfcgal UPDATE TO "2.3.2";

REASON 3: 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.

1ALTER EXTENSION postgis UPDATE;

REASON 4: You did something unorthodox like you managed to install an old PostGIS version without extensions and a new one with extensions. Or 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

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

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

1|   probin            | count |
2|---------------------|-------|
3|$libdir/postgis-2.4  |   446 |

If you have more than one row, such as:

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

then run this query to list the offending functions replacing the postgis-2.1 with the lower version you have listed.

1SELECT probin, proname, prosrc
2FROM pg_catalog.pg_proc
3WHERE probin LIKE '%/postgis-2.1%';