[mapserver-users] Help debugging PostGIS raster connection

[Jeff McKenna]

Hi Thomas,

I would try the following:

- remove all single quotes from your DATA statement (most times they are 
unnecessary for my use, and cause me problems)
- remove the WHERE clause
- also try to access another table from your database, but not raster, 
using that same DATA connection statement (I bet the problem has nothing 
to do with the raster table, but instead is your connection parameters)
- verify that remote connections can access your database (check your 
file pg_hba.conf)

hope that brings ideas and opens doors...

-jeff



-- 
Jeff McKenna
MapServer Consulting and Training Services
https://gatewaygeomatics.com/


On 2019-03-04 2:38 PM, Thomas Horner wrote:
> I have a PostGIS connection string that works fine in GDAL, as I get a 
> raster response when running
> ---
> gdalinfo "PG:host=... port=5432 user='...' password='...' 
> dbname='weather_models' schema='rasters' 
> table='wpc_snow_50pct_1551268800' column='rast' mode=1 
> where='timestamp=\'2019-03-02 00:00:00\''"
> ---
> 
> On this very same server, I have cgi-mapserver running, and the output 
> from /usr/lib/cgi-bin/mapserv/ -v is
> ---
> MapServer version 7.2.1 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ 
> SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=SVG_SYMBOLS 
> SUPPORTS=RSVG SUPPORTS=ICONV SUPPORTS=FRIBIDI SUPPORTS=WMS_SERVER 
> SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT 
> SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI 
> SUPPORTS=THREADS SUPPORTS=GEOS SUPPORTS=PBF INPUT=JPEG INPUT=POSTGIS 
> INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE
> ---
> 
> The problem is, when I use that same PG:... connection string as the 
> DATA for a LAYER in MapServer, I get an unhelpful error: Unable to 
> access file. Corrupt, empty or missing file '<snip of PG connection 
> string>' Couldn't establish a database connection.
> 
> Despite turning DEBUG level to 5, and CPL_DEBUG to ON in the MAP block 
> of my mapfile, I don't get any helpful debug information whatsoever in 
> my error log -- just the connection string and "Couldn't establish a 
> database connection." I have tried a variety of where clauses and other 
> tables and get the same result. I have no idea if the actual error is 
> coming from GDAL or MapServer, despite all those strings working fine 
> when running them directly via GDAL.
> 
> By the way, it's the same error whether I use 'timestamp'= or timestamp=.
> 
> Here is the mapfile:
> ---
> MAP
>   CONFIG "MS_ERRORFILE" "/map/error.txt"
>   CONFIG "CPL_DEBUG" "ON"
>   DEBUG 5
>   PROJECTION
>    "init=epsg:4326"
>   END
>   LAYER
>    NAME wpc_snow_50pct
>    TYPE RASTER
>    STATUS ON
>    VALIDATION
>     "timestamp" ".*"
>    END
>    PROJECTION
>     "init=epsg:4326"
>    END
>    METADATA
>     "wms_title" "wpc_snow_50pct"
>     "wms_srs"   "EPSG:4326"
>     "wms_extent" "-108.5 41 -104.5 36"
>     "wms_enable_request" "*"
>    END
>    DATA "PG:host=... port=5432 dbname='weather_models' user='...' 
> password='...' schema='rasters' table='wpc_snow_50pct_1551268800' 
> column='rast' mode=1 where='timestamp=\'2019-03-02 00:00:00\''"
>    PROCESSING "BANDS=1"
>   END
> END
> ---
> 
> Any help is appreciated!
> 
> Thanks,
> Thomas
> 
>