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

[Bborie Park]

As you indicated, the problem has to do with psql or more specifically your
database. You'll need to provide more info, such as your postgres
configuration and hardware specs, for anyone to provide any useful
suggestions.

-bborie

On Tue, Jun 2, 2015 at 1:43 PM, weston mccarron <quantumpsychotics at gmail.com
> wrote:

> 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?
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150602/fff0b1d9/attachment.html>