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 ?

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