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

Tue Jun 2 14:17:33 PDT 2015

Are you tailing the postgresql server logs? There typically is more
information in there about what's causing the error.

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

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