4

I just wanted to check and see if anybody else out there encountered this issue before I started diving into the code. I'm using osm2pgsql to import OSM data into my Postgres database. I encounter the following exception at what seems like the end of the process:

Server side log:

LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.


CONTEXT:  COPY planet_osm_polygon, line 162
STATEMENT:  COPY planet_osm_polygon (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.


CONTEXT:  COPY planet_osm_roads, line 145
STATEMENT:  COPY planet_osm_roads (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
LOG:  incomplete message from client
CONTEXT:  COPY planet_osm_polygon, line 162
STATEMENT:  COPY planet_osm_polygon (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
LOG:  incomplete message from client
CONTEXT:  COPY planet_osm_roads, line 145
STATEMENT:  COPY planet_osm_roads (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.


CONTEXT:  COPY planet_osm_rels, line 171703
STATEMENT:  COPY planet_osm_rels FROM STDIN;

LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.


CONTEXT:  COPY planet_osm_line, line 159
STATEMENT:  COPY planet_osm_line (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
LOG:  incomplete message from client
CONTEXT:  COPY planet_osm_rels, line 171703
STATEMENT:  COPY planet_osm_rels FROM STDIN;

LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.


CONTEXT:  COPY planet_osm_point, line 12884252
STATEMENT:  COPY planet_osm_point (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","capital","construction","covered","culvert","cutting","denomination","disused","ele","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","poi","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tunnel","water","waterway","wetland","width","wood","z_order",tags,way) FROM STDIN
LOG:  incomplete message from client
CONTEXT:  COPY planet_osm_line, line 159
STATEMENT:  COPY planet_osm_line (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
LOG:  incomplete message from client
CONTEXT:  COPY planet_osm_point, line 12884252
STATEMENT:  COPY planet_osm_point (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","capital","construction","covered","culvert","cutting","denomination","disused","ele","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","poi","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tunnel","water","waterway","wetland","width","wood","z_order",tags,way) FROM STDIN
LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.


LOG:  unexpected EOF on client connection with an open transaction
LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.


LOG:  unexpected EOF on client connection with an open transaction
ERROR:  unexpected EOF on client connection with an open transaction
CONTEXT:  COPY planet_osm_rels, line 171703
STATEMENT:  COPY planet_osm_rels FROM STDIN;

ERROR:  unexpected EOF on client connection with an open transaction
CONTEXT:  COPY planet_osm_line, line 159
STATEMENT:  COPY planet_osm_line (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
ERROR:  unexpected EOF on client connection with an open transaction
CONTEXT:  COPY planet_osm_roads, line 145
STATEMENT:  COPY planet_osm_roads (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
LOG:  could not send data to client: No connection could be made because the target machine actively refused it.


STATEMENT:  COPY planet_osm_rels FROM STDIN;

LOG:  could not send data to client: No connection could be made because the target machine actively refused it.


STATEMENT:  COPY planet_osm_line (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
LOG:  could not send data to client: No connection could be made because the target machine actively refused it.


STATEMENT:  COPY planet_osm_roads (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
ERROR:  unexpected EOF on client connection with an open transaction
CONTEXT:  COPY planet_osm_polygon, line 162
STATEMENT:  COPY planet_osm_polygon (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
LOG:  could not send data to client: No connection could be made because the target machine actively refused it.


STATEMENT:  COPY planet_osm_polygon (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","construction","covered","culvert","cutting","denomination","disused","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tracktype","tunnel","water","waterway","wetland","width","wood","z_order","way_area",tags,way) FROM STDIN
ERROR:  unexpected EOF on client connection with an open transaction
CONTEXT:  COPY planet_osm_point, line 12884252
STATEMENT:  COPY planet_osm_point (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","capital","construction","covered","culvert","cutting","denomination","disused","ele","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","poi","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tunnel","water","waterway","wetland","width","wood","z_order",tags,way) FROM STDIN
LOG:  could not send data to client: No connection could be made because the target machine actively refused it.


STATEMENT:  COPY planet_osm_point (osm_id,"access","addr:housename","addr:housenumber","addr:interpolation","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","brand","bridge","boundary","building","capital","construction","covered","culvert","cutting","denomination","disused","ele","embankment","foot","generator:source","harbour","highway","historic","horse","intermittent","junction","landuse","layer","leisure","lock","man_made","military","motorcar","name","natural","office","oneway","operator","place","poi","population","power","power_source","public_transport","railway","ref","religion","route","service","shop","sport","surface","toll","tourism","tower:type","tunnel","water","waterway","wetland","width","wood","z_order",tags,way) FROM STDIN
FATAL:  connection to client lost
FATAL:  connection to client lost
FATAL:  connection to client lost
LOG:  could not send data to client: No connection could be made because the target machine actively refused it.


FATAL:  connection to client lost
FATAL:  connection to client lost
FATAL:  connection to client lost

Client side log:

osm2pgsql SVN version 0.83.0 (64bit id space)

Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_osm_point
NOTICE:  table "planet_osm_point" does not exist, skipping
NOTICE:  table "planet_osm_point_tmp" does not exist, skipping
Setting up table: planet_osm_line
NOTICE:  table "planet_osm_line" does not exist, skipping
NOTICE:  table "planet_osm_line_tmp" does not exist, skipping
Setting up table: planet_osm_polygon
NOTICE:  table "planet_osm_polygon" does not exist, skipping
NOTICE:  table "planet_osm_polygon_tmp" does not exist, skipping
Setting up table: planet_osm_roads
NOTICE:  table "planet_osm_roads" does not exist, skipping
NOTICE:  table "planet_osm_roads_tmp" does not exist, skipping
Using built-in tag processing pipeline
Allocating memory for sparse node cache
Node-cache: cache=1650MB, maxblocks=0*211201, allocation method=8192
Mid: pgsql, scale=100 cache=1650
Setting up table: planet_osm_nodes
NOTICE:  table "planet_osm_nodes" does not exist, skipping
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_nodes
_pkey" for table "planet_osm_nodes"
Setting up table: planet_osm_ways
NOTICE:  table "planet_osm_ways" does not exist, skipping
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_ways_
pkey" for table "planet_osm_ways"
Setting up table: planet_osm_rels
NOTICE:  table "planet_osm_rels" does not exist, skipping
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_rels_
pkey" for table "planet_osm_rels"

Reading in file: north-america-latest.osm.pbf
Processing: Node(610466k 131.5k/s) Way(39617k 1.07k/s) Relation(171710 11.37/s)

I'm using the latest cygwin build of osm2pgsql:

https://vanguard.houghtonassociates.com/browse/OSM-OSM2PSQL-60/artifact

My commandline parameters:

./osm2pgsql.exe -v -c -d osm -U osm -H localhost -P 5432 -S default.style -s -C 1650 --hstore -r pbf north-america-latest.osm.pbf

Data file is from:

http://download.geofabrik.de/north-america-latest.osm.pbf - 5GB

My server has a 6core AMD processor, TB harddrive, and 12GB of memory.

OS: 64bit Windows 7 Professional

Any input is greatly appreciated. Thanks!

  • Amit Naidu: Why did you tag this with spatialite? – BradHards Jan 1 '14 at 6:03
  • Having the same problem with download.geofabrik.de/europe/austria-latest.osm.pbf (313 MB) – tautologe Jan 7 '14 at 15:26
  • Brad, I think I was just trying to prevent others, who tried tried and failed like this with osm2pgsql on x64 Windows, from spending time on sqlite as a fallback (for Geoserver). As I already found that the spatialite Geoserver extension doesn't support 64-bit JVM, I thought it may save them some time. So in a roundabout way, I felt that the same Win64 deficiency exists with both postGIS and spatialite. But feel free to remove if inappropriate. – Amit Naidu Jan 24 '14 at 20:49
2

Sorry, not an answer, but I want to confirm I am seeing the exact same connection lost issue, in the same pbf file and have been unable to load it for 2 weeks now. It fails after about 20 hours with this error. I will edit this if I find a cause or a solution. Right now I suspect that the pbf file is bad or its a bug.

Tests that worked so far:

  • Tested a small 11MB city pbf to Postgres, that imported fine in about 5 minutes
  • Tested importing this pbf into sqlite, and that seemed to work fine, I ended up with a 17GB .sqlite file. Except this is unusable for me because Geoserver 2.3.5 doesn't have a functional sqlite plugin for 64-bit Windows JVM. The spatialite community extension has been deceptively putting the exact same native dll for both 32 and 64, causing a lot of confusion and frustration.

    INFO   | jvm 1    | 2013/08/28 15:46:23 | java.lang.UnsatisfiedLinkError:
    C:\Windows\System32\config\systemprofile\AppData\Local\Temp\sqlite-3.7.2-sqlitejdbc.dll: 
    Can't load IA 32-bit .dll on a AMD 64-bit platform
    

I am using :

  • The Cygwin version of osm2pgsql 0.83
  • Windows 2008R2 Server x64
  • Quad core, 16GB RAM, 200GB free space
  • North America pbf from Geofabrik 5,929,305,075 bytes
  • Postgres 9.2.4 x64
  • PostGIS 2.0.3 x64

osm2pgsql -K -s -v -c -C 1600 -r pbf -d NorthAmericaOpenStreetMaps -U postgres -H localhost -P 5432 -S default.style north-america-latest.osm.pbf > osm2pgsql-log.txt 2>&1

Plus I don't understand why it doesn't accept a cache parameter of over 2GB.

I've also optimized Postgres for this load:

  • Autovaccum off
  • maintenance_work_mem 512MB
  • shared_buffers 2048MB
  • work_mem 256MB
  • checkpoint_completion_target 0.9
  • checkpoint_segments 64

I think this tool simply was never tested in Windows and is just not meant to be used on Windows. See the wiki. Especially since it has no maintainer. So it will have to wait until I can get my Linux VM up.

Update

I just realized after multiple runs that the errors reported in the pg_log are always the same line numbers. Then I looked at the OPs errors and they are the same too. So it must be something in the pbf file.

LOG: unexpected EOF on client connection with an open transaction LOG: could not receive data from client: No connection could be made because the target machine actively refused it.

CONTEXT:  COPY planet_osm_roads, line 145
LOG:  incomplete message from client
CONTEXT:  COPY planet_osm_point, line 8416609
LOG:  incomplete message from client
CONTEXT:  COPY planet_osm_line, line 159
LOG:  incomplete message from client
CONTEXT:  COPY planet_osm_rels, line 171601
CONTEXT:  COPY planet_osm_polygon, line 162
FATAL:  connection to client lost

All lines have the exact same timestamp, so I think there is some bad data which aborts the entire thing, all five tables report connection lost. Could be just one of those lines that is bad. So we need to edit or remove or ignore that line, but how? An xml file you might edit, but pbf?

  • 1
    Although this is not an answer, it provides useful information that could lead to an answer (+1). Thank you for making the effort to pursue this problem and document your progress. – whuber Feb 3 '14 at 13:34
  • Just happen to come across this. I was trying with the latest version of north-america-latest.pbf and it failed after about 20 hours during the "Relation" stage. Didn't throw any errors, but just quit. – GravitaZ May 14 '15 at 13:28
0

I've received the same issue while trying to run postgresql in docker container. The default device mapper "Data Space Available" is 100G.

  1. The size of docker container is 99.99G.
  2. Postresql tried to sync more data to disk.
  3. Device mapper couldn't resize container device-mapper: thin: 253:0: switching pool to out-of-data-space.
  4. Ext4 received many errors and stopped journal Aborting journal on device dm-1-8.
  5. Postgresql tried to write core dump, but failed (root partition is full).
  6. Root partition remounted read-only EXT4-fs (dm-1): Remounting filesystem read-only.

The solution is:

  1. Use huge hdd DOCKER_OPTS="-g /mnt/huge_hdd/docker --storage-opt dm.loopdatasize=4000G".
  2. Use separate partition for postgresql data (with 3950G size).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.