[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?

[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

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[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.

[Bborie Park]

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

[weston mccarron]

The only thing showing up in the log was the following:

LOG:  checkpoints are occurring too frequently (9 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

I'm suspicious it had something to do with trying to do too much in one transaction, so I've now got raster2pgsql running with the -e flag (so there is no transaction and each insert proceeds separately). So far it's been running for 15 minutes, I'm up to about 800k records in the table, and there are no complaints in the log.

[Bborie Park]

How strange. I'm betting some hardware limitation (you didn't specify the amount of memory or disk space) or default PostgreSQL config setting is causing this behavior. I don't know which exact PostgreSQL setting but that's because I always reconfig my servers. For good performance, I strongly urge you to reconfigure that server. At the very least, take a read through...

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

-bborie

[weston mccarron]

Yeah, it may be a setting. I had read that document and done some tweaking and customizing on the initial 9.3 database where I started having the problem. I thought maybe one of my custom settings was the problem, which was one reason I tried again with a fresh install.

After about three hours, it appears to have finished just fine with the -e flag and piping raster2pgsql straight into psql. 8 million records total in the table.

Thanks a lot for your help, Bborie.