Search code examples
postgresqlpostgisgoogle-cloud-sql

PostGIS installed, but unable to select version or add geography columns

I've got an existing Postgres 9.6 database on Google Cloud that has the PostGIS extension enabled, however whenever I try to create a table with a column with the geography type or select the PostGIS version I get an error.

For creating the geography column the error is non-descriptive — just an arrow pointing to the word geography in the following statement

create table place(coordinate geography(POINT,4326));

The version error looks as follows:

select postgis_full_version();
ERROR:  could not access file "$libdir/postgis-2.3": No such file or directory
CONTEXT:  SQL statement "SELECT postgis_lib_version()"
PL/pgSQL function postgis_full_version() line 22 at SQL statement

The create extension statement confirms it is already installed though e.g

create extension postgis;
error: the extension already exists

I've tried running these commands as the postgres user and as another user who's been granted permissions on the database in question but neither seem to work. Any help would be appreciated.

Solution

  • It's simpler than you think: Your hosting privider has goofed up the PostGIS installation.

    It looks like the file is really missing, or something else on the operating system level is misconfigured.

    You should gripe at Google.