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!
Best Answer
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 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.
I am using :
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>&1Plus I don't understand why it doesn't accept a cache parameter of over 2GB.
I've also optimized Postgres for this load:
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.
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?