Importing large dataset with osm2psql? [closed]

Question

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?

Answer 1

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)

Answer 2

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.