I imported a planet pbf with osm2pgsql using this command line:

osm2pgsql -c -k -d <dbname> -s -G -K -U <user> -W -H <host> -S <custom.style> planet-200224.osm.pbf

I'm now trying to update with minutely diffs and keep getting duplicate key errors no matter what settings I try. The command line for diffs is:

osmosis --rri workingDirectory=/mnt/OSM/osmosis-minutely/ --sc --wxc - | osm2pgsql -a -k -d <dbname> -P 5432 -s -G -K -U <user> -W -H <host> -S custom.style -

I'm using 64-bit osm2pgsql (v 1.2.1). I've tried adding swap space, specifying multiple -C values (2000 up to 16000), number of processes from 1 to 8, importing 60 seconds at a time up to 1 hour, always the same error.

The error I'm getting is this:

alt text

Thanks in advance for any assistance.

asked 12 Mar '20, 22:42

skmoore's gravatar image

accept rate: 0%

edited 12 Mar '20, 22:43

Is there any danger in removing the unique constraint on the id column from the nodes/ways/rels tables in Postgres to work around this?

(13 Mar '20, 23:09) skmoore

What's in the change file that you get the error with?

(13 Mar '20, 23:42) SomeoneElse ♦

I get it with any change file. Whether its minutely/hourly, etc. I have it scheduled with cron and I've let it run for several hours, get the same error for each changeset.

I have seen it successfully process a minutely diff a couple of times though, which is weird. That makes me think it's more of a RAM issue on the server, but there appears to plenty of free RAM...

It's a 4 core 16gb ec2 instance connecting to an RDS Postgres instance.

(13 Mar '20, 23:45) skmoore

(perhaps I should have been more precise in the question).

What data is there in the change file that is interpreted as a duplicate key?

(13 Mar '20, 23:47) SomeoneElse ♦

Seems to almost always be a node (think I saw an error on a way a couple times). I've checked the nodes table in the database for several of the duplicate features, some exist there and some do not.

(13 Mar '20, 23:49) skmoore

For example, the last time I ran it I got this error, the node is question does not exist in the Postgres database:

Processing: Node(170k 0.2k/s) Way(0k 0.00k/s) Relation(0 0.00/s)DB writer thread failed due to ERROR: result COPY_END for planet_osm_nodes failed: ERROR: duplicate key value violates unique constraint "planet_osm_nodes_pkey" DETAIL: Key (id)=(33442470) already exists. CONTEXT: COPY planet_osm_nodes, line 40

(13 Mar '20, 23:55) skmoore
showing 5 of 6 show 1 more comments

You need to remove duplicates from the change file before importing into osm2pgsql. In osmosis the relevant option is called --simplify-change (--simc). So your command line should be this:

osmosis --rri workingDirectory=/mnt/OSM/osmosis-minutely/ --simc --wxc - | ...
permanent link

answered 15 Mar '20, 10:46

lonvia's gravatar image

accept rate: 41%

Interesting, that seems to have worked but I don't recall needing that option in the past. Thanks so much for your help!

(17 Mar '20, 06:06) skmoore
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 12 Mar '20, 22:42

question was seen: 437 times

last updated: 17 Mar '20, 06:06

powered by OSQA