Page MenuHomePhabricator

Update osm2pgsql to 0.90+
Closed, ResolvedPublic


We are using 0.86, and per @Pnorman:

<pnorman> I suggest you update to 0.88.1, earlier versions have an off-by-one error which can result in invalid geometries as updates occur and some nodes are in cache and others are in storage, and an invalid geometry can potentially cause an exception in ST_PointOnSurface which you use a lot with vector tile generation

Event Timeline

Yurik raised the priority of this task from to Needs Triage.
Yurik updated the task description. (Show Details)
Yurik added subscribers: Yurik, Pnorman.

A fresh import offers some advantages

  • Invalid geometries will be fixed
  • Tables are re-clustered
  • Tables are clustered with GeoHash, which is 10-20% faster
  • Indexes are recreated, removing bloat
  • The new z_order logic is used
  • You should periodically reimport anyways, and this will test that you are able to do so

If you use the existing database, the pending removal migration at needs to be applied. The z_order changes should also be applied, but this is not essential.

To expand on the invalid geometries, an invalid geometry may cause ST_PointOnSurface to error and stop the query for the layer for the entire tile. Not all invalid geometries will cause this error, and the geometry errors introduced by tend to fall into this class.

0.88.1 is recommended anyways, as it needs a smaller database, using in-memory pending, which also cuts down on disk accesses. As well, deduplication ( can cut down on disk accesses in updates, potentially significantly for longer update intervals.

Yurik triaged this task as High priority.Apr 30 2016, 11:38 PM
Yurik added a subscriber: Gehel.

This is becoming a bit more pressing now, as we try to expand into alternative OSM db schemas (like being able to query for a geometry based on wikidata ID). @Pnorman, should we persue v0.90.x instead?

Adding a column doesn't require an osm2pgsql upgrade, but without lua transforms you'll have to do all normalization of that column in SQL.

I'm not sure how much normalization you need for Wikidata ID

@Pnorman, we probably don't need as much normalization for WikidataID, but we definitly want to move away from the original default schema and more into the clear-tables direction and other similar efforts, and for that Lua would be needed. My question was mostly if we should pursue 0.88 or 0.90.

If you're going to use the multi backend, 0.90.0 is recommended. If you're using the pgsql backend with Lua transforms, 0.86 and later support that.

Because Debian testing and stable-bpo have moved to 0.90.0, I'd go for that.

For what is worth, I support the jump to 0.90.0 as well, given it is in jessie-backports.

Gehel moved this task from Backlog to In progress on the Maps-Sprint board.

Change 287600 had a related patch set uploaded (by Gehel):
WIP - Upgrade osm2pgsql to 0.90.0

MaxSem renamed this task from Update osm2pgsql to 0.88.1 + to Update osm2pgsql to 0.90+.May 26 2016, 10:15 PM

Test in progress on new maps servers. Upgrade was manually applied on the server to validate before pushing this to puppet.

Change 293475 had a related patch set uploaded (by Gehel):
explicitely set input reader format in osm2pgsql

Change 293475 merged by Gehel:
explicitely set input reader format in osm2pgsql

Change 287600 merged by Gehel:
Upgrade osm2pgsql to 0.90.0

Seems like its in prod, looks good, thx :)