8

My question is similar to slow import via osm2pgsql to postgresql database and Optimizing osm2pgsql imports for OSM data but as we currently have quite some problems importing a large set of OSM data I open a new one.

What is the best way to import a large dataset (OSM Export of Europe) into a postgres DB?

Our computer has 32 GB of RAM... so it could use all of that.

We tried a couple of params, but had no success... last try we used the

osm2pgsql -c -S /usr/share/osm2pgsql/default.style --slim -d osm-europe -U postgres -C 25000 europe-latest.osm.pbf

But we ran out of memory even though our sever has 32 GB of RAM available.

pending_ways failed: out of memory for query result
(7)
Error occurred, cleaning up

How do we improve our import command?

Even if it takes longer... But we need to have the data imported into our postgres DB.

Would you recommend using an EC2 for the task or should our setup work with different parameters?

closed as too broad by PolyGeo Apr 16 at 12:29

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

7

Your computer should be fine for importing Europe.

Given your dataset size and computer, I'd recommend something like this


I'm assuming that you have an 8 thread CPU, if not, adjust --number-processes.

You don't need 25GB of ram for cache with just Europe.

For Europe, flat nodes should be smaller and faster than in-DB storage of node positions.

If there are still problems, check that you have a version of osm2pgsql using the 64-bit ID space and if so, check your PostgreSQL settings. You could be filling up your disk. Try tuning your settings in postgresql.conf.

  • thanks a lot... during the weekend my import succeeded with my command and with no other process running on our machine... but thank you very much! – Georg Jul 6 '14 at 16:48
  • --flat nodes might not work with the windows binary according to wiki.openstreetmap.org/wiki/Osm2pgsql#Binary – AndreJ Jul 10 '14 at 12:38
7

I imported a Planet File on a 24Gb Machine (Ubuntu Trusty) with the following ..

bzcat planet-latest.osm.bz2 | 
osm2pgsql --verbose -U YourUser --flat-nodes flat-nodes --keep-coastlines --cache 24000 --hstore --hstore-add-index --tablespace-index pg_default --exclude-invalid-polygon --number-processes 6 --unlogged --cache-strategy dense --extra-attributes --slim -H localhost -d planetosm --style ../my.style planet-latest.osm.bz2

It took approx 5 days, the last half on the database side .. rather than actual import

I tuned Postgres with the following for the import

autovacuum = off (default #autovacuum = on)
checkpoint_segments = 60 (default #checkpoint_segments = 3 # in logfile      
segments, min 1, 16MB each)
maintenance_work_mem = 256MB ( default #maintenance_work_mem = 16MB # min 1MB)
work_mem = 256MB (default #work_mem = 1MB  # min 64kB)
  • thanks a lot... during the weekend my import succeeded with my command and with no other process running on our machine... but thank you very much! – Georg Jul 6 '14 at 16:47
  • You are welcome. Took me a month to get it right .. Cheers – Mark Cupitt Jul 7 '14 at 0:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.