Re-import of the osm_mapnik database on ptolemy

List overview All Threads
Download

newer

older

non-rendering application at the...

Scripto, free software for...

Kai Krueger

18 Nov 2011 18 Nov '11

7:57 a.m.

Hello everyone, as you all probably know, the osm_mapnik database is not being updated anymore since a while. About two months ago the database seems to have become corrupted, which in turn has unfortunately prevented diffs from being applied. In order to get things up-to-date again and working more smoothly than before, the database needs to be freshly imported. During this re-import period the database will not be available for use. As a full import will likely take several days plus an additional couple of days for the diffs to catch up, there will unfortunately be a bit of an extended downtime for the database. During this time postgresql will likely be updated and I would also like to use it to see if the database can be optimized further to give better performance than before. To try and minimize disruption, I would like to ask who, or which tools, are currently using the osm_mapnik database? Is the downtime going to be an issue for anyone? In return, we will hopefully have an up-to-date database again that can be kept up-to-date. Kai

Show replies by date

Kay Drangmeister

18 Nov 18 Nov

2:38 p.m.

Hi Kai, Am 18.11.2011, 08:57 Uhr, schrieb Kai Krueger <kakrueger(a)gmail.com>om>:

...

[osm_mapnik database is not being updated anymore since a while]

...

To try and minimize disruption, I would like to ask who, or which tools, are currently using the osm_mapnik database? Is the downtime going to be an issue for anyone?

I consider the downtime lasting already for two months now, so some additional days are not worth mentioning. :o) Cheers, Kay

Kai Krueger

25 Nov 25 Nov

8:56 a.m.

Hi everyone, I think we have made some progress on updating the software stack (The re-import will occur into a postgresql 9.1 database with the latest osm2pgsql) and are nearly ready to start the import. In order to have enough space on disk for the new import, the following db tables will be dropped: "planet_line_old, planet_point_old, planet_polygon_old and planet_roads_old" These are presumably old tables that were left over from the previous import and aren't used anymore. Furthermore, the tables "planet_nodes, planet_ways and planet_rels" will be dropped, as those are only used for updating which is currently not occurring. The rendering tables will likely be kept during the import and should continue to be usable, both for rendering and other purposes. Once the import into the new db is complete and up-to-date again, the dbs will be switched and the old one deleted. Tirex for now has, however, been set to only render missing tiles to minimize the load on the database during the re-import. The import is intended to occur with the default set of db columns together with the hstore extensions. Once everything is up-to-date again, I will try and enable tile expiry at least for the 5 - 10 most used styles, but we will see how that goes. Kai On 11/18/2011 07:38 AM, Kay Drangmeister wrote:

...

Hi Kai, Am 18.11.2011, 08:57 Uhr, schrieb Kai Krueger <kakrueger(a)gmail.com>om>:

[osm_mapnik database is not being updated anymore since a while]

To try and minimize disruption, I would like to ask who, or which tools, are currently using the osm_mapnik database? Is the downtime going to be an issue for anyone?

I consider the downtime lasting already for two months now, so some additional days are not worth mentioning. :o) Cheers, Kay _______________________________________________ Maps-l mailing list Maps-l(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/maps-l

Kay Drangmeister

17 Dec 17 Dec

10:38 p.m.

New subject: hstore incomplete (was: Re-import of the osm_mapnik database on ptolemy)

Hi all The parking styles partially fail with the newly imported database. Am 25.11.2011 09:56, schrieb Kai Krueger:

...

[...] progress on updating the software stack (The re-import will occur into a postgresql 9.1 database with the latest osm2pgsql) [...]

Maybe it is due to the latest osm2pgsql.

...

The import is intended to occur with the default set of db columns together with the hstore extensions.

The problem is that not all tags are present in the hstore any more, unlike in the old database. The tags available in other columns are left out in the hstore. Here is an example for the "amenity" tag missing: osm_mapnik=> select amenity,tags->'amenity' as "hstore-amenity" from planet_polygon where osm_id=32550266; amenity | hstore-amenity ---------+---------------- parking | (1 row) here is how it looked before the new import: osm_mapnik=> select amenity,tags->'amenity' as "hstore-amenity" from planet_polygon where osm_id=32550266; amenity | hstore-amenity ---------+---------------- parking | parking (1 row) (If you happen to be a subscriber of mapnik-de there has been the same issue with osm2pgsql, but it has been changed back to the old behaviour. See thread here: https://lists.openstreetmap.de/mailman/private/mapnik-de/2011-July/000067.h… login needed.) Of course it is a space advantage not to repeat each tag existing in a regular column in the "tags" hstore again, but on the other side, stylesheets have to be always adapted if the osm2pgsql config file is changed. Plus they will no longer be portable across servers with different osm2pgsql configurations. Would it be possible to re-import the hstore? Kind regards, Kay

Stephan Knauss

18 Dec 18 Dec

12:03 a.m.

New subject: hstore incomplete

On 17.12.2011 23:38, Kay Drangmeister wrote:

...

Of course it is a space advantage not to repeat each tag existing in a regular column in the "tags" hstore again, but on the other side, stylesheets have to be always adapted if the osm2pgsql config file is changed. Plus they will no longer be portable across servers with different osm2pgsql configurations. Would it be possible to re-import the hstore?

There had been rumors that accessing hstore "columns" is slower than regular columns. Anyone done some tests regarding this? So performance-wise it might be better to adjust styles. It should be a simple search-replace. It could even be automated so it replaces hstore columns with native columns. Stephan

Peter Körner

1:23 p.m.

New subject: hstore incomplete

Am 17.12.2011 23:38, schrieb Kay Drangmeister:

...

In my history-schema I import all tags into a single hstore column. Afterwards views are created where those hstore-tags are mapped over to regular columns. This provides compatibility with hthe regular styles while still providing all information in one place and without the need to store data twice. Peter

Kai Krueger

3:42 p.m.

New subject: hstore incomplete

On 12/17/2011 03:38 PM, Kay Drangmeister wrote:

...

Hi all The parking styles partially fail with the newly imported database. Am 25.11.2011 09:56, schrieb Kai Krueger:

[...] progress on updating the software stack (The re-import will occur into a postgresql 9.1 database with the latest osm2pgsql) [...]

Maybe it is due to the latest osm2pgsql.

The import is intended to occur with the default set of db columns together with the hstore extensions.

The problem is that not all tags are present in the hstore any more, unlike in the old database. The tags available in other columns are left out in the hstore.

It looks like the following commit to osm2pgsql might have been the issue: r25689 | giggls | 2011-03-24 09:25:24 -0600 (Thu, 24 Mar 2011) | 9 lines Minor rework of hstore functionality: * Do not add a key to the hstore column if there is an exclusive column for this key by * Add an additional commandline switch to restore the original behaviour to add any key * remove the "z_order" special key from hstore in any case I did not use the additional commandline switch to move back to the original behaviour. Coming to think of it, I thought I saw a commit changing the behaviour back again, but I can't find it anymore.

...

Here is an example for the "amenity" tag missing: osm_mapnik=> select amenity,tags->'amenity' as "hstore-amenity" from planet_polygon where osm_id=32550266; amenity | hstore-amenity ---------+---------------- parking | (1 row) here is how it looked before the new import: osm_mapnik=> select amenity,tags->'amenity' as "hstore-amenity" from planet_polygon where osm_id=32550266; amenity | hstore-amenity ---------+---------------- parking | parking (1 row) (If you happen to be a subscriber of mapnik-de there has been the same issue with osm2pgsql, but it has been changed back to the old behaviour. See thread here: https://lists.openstreetmap.de/mailman/private/mapnik-de/2011-July/000067.h… login needed.)

No I am not a subscriber, but why aren't those archives public?!

...

OK, so the question is now what to do?

...

Would it be possible to re-import the hstore?

Does anyone know of an sql query that could be run to pull data from the normal columns and insert it into the hstore? I'd be somewhat reluctant to do a full re-import of the database. Kai

...

Kind regards, Kay

Julian Picht

6:28 p.m.

New subject: hstore incomplete

Hi all, this could be done using the hstore concat operator "||". See [1] section F.16.4. But I don't know if this is anywhat faster than a full reimport. Greetings, Julian Picht [1] http://www.postgresql.org/docs/9.1/static/hstore.html 2011/12/18 Kai Krueger <kakrueger(a)gmail.com>

...

On 12/17/2011 03:38 PM, Kay Drangmeister wrote:

Hi all The parking styles partially fail with the newly imported database. Am 25.11.2011 09:56, schrieb Kai Krueger:

[...] progress on updating the software stack (The re-import will occur into a postgresql 9.1 database with the latest osm2pgsql) [...]

Maybe it is due to the latest osm2pgsql.

The import is intended to occur with the default set of db columns together with the hstore extensions.

The problem is that not all tags are present in the hstore any more, unlike in the old database. The tags available in other columns are left out in the hstore.

https://lists.openstreetmap.de/mailman/private/mapnik-de/2011-July/000067.h…

No I am not a subscriber, but why aren't those archives public?!

OK, so the question is now what to do?

Would it be possible to re-import the hstore?

Does anyone know of an sql query that could be run to pull data from the normal columns and insert it into the hstore? I'd be somewhat reluctant to do a full re-import of the database. Kai

Kind regards, Kay

_______________________________________________ Maps-l mailing list Maps-l(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/maps-l

Kai Krueger

20 Dec 20 Dec

4:23 p.m.

New subject: hstore incomplete

On 12/18/2011 11:28 AM, Julian Picht wrote:

...

Hi all, this could be done using the hstore concat operator "||". See [1] section F.16.4. But I don't know if this is anywhat faster than a full reimport.

It would be hopefully less disruptive for other users than a full reimport though. I guess there would be three things that need to be checked. 1) What is the exact sql query to run to do this? 2) Can osm2pgsql in update mode be changed from -k (the current behaviour) to -j (the desired behaviour) without issues? 3) Does the update have to be stopped to prevent deadlocks or corruption while the hstore is modified? I would guess most other db's would use the osm2pgsql default.style in addition to hstore. So it would probably be safe to modify the style sheet to use the regular columns where available. Kai

...

Greetings, Julian Picht [1] http://www.postgresql.org/docs/9.1/static/hstore.html 2011/12/18 Kai Krueger <kakrueger(a)gmail.com <mailto:kakrueger@gmail.com>> On 12/17/2011 03:38 PM, Kay Drangmeister wrote:

Hi all The parking styles partially fail with the newly imported database. Am 25.11.2011 09:56, schrieb Kai Krueger:

[...] progress on updating the software stack (The re-import will occur into a postgresql 9.1 database with the latest osm2pgsql) [...]

Maybe it is due to the latest osm2pgsql.

The import is intended to occur with the default set of db columns together with the hstore extensions.

The problem is that not all tags are present in the hstore any more, unlike in the old database. The tags available in other columns are left out in the hstore.

https://lists.openstreetmap.de/mailman/private/mapnik-de/2011-July/000067.h…

No I am not a subscriber, but why aren't those archives public?!

OK, so the question is now what to do?

Would it be possible to re-import the hstore?

Does anyone know of an sql query that could be run to pull data from the normal columns and insert it into the hstore? I'd be somewhat reluctant to do a full re-import of the database. Kai

Kind regards, Kay

_______________________________________________ Maps-l mailing list Maps-l(a)lists.wikimedia.org <mailto:Maps-l@lists.wikimedia.org> https://lists.wikimedia.org/mailman/listinfo/maps-l _______________________________________________ Maps-l mailing list Maps-l(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/maps-l

4511

days inactive

4543

days old

maps-l@lists.wikimedia.org

Manage subscription

8 comments

5 participants

tags (0)

participants (5)

Julian Picht
Kai Krueger
Kay Drangmeister
Peter Körner
Stephan Knauss