[OSM-dev] osm2pgsql - incomplete import (missing indexing), possible to add indexes manually?

[Nick Whitelegg]

Hi,


I've attempted to import all highways and selected POIs in Europe into an osm2pgsql database.


It has mostly worked (in the sense that the data I want is in the DB), but failed towards the end due to insufficient disc space during the process of indexing (see below):


The planet_osm_line and planet_osm_point have been populated successfully, however it appears that indexes were never added on planet_osm_line (they were added on planet_osm_point). Consequently line queries are slow and point queries faster.


Extract from my log:


WARNING:  there is no transaction in progress
Sorting data and creating indexes for planet_osm_point
Stopping table: planet_osm_nodes
Stopped table: planet_osm_nodes in 0s
Stopping table: planet_osm_ways
Building index on table: planet_osm_ways
Stopping table: planet_osm_rels
Building index on table: planet_osm_rels
Sorting data and creating indexes for planet_osm_line
Sorting data and creating indexes for planet_osm_polygon
Sorting data and creating indexes for planet_osm_roads
Stopped table: planet_osm_rels in 0s
node cache: stored: 263295984(54.84%), storage efficiency: 50.22% (dense blocks: 933, sparse nodes: 258322433), hit rate: 56.39%
Osm2pgsql failed due to ERROR: CREATE TABLE planet_osm_point_tmp  AS
  SELECT * FROM planet_osm_point
    WHERE ST_IsValid(way)
    ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10)
    COLLATE "C" failed: ERROR:  could not write block 10861 of temporary file: No space left on device

After this I deleted all the tables I don't need e.g. planet_osm_roads and planet_osm_ways, meaning I now have 52 GB free (out of 80).

I don't want to use a bigger server if I can possibly help it as I only need the disc space for the import process, not for its day-to-day running.


At this stage, I presume I can add an index manually, but what should I add the index on?

Do I need the temp tables that I deleted?


Thanks,

Nick


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20190319/5f5b364c/attachment.html>