I have a local installation of PostgreSQL 16 on a windows-Computer from EBD (https://www.enterprisedb.com/downloads/postgres-postgresql-downloads). I am struggling with the ogr2ogr tool when exporting Geometries from the Postgres Database. It used to work within our application for the last 15 years-. But not now any more.
I tried with the following Versions:
- C:\apps\PostgreSQL16円\bin>ogr2ogr.exe --version
- returns: GDAL 3.7.1, released 2023年07月06日
- C:\apps\PostgreSQL13円\bin>ogr2ogr.exe --version
- returns: GDAL 3.4.3, released 2022年04月22日
- ogr2ogr on windows server
- returns 3.2.3 But this is only a windows Problem. On our Debian/Unix production Servers I can use the ogr tool from the Postgres-Installation.
(one of) the call I use is the following:
C:\apps\PostgreSQL16円>bin\ogrinfo.exe PG:"service='local-postgres'"
It seems to me that the Postgres-Installer does not include the Postgres-Driver. Can this be possible? I always get the error:
FAILURE:
Unable to open datasource `PG:service='local-postgres'' with the following drivers.
-> `PCIDSK'
-> `PDS4'
-> `VICAR'
-> `JP2OpenJPEG'
-> `PDF'
-> `MBTiles'
-> `EEDA'
-> `OGCAPI'
-> `ESRI Shapefile'
-> `MapInfo File'
-> `UK .NTF'
-> `LVBAG'
-> `OGR_SDTS'
-> `S57'
-> `DGN'
-> `OGR_VRT'
-> `Memory'
-> `CSV'
-> `GML'
-> `GPX'
-> `KML'
-> `GeoJSON'
-> `GeoJSONSeq'
-> `ESRIJSON'
-> `TopoJSON'
-> `OGR_GMT'
-> `GPKG'
-> `SQLite'
-> `ODBC'
-> `WAsP'
-> `PGeo'
-> `MSSQLSpatial'
-> `OpenFileGDB'
-> `DXF'
-> `CAD'
-> `FlatGeobuf'
-> `Geoconcept'
-> `GeoRSS'
-> `VFK'
-> `PGDUMP'
-> `OSM'
-> `GPSBabel'
-> `OGR_PDS'
-> `WFS'
-> `OAPIF'
-> `EDIGEO'
-> `SVG'
-> `Idrisi'
-> `XLS'
-> `ODS'
-> `XLSX'
-> `Elasticsearch'
-> `Carto'
-> `AmigoCloud'
-> `SXF'
-> `Selafin'
-> `JML'
-> `PLSCENES'
-> `CSW'
-> `VDV'
-> `MVT'
-> `NGW'
-> `MapML'
-> `Parquet'
-> `Arrow'
-> `GTFS'
-> `TIGER'
-> `AVCBin'
-> `AVCE00'
-> `HTTP'
C:\apps\PostgreSQL16円>
I even tried as Administrator or on Powershell but this did not solve the problem
After some research I found some users that installed GDAL from OSGeo4W. This I tried and that solved the case.
C:\OSGeo4W>bin\ogrinfo.exe PG:"service='local-postgres'"
INFO: Open of `PG:service='local-postgres''
using driver `PostgreSQL' successful.
1:
Or do i miss something else? That does not sound reasonable to not be able to connect with the Postgres 16 (or 13) ogr binaries to the same Postgres-Installation.
One speciality: I updated the Postgres 16 Installation two times with the GDB-Installer from 16.0 to 16.1. and 16.2 as documented. But that should not be the cause.
1 Answer 1
The windows installer from Enterprise-DB (https://www.enterprisedb.com/downloads/postgres-postgresql-downloads) has the ogr2ogr / ogrinfo included but without the postgres-driver. Not since Postgres 11. I am pretty sure that it was included with PostgreSQL 9.6 (which was still installed but disabled on some servers and correctly used).
Our solution:
- I downloaded the OSGeo4W installer of OSGeo4W and installed the "gdal: The GDAL/OGR library and commandline tools". With this ogr2ogr-Version the PostgreSQL-Driver was included, so I only had to change the Path from the postgres/bin path to the C:\OSGeo4W\bin Path to start/use the ogr2ogr
- As an alternative we use QGIS (also has bin/ogr2ogr.exe) which is already installed on most of the computers we use and where we need it.
There were some follow-up problems, e.g. the proj.db was used from the Postgres Installation as it was still on the PATH
- ogr2ogr OUTPUT >> ERROR 1: PROJ: proj_create_from_database: C:\apps\PostgreSQL16円\share\contrib\postgis-3.4\proj\proj.db contains DATABASE.LAYOUT.VERSION.MINOR = 2 whereas a number >= 3 is expected. It comes from another PROJ installation. This was adressed and solved in this Stack-Overflow-Answer
I had the same issue, I removed the environment variable of the GDAL from Postgres installation. Environment variable with OSGeo package working perfectly now.is this an issue for you too