[postgis-users] raster2pgsql problem - SSL SYSCALL error EOF detected

[weston mccarron]

Hey folks. I've done some searching on this, and I can't seem to find
evidence of anyone else having this problem, but I can very reliably
duplicate it.

Several months ago, I loaded a bunch of raster data into my 9.3 Postgres
database with PostGIS 2.1 (I think it was 2.1.2 at the time). I used the
following commands to simultaneously import 4 tif files (continental US,
AK, PR and HI):

raster2pgsql -d -I -C -M -R /path/to/gmted75*.tif -F -t 100x100 trn.gmted75
| psql -Uadmin --password -hlocalhost mydatabase

Everything appeared to work great, except that access was too slow for it
to be practical for my intended use. I wondered if shrinking my tile size
would help, since I'm only ever pulling single point values (with
ST_Value). So yesterday, I tried the following:

raster2pgsql -d -I -C -M -R /path/to/gmted75*.tif -t 10x10 trn.gmted75 |
psql -Uadmin --password -hlocalhost mydatabase

The only things I changed were the tile size, and I figured I probably
didn't need the filename saved in each record (so I got rid of the -F flag).

But the process went for a long time and then said my connection was lost.
I tried again, first sending the raster2pgsql output to a file, then
loading the file with psql. The file output completed just fine, but when I
tried loading it in psql, it would go for almost an hour, gobble up my hard
drive space, and then right at the end, give me the following error:

SSL SYSCALL error: EOF detected

I've now tried it a bunch of different ways. One tif instead of all four at
once, logging in and using \i vs. just running it from the shell with the
-f flag on psql...

Each time I run the command I lose about 1 GB of hard drive space that I
can't seem to recover. It's being used by postgres's data directory, but
VACUUMing FULL, restarting the service, rebooting the server, etc, don't
seem to recover it. And there's nothing showing in my database schema that
indicates anything there hogging all that space.

I finally deleted my database (which freed the disk space), uninstalled
postgres 9.3, and did a fresh install of 9.4 with PostGIS 2.1.7. I set up a
fresh database and tried again. Same thing. I edited my conf file to turn
ssl off and tried again. It behaved exactly the same except now the error
message was:

psql:gmted75_US_cont.sql:4866054: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:gmted75_US_cont.sql:4866054: connection to server was lost


Any ideas? Is the transaction maybe just too large to do at once?

Am I even on the right track to try smaller tiles to speed up my ST_Value
access?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150602/2b35fd63/attachment.html>