Hi!
First of all: this is not a question, but a “report of my experience”…
Since I had many problem with very slow import (see here) and with disk space too (see here), I looked for a solution and I found it.
So, I’d like to share my solution with the community, hoping it helps someone else…
Caveat: this solution worked for me, since I just need an import of the OSM data once per year (so every year I destroy the previous database and import the data to a new one)! If you need regular updates, it will not works!
-
Preparing the hardware
Since the import and the querying of the data need quick access, I used an SSD instead of an HDD. 512GB is enough for whole Europe. 256GB can be enough, if you don’t need extra index. -
Preparing the database
To speed up the import, autovacuum should be turned off. In postgresql.conf change to:
autovacuum = off
- Start the import
I decided to use a separated node cache (created in the SSD, ~70GB für Europe), to disable the parallel indexing, to drop the slim mode tables after completing import and to use unlogged tables for storing data.
So my commands are:
sudo touch /var/lib/postgresql/nodes.cache ; sudo chmod a+w /var/lib/postgresql/nodes.cache
time sudo -u _renderd osm2pgsql -d gis --disable-parallel-indexing --drop --unlogged \
--flat-nodes /var/lib/postgresql/nodes.cache --create --slim -G --hstore --tag-transform-script \
~/src/osm-icao/osm-icao.lua -C 12000 --number-processes 12 -S ~/src/osm-icao/osm-icao.style \
~/data/europe-latest.osm.pbf
sudo rm /var/lib/postgresql/nodes.cache
/var/lib/postgresql is the mount point of my SSD.
Result:
Processing: Node(2875001k 930.1k/s) Way(348143k 7.94k/s) Relation(5895530 239.70/s) parse time: 71530s
As I said, this solution cannot be applyd be regular updates, since some data are missing:
gis=# \d+
Liste der Relationen
Schema | Name | Typ | Eigentümer | Größe | Beschreibung
--------+-------------------------------------+---------+------------+---------+--------------
public | external_data | Tabelle | _renderd | 16 kB |
public | geography_columns | Sicht | postgres | 0 bytes |
public | geometry_columns | Sicht | _renderd | 0 bytes |
public | planet_osm_line | Tabelle | _renderd | 35 GB |
public | planet_osm_nodes | Tabelle | _renderd | 0 bytes |
public | planet_osm_point | Tabelle | _renderd | 13 GB |
public | planet_osm_polygon | Tabelle | _renderd | 12 GB |
public | planet_osm_rels | Tabelle | _renderd | 2946 MB |
public | planet_osm_roads | Tabelle | _renderd | 5540 MB |
public | planet_osm_ways | Tabelle | _renderd | 80 GB |
public | raster_columns | Sicht | postgres | 0 bytes |
public | raster_overviews | Sicht | postgres | 0 bytes |
public | spatial_ref_sys | Tabelle | _renderd | 4616 kB |
(13 Zeilen)
Hope to help someone!
Luca