NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

I have a daily cron job that updates the Great Britain extract in my PostGIS database using data from Geofabrik. The command is

osmosis -q --read-replication-interval workingDirectory=$HOME/maps/replication --write-pgsql-change database=osm

and the configuration file is

# -*- default-generic -*-

# The URL of the directory containing change files.
baseUrl=http://download.geofabrik.de/europe/great-britain-updates/

# Defines the maximum time interval in seconds to download in a single invocation.
# Setting to 0 disables this feature.
#maxInterval = 0
maxInterval = 86400

(I had to set maxInterval early on, to take only one update at a time, but now even setting it to 100 isn't helping).

This has worked very nicely for the last few years, but recently it has started failing with

org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO actions(data_type, action, id) VALUES(?, ?, ?)]; ERROR: duplicate key value violates unique constraint "pk_actions"
  Detail: Key (data_type, id)=(N, 469786539) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pk_actions"
  Detail: Key (data_type, id)=(N, 469786539) already exists.

It's as if items are being thrown into actions but without taking account of the version numbers or something. When I connect using the psql client, the actions table is empty, so it's not a matter of stale data causing the problem.

If it helps, my state.txt now has

#Fri Feb 03 05:31:32 UTC 2017
sequenceNumber=1415
timestamp=2017-02-01T21\:21\:02Z

How can I get my database updating again?

asked 15 Feb '17, 20:33

tms13's gravatar image

tms13
16113
accept rate: 0%


This appears to be a side effect of the switch to a new extract software at Geofabrik. The download dataset was re-synced with a current planet file at the time. This apparently led to some diff files containing some objects twice. This doesn't hurt osm2pgsql imports but trips up osmosis. Either re-initialize your database from a current data set, or use the --simplify-change flag in Osmosis to flatten the diff before applying to the database.

permanent link

answered 15 Feb '17, 20:38

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

Perfect - I put --simplify-change immediately before --write-pgsql-change and it worked! I still need the maxInterval, else it fails with "Pipeline entities are not sorted", but I can probably live with that.

(15 Feb '17, 21:47) tms13

Follow this question

By Email:

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

By RSS:

Answers

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:

×252
×40
×11
×1

question asked: 15 Feb '17, 20:33

question was seen: 2,408 times

last updated: 15 Feb '17, 21:47

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum