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
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”
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
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:
\connect gisdb \i /usr/share/postgresql/9.4/contrib/postgis-2.1/uninstall_legacy.sql -- only if you want still need legacy functions \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:
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";
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.
ALTER 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
SELECT probin, COUNT(*) FROM pg_catalog.pg_proc WHERE probin LIKE '%/postgis%' GROUP BY probin ORDER BY probin;
In a good, install, you should have only one row that looks something like this. Your version number might be different.
probin | count ---------------------+------- $libdir/postgis-2.4 | 446 (1 row)
If you have more than one row, such as:
probin | count ---------------------+------- $libdir/postgis-2.4 | 446 $libdir/postgis-2.1 | 4 (1 row)
then run this query to list the offending functions replacing the postgis-2.1 with the lower version you have listed.
SELECT probin, proname, prosrc FROM pg_catalog.pg_proc WHERE probin LIKE '%/postgis-2.1%';
As of PostGIS 2.3, the postgis extension was changed to no longer allow relocation. All function calls within the extension are now schema qualified.
While this change fixed some issues with database restore, it created the issue of if you installed PostGIS in a schema other than the one you wanted to it is not intuitive how to move it to a different schema. Luckily there is a way to do this.
For this exercise, I will install PostGIS in the default schema and then demonstrate how to move it into another schema location.
You can run these steps using psql or pgAdmin or any other PostgreSQL tool you want.
This raster question comes up quite a bit on PostGIS mailing lists and stack overflow and the best answer often involves
the often forgotten
ST_Reclass function that has existed since PostGIS 2.0.
People often resort to the much slower though more flexible
ST_MapAlgebra or dumping out
their rasters as Pixel valued polygons they then filter
with WHERE val > 90,
ST_Reclass does the same thing but orders of magnitude faster.