[OSM-dev] osm2pgsql commit point and index recovery

[Aiden Bell]

Thanks Paul,

I wonder if osm2pgsql would benefit from a stage based launch, ie:
]$ osm2pgsql ... --stages=import,index ... or something.

Not sure why I have had so many issues
with a planet import with 16GB RAM and PCIe SSDs in RAID0.

Are your indexing statements equivalent to the ones that osm2pgsql generates
during the index phase? Do you have vanilla index statements to hand? I'm
not using
hstore or any of the other options, just a basic import.

Thanks,
Aiden




On 24 March 2014 00:21, Paul Norman <penorman at mac.com> wrote:

> You should be fine. Once pending ways are completed, everything else is
> postgresql indexing and clustering. The following commands are what I run
> to manually cluster and index. Note that this uses a st_geohash clustering
> which is faster. I use psql commands run in parallel rather than do
> everything in one connection.
>
>
>
> It also does not create indexes for the slim tables, but does analyze them.
>
>
>
> psql -d gis -c 'CREATE INDEX ON planet_osm_roads (
> ST_GeoHash(ST_Transform(way,4326))) WITH (fillfactor=100);
>
> CLUSTER planet_osm_roads USING "planet_osm_roads_st_geohash_idx"; drop
> index planet_osm_roads_st_geohash_idx;' &
>
> psql -d gis -c 'CREATE INDEX ON planet_osm_point (
> ST_GeoHash(ST_Transform(way,4326))) WITH (fillfactor=100);
>
> CLUSTER planet_osm_point USING "planet_osm_point_st_geohash_idx"; drop
> index planet_osm_point_st_geohash_idx;' &
>
> psql -d gis -c 'CREATE INDEX ON planet_osm_line (
> ST_GeoHash(ST_Transform(way,4326))) WITH (fillfactor=100);
>
> CLUSTER planet_osm_line USING "planet_osm_line_st_geohash_idx"; drop index
> planet_osm_line_st_geohash_idx;' &
>
> psql -d gis -c 'CREATE INDEX ON planet_osm_polygon (
> ST_GeoHash(ST_Transform(way,4326))) WITH (fillfactor=100);
>
> CLUSTER planet_osm_polygon USING "planet_osm_polygon_st_geohash_idx"; drop
> index planet_osm_polygon_st_geohash_idx;' &
>
> wait
>
> psql -d gis -c 'CREATE INDEX ON planet_osm_roads USING gist (way);' &
>
> psql -d gis -c 'CREATE INDEX ON planet_osm_point USING gist (way);' &
>
> psql -d gis -c 'CREATE INDEX ON planet_osm_line USING gist (way);' &
>
> psql -d gis -c 'CREATE INDEX ON planet_osm_polygon USING gist (way);' &
>
> wait
>
> psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
> planet_osm_roads;' &
>
> psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
> planet_osm_point;' &
>
> psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
> planet_osm_line;' &
>
> psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
> planet_osm_polygon;' &
>
> psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
> planet_osm_ways;' &
>
> psql -d gis -c 'SET default_statistics_target=10000;ANALYZE
> planet_osm_rels;' &
>
> wait
>
> psql -d gis -c 'CREATE INDEX ON planet_osm_polygon USING gist (way) WHERE
> building IS NOT NULL;'
>
>
>
>
>
> *From:* Aiden Bell [mailto:aiden449 at gmail.com]
> *Sent:* Sunday, March 23, 2014 1:20 PM
> *To:* dev at openstreetmap.org
> *Subject:* [OSM-dev] osm2pgsql commit point and index recovery
>
>
>
> Hi all,
>
> I recently imported the osm planet file using osm2pgsql. The process
> crashed (hung my whole box) when creating the geometry indices and other
> indices on the tables. Most of the indexing completed, but the box hung on
> one of the last ones. Scouring through the source code, it looks fairly
> trivial to add the indices by hand, however I was wondering if anybody knew
> if the database is consistent at this point (ie, all row data comitted).
>
> Don't want to have to start over with the whole import really.
>
> Many thanks,
>
> Aiden
>



-- 
------------------------------------------------------------------
Never send sensitive or private information via email unless it is
encrypted. http://www.gnupg.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20140324/95e53e6d/attachment.html>