could not access file "$libdir/plpgsql" while creating template_postgis

Question

I have installed PostgreSQL and Postgis on my Mac before when it's 9.3 and 2.1 but never used. After upgrading pg to 4.5_2 and postgis to 2.2 (the latest now), I try to build my first postgis based app and creating template_postgis like this:

createdb template_postgis
psql -d template_postgis -f /usr/local/share/postgis/postgis.sql

And got this error:

SET
BEGIN
psql:/usr/local/share/postgis/postgis.sql:77: ERROR:  could not access file "$libdir/plpgsql": No such file or directory
...

So I run pg_config --libdir it says /usr/local/lib, and pg_config --pkglibdir returns /usr/local/lib/postgresql. And I found plpgsql.so under pkglibdir not libdir, so I create a symbol link named plpgsql.so and another plpgsql under libdir, but it still not works.

What really is the problem there? How can I fix this?

My Mac is running the newest OS X 10.11 and both of them are installed and upgraded by homebrew.

Update: Inspired by @Stefan D. , I run psql -c "\dx" to list all the installed extensions which results:

  Name   | Version |   Schema   |                             Description
---------+---------+------------+---------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 2.1.7   | public     | PostGIS geometry, geography, and raster spatial types and functions

So I run ALTER EXTENSION postgis UPDATE TO "2.2.0"; to manually update the version of postgis, but got another error: could not open extension control file "/usr/local/Cellar/postgresql/9.4.5/share/postgresql/extension/postgis.control": No such file or directory.

This is wired as my PostgreSQL is installed in /usr/local/Cellar/postgresql/9.4.5_2/. And postgis.control is not listed in /usr/local/Cellar/postgresql/9.4.5_2/share/postgresql/extension/ either.

After locating, I find all the .control files have an symbol link in /usr/local/share/postgresql/extension/.

Answer 1

What a coinincidence: I just had a similar problem and were searching for a solution when I found your question.

I also updated my postgresql/postgis installation a couple of days ago, and when I wanted to use my database (which I created with before the update) it today I got an error similiar to yours (for me it was that the postgis 2.1 library could not be found)

Meanwhile I could fix it. Here is how I did it:

My problem was: The database was set up with the old Postgresql/postgis version. And it still looks for the old libraries. You can check this by listing all your extensions with the following command:

psql --username=gisuser --dbname=gis -c "\dx"

For me this gave me this output:

                                         List of installed extensions
       Name       | Version |   Schema   |                             Description                             
------------------+---------+------------+---------------------------------------------------------------------
 plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis          | 2.1.7   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_topology | 2.1.7   | topology   | PostGIS topology spatial types and functions
(3 rows)

As you can see the postgis and postgis_topology extension versions (2.1.7) do not fit my installed versions (2.2.0) To fix that I executed the following commands to update the extension versions:

psql --username=gisuser --dbname=gis -c "ALTER EXTENSION postgis UPDATE;"
psql --username=gisuser --dbname=gis -c "ALTER EXTENSION postgis_topology UPDATE;"

You will probably have to alter the command a bit (username, database, and the extension you want to update)

Check whether it worked again with: psql --username=gisuser --dbname=gis -c "\dx"

Now the output is:

                                         List of installed extensions
       Name       | Version |   Schema   |                             Description                             
------------------+---------+------------+---------------------------------------------------------------------
 plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis          | 2.2.0   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_topology | 2.2.0   | topology   | PostGIS topology spatial types and functions
(3 rows)

(Note the changed version number for postgis and postgis_topology)

Answer 2

Finally, I got out what caused this issue: Although there's only one postgres installed on my Mac, but there are TWO postgres exits!

What really happened is I have upgraded postgres program without shutting down, neither brew did, so I have a postgres 9.4.5_2 installed locally, and an old one in the memory!

So the solutions is really simple: Shutting down the old one and start the new installed postgres!

Anyway, thanks for your kind help!

Answer 3

Probably You have installed multipe instances of Postgres on Your Mac.

When you execute pg_config --pkglibdir, make sure it's the one associated with your installation. Run:

which pg_config