Could not load library /usr/local/lib/postgresql/plpgsql.so .. undefined symbol "MakeExpandedObjectReadOnly"

Question

What I'm trying to do is to convert this installing script for webodm (https://gist.github.com/lkpanganiban/5226cc8dd59cb39cdc1946259c3fea6e) written in bash to be used in tcsh shell under a freenas jail.

I have now enter at part where I can't find a solution to and my hope is that someone can en light me what to do next.

The line that is triggering the problem is :

su - postgres -c "psql -d webodm_dev -c "\""CREATE EXTENSION postgis;"\"" "

The whole error line :

ERROR:  could not load library "/usr/local/lib/postgresql/plpgsql.so": dlopen (/usr/local/lib/postgresql/plpgsql.so) failed: /usr/local/lib/postgresql/plpgsql.so: Undefined symbol "MakeExpandedObjectReadOnly"

pkg info give :

postgis24-2.4.5_1              Geographic objects support for PostgreSQL databases
postgresql95-client-9.5.15_2   PostgreSQL database (client)
postgresql95-contrib-9.5.15_2  The contrib utilities from the PostgreSQL distribution
postgresql95-server-9.5.15_2   PostgreSQL is the most advanced open-source database available anywhere

And yes the file exists:

root@webodm2:~ # ls -l /usr/local/lib/postgresql/plpgsql.so
-rwxr-xr-x  1 root  wheel  195119 Feb  7 18:16 /usr/local/lib/postgresql/plpgsql.so
root@webodm2:~ # 

So anyone have some idea ?

Voiceful

Answer 1

I faced this issue after the upgrade from postgres 11 to 12, here how to fix it for Linux and Mac (without brew)

$ sudo su postgres

$ /usr/lib/postgresql/12/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/11/main \
  --new-datadir=/var/lib/postgresql/12/main \
  --old-bindir=/usr/lib/postgresql/11/bin \
  --new-bindir=/usr/lib/postgresql/12/bin \
  --old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \

you can add --check to do a dry test upgrade without changing anything in your postgres installation.

for Mac users with brew installation:

after the upgrade run the following command"

$ brew postgresql-upgrade-database

Suffragan

Answer 2

That error message means that you have a plpgsql.so from PostgreSQL 9.5 or earlier and try to use it with PostgreSQL 9.6 or later.

Either you are picking up the wrong library, or you copied files around.

Anyway, the problem has nothing to do with PostGIS.

Loferski

So this function "MakeExpandedObjectReadOnly" was introduced in 9.5 ? Its a pitty due when you install postgis 2.4 in Freebsd your postgres 9.6 install will be replaced by postgres 9.5. Once more FreeBSD show how incomplete it really is. – Voiceful

No, the function was turned into a macro, so it doesn't exist from 9.6 on. Your complaint seems to be about packaging. Why don't you build from source? – Loferski

My error is gone after I started from scratch and stuck with 9.5 branch. Thanks for your helping me out on this :). Building from source in a install script in FreeBSD is such a timehog. I want to avoid this a much as possible due I have some ideas to complete all this into a pluggin for Freenas. Other problem that I encounter is that default shell in BSD is not bash and webodm start scripts is using some Linux calls only. But right now I'm stuck in a endless rows of errors generated by phyton3. The rouths of it seems to be the call on celery -A worker .... – Voiceful

Answer 3

It might be your database has an outdated version, try to run the checks before running brew postgresql-upgrade-database. OR try to restart your service brew services restart postgres.

psql --version # 11.4 <--- psql cli version
psql -c 'select version();' postgres # 10.2 <--- db version in storage
brew info postgres # check pg info <--- found solution
brew postgresql-upgrade-database # upgrade db version in storage and fixed the issue

Serapis