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

[weston mccarron]

Well let's see. I'm running it on a VPS provided by a2hosting. OS is Ubuntu
14.04. Not much else going on on the VPS except postgres and a few python
scripts run nightly by cron (the scripts download flat file data and load
it into the database).

I used apt-get to install everything from the postgres repository.
Previously (on the 9.3 install) I had messed a little with the memory
settings in the conf file, but I reverted to completely vanilla
installation (9.4) last night and still have the exact issue. Like I
mentioned, I disabled ssl in the conf today and had the same behavior with
a slightly different error message.

The database is nothing too special. UTF8 encoding, but otherwise default.
Last night before trying the raster load again, I reimported one schema
with three polygon tables (previously imported from shapefiles). Otherwise
the database is empty. Just adminpack, postgis and postgis-topology
extensions loaded. I'm trying to load the raster into a new table in a
completely empty schema.

>From the output, it appears as if it completes every command in the sql
file generated by raster2pgsql. It finishes the inserts, creates the index,
does an ANALYZE, then adds all the constraints one-by-one. Finally, it must
be when it tries to commit the transaction that it croaks.



On Tue, Jun 2, 2015 at 2:58 PM, Bborie Park <dustymugs at gmail.com> wrote:

> 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
>>
>
>
> _______________________________________________
> 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/c634e2c5/attachment.html>