An issue with columns (version?) in underlying proj database when trying to create an ogr_fdw server in the Postgres db

[Brent-Wood]

Hopefully all the relevant info included below!

I have built a clean Linux Mint v20.2 system, with Postgres v13, Postgis v3 and ogr_fdw installed, from the Postgres & UbuntuGIS (stable) repositories, supporting Postgres v13. All packages seem to install cleanly.

The only proj.db I can find on the computer is /usr/share/proj/proj.db

The output from running select postgis_full_version() in my test Postgres database is: POSTGIS="3.1.4 ded6c34" [EXTENSION] PGSQL="130" GEOS="3.9.1-CAPI-1.14.2" PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

However, the proj version installed on the system is supposedly v7.2.1 (proj-bin package). The metadata table in proj.db contains: DATABASE.LAYOUT.VERSION.MAJOR|1 DATABASE.LAYOUT.VERSION.MINOR|0

The proj.db ( /usr/share/proj/proj.db ) projected_crs table does not have the area_of_use_auth_name or area_of_use_code columns present. I can't see where I can find the proj version or find out which package installed this database.

I can install postgis & ogr_fdw extensions in the Postgres db, but trying to create an instance of a server results in an error (I'm using a public WFS service for testing purposes): create server wfs_server FOREIGN DATA WRAPPER ogr_fdw options ( datasource 'WFS:https://services.slip.wa.gov.au/public/services/SLIP_Public_Services/Property_and_Planning_WFS/MapServer/WFSServer?request=GetCapabilities&service=WFS', format 'WFS' );

The message: ERROR: GDAL AppDefined [1] PROJ: proj_create_from_database: SQLite error on SELECT name, coordinate_system_auth_name, coordinate_system_code, geodetic_crs_auth_name, geodetic_crs_code, conversion_auth_name, conversion_code, area_of_use_auth_name, area_of_use_code, text_definition, deprecated FROM projected_crs WHERE auth_name = ? AND code = ?: no such column: area_of_use_auth_name

I had the same issue on another system running Postgres v12, but with QGIS, mapserver, etc also installed. The new system is my attempt to progress this further, with fewer packages installed to minimise potential conflicts.

Any ideas as to how I might best resolve this?

Thanks

[pramsey]

Maybe upstream this to whomever makes the Linux Mint packages because the error seems to indicate a mismatch between the proj library and the proj.db it is bundled with. Also, I know nothing about Linux Mint packaging, but most package managers allow you to list the contents of a package, or to reverse query the package a file is in. (In RPM world, rpm -ql packagename lists the files in the package). Sorry, given the clues you have here this seems to not be an ogr_fdw issue, so I'm closing out here for now.

[robe2]

@Brent-Wood Just a note here lots of people are having issues with mixing UbuntuGIS and apt.postgresql.org.

The issue is

1) UbuntuGIS carries a newer proj and GDAL and so forth and relies on upstream ubuntu to provide the postgresql. Which means the postgis they ship is for PostgreSQL 12 and not for 13

2) apt.postgresql.org gets all it's dependencies from upstream and it offers PostGIS for all versions, but builds the dependencies against lower. I think it ships with proj 6.something. Dependencies you get with the standard Ubuntu suck - they are old (old by my standards :)) . But sadly the newer versions offered by UbuntuGIS (in particular the proj area) do not play nicely with the older versions apt.postgresql.org compiles with

So the sad choice is do you want a newer PostgreSQL or a PostGIS with the newest GEOS/GDAL/etc.

If you care way more about the GEOS/GDAL version (and want more bleeding edge there), stay away from apt.postgresql.org

If however you prefer a newer PostgreSQL and with a newer PostGIS (but kinda oldish GEOS/PROJ/GDAL), then use apt.postgresql.org and stay away from UbuntuGIS.

If you need desktopish / commandline goodies that only UbuntuGIS can provide - stay way from apt.postgresql.org

Sadly these things kinda worked together with each other in the past, but because of the incompatibility between proj 6 and proj 7, the story is very sad and depressing.

I feel like the best compromise would be if UbuntuGIS built versioned extensions building against what apt.postgresql.org offers instead of what ubuntu.org offers. That would mean they have to offer multiple versions of postgis, ogr_fdw (which apt.postgresql.org already does but built with old dependencies :) )

Of course then you'd have to play with repo priority to get your postgis from the place you want to get it from.