ogr2ogr doesn't find PostgreSQL driver (Windows)

Question

I've seen this question several times, but I'm not 100% sure what needs to happen for my situation. Quite often the answer is to install OpenGEO4W & GDAL, but it appears I already have GDAL.

I just did a fresh install of the all in one PostgreSQL and PostGIS windows installer. PostGIS seems to be working quite fine. I'm attempting to use the ogr2ogr utility to ultimately migrate geo tables into a SpatiaLite database. I only found the OGR exectubles in one spot and that is in the pgsql bin folder.

ogr2ogr --version results in this:

GDAL 3.2.0, released 2020年10月26日

To simplify this I can generate the error I'm getting by running ogrinfo and attempt to connect to my local pgsql host, like so:

\database\PostgreSQL13円\bin>ogrinfo PG:"host=127.0.0.1 port=5432 user='postgres' password='blablabla' dbname='postgis_30_sample'"

And the instant outcome is this:

FAILURE:
Unable to open datasource `PG:host=127.0.0.1 port=5432 user='postgres' password='blablabla' dbname='postgis_30_sample'' with the following drivers.
 -> ESRIC
 -> PCIDSK
 -> PDS4
 -> VICAR
 -> JP2OpenJPEG
 -> PDF
 -> MBTiles
 -> EEDA
 -> OGCAPI
 -> ESRI Shapefile
 -> MapInfo File
 -> UK .NTF
 -> LVBAG
 -> OGR_SDTS
 -> S57
 -> DGN
 -> OGR_VRT
 -> REC
 -> Memory
 -> BNA
 -> CSV
 -> GML
 -> GPX
 -> KML
 -> GeoJSON
 -> GeoJSONSeq
 -> ESRIJSON
 -> TopoJSON
 -> OGR_GMT
 -> GPKG
 -> SQLite
 -> ODBC
 -> WAsP
 -> PGeo
 -> MSSQLSpatial
 -> OpenFileGDB
 -> XPlane
 -> DXF
 -> CAD
 -> FlatGeobuf
 -> Geoconcept
 -> GeoRSS
 -> GPSTrackMaker
 -> VFK
 -> PGDUMP
 -> OSM
 -> GPSBabel
 -> SUA
 -> OpenAir
 -> OGR_PDS
 -> WFS
 -> OAPIF
 -> HTF
 -> AeronavFAA
 -> Geomedia
 -> EDIGEO
 -> SVG
 -> CouchDB
 -> Cloudant
 -> Idrisi
 -> ARCGEN
 -> SEGUKOOA
 -> SEGY
 -> XLS
 -> ODS
 -> XLSX
 -> Elasticsearch
 -> Walk
 -> Carto
 -> AmigoCloud
 -> SXF
 -> Selafin
 -> JML
 -> PLSCENES
 -> CSW
 -> VDV
 -> MVT
 -> NGW
 -> MapML
 -> TIGER
 -> AVCBin
 -> AVCE00
 -> HTTP

So, Is OpenGEO4W needed? And what install? It seems like the standard install is a large suite of tools? It is confusing to me that the instance packaged with the PgSQL install doesn't have the driver necessary to connect to PgSQL. Is there a way to simply add the PgSQL driver for ogr?

• J. Monticolo: Welcome to GIS SE ! On which platform are you ? If windows, it seems that the compiled GDAL that you installed haven't the 'PostgreSQL' driver.
• Ian Turton: Try localhost instead of 127
• slambeth: @J.Monticolo I am on windows. The windows installer handled installing the optional utilities as part of the install process, so I guess I had hopes that it would have defaulted to including the PostGreSQL driver, but perhaps the default binary install doesn't have it.
• jbalk: Try it without the single quotes: \database\PostgreSQL13円\bin>ogrinfo PG:"host=127.0.0.1 port=5432 user=postgres password=blablabla dbname=postgis_30_sample"
• jbalk: Also if you're on windows and having problems with GDAL, you should check out miniconda. It has a good gdal package that always works for me.

Answer 1

In my case, specifically setting the output format and quoting every connectionstring component did the trick:

ogrinfo -f PostgreSQL PG:"host='127.0.0.1' port='5432' user='postgres' password='blablabla' dbname='postgis_30_sample'"

This was on Windows, using the GDAL 3.6.2 version that comes bundled inside QGIS OSGeo4W (by default installed on C:\Program Files\QGIS 3.28.3)

• leole: unfortunately this does not help me with POstgres 16, GDAL-Version 3.7.1. The "-f PostgreSQL" is an "Unknown Option name.

Answer 2

I my case on my windows machine with locally installed PostgreSQL-Server it did not help to quote the connection-String-components.

To emphasize the above comment from @slambeth (see above: The windows installer handled installing the optional utilities as part of the install process, so I guess I had hopes that it would have defaulted to including the PostGreSQL driver, but perhaps the default binary install doesn't have it):

I also learned, that the current PostgreSQL Windows Binaries don't have (as they used to have) the GDAL/ogr2ogr Postgres-Driver Utilities included. That seems to be missing. But used to be in there up to Postgres 9.6 or something like that.

I also 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.

• leole: The team from EDB - Enterprise-DB was fast and very helpful - i have a date with a technician tomorrow about this topic. I will update this answer
• leole: The OGR/GDAL tool ist not included. We will use the OSGeo4W Tool or on some Servers the ogr2ogr from the installed QGIS on the same computer (we use this very often, therefore it is already on some machines)