When I run the command:
raster2pgsql -s 4269 -a -R -F -t auto path_to_my_file.tif public.agdd_2016 | psql -U postgres -d climate -h server_ip -p 5432 --pass
I get the following error:
Processing 1/1: /geo-data/gridded_models/agdd/agdd_20161021_base_thirtytwo_f.tif
INFO: Using computed tile size: 93x72
BEGIN
ERROR: new row for relation "agdd_2016" violates check constraint "enforce_out_db_rast"
DETAIL: Failing row contains (3123162, 0100000100DCC660BBAF7A963FDCC660BBAF7A96BFBD8A315555415FC0000000..., agdd_20161021_base_thirtytwo_f.tif, null, null, null).
ERROR: current transaction is aborted, commands ignored until end of transaction block
How do I fix this? I have tried running this query on my database:
SET postgis.enable_outdb_rasters TO True;
I have also tried adding the following to /etc/postgresql/9.4/main/postgresql.conf and then rebooting postgres
postgis.enable_outdb_rasters = True
- Update -
I have verified that the following query returns true, but the error in question still persists:
SHOW postgis.enable_outdb_rasters;
I also noticed that if I drop the enforce_out_db_rast constraint, the raster2psql command fails on the next constraint that exists on the table so I'm now not so sure that this problem is specific to enforce_out_db_rast.
This error only started happened after upgrading from PostgreSQL from 9.4.5 to 9.4.10. Before that I was not getting any constraint violations.
select version(); (before and after upgrade)
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
PostgreSQL 9.4.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
SELECT PostGIS_Version(); (before and after upgrade)
2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
I'm not sure which version of gdal (raster2pgql) I have but maybe that's where the issue is? I also currently have two server snapshots one pre upgrade and functional and the other post upgrade which exhibits the issues.