Causes for 'postgis.backend' is already set
'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
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 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.
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";
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;
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%';