Hi guys,

Id' like to import the France extract in my postgresql database with osm2pgsql. With the actual settings, it's really slow... but I saw that it's possible to tune Postgresql in order to speed up the process. But, I know almost nothing about hardware...

How would you tune my postgresql depending on my hard configuration (shared_buffers, work_mem, etc) ?. I'm using Ubuntu 12.04 server on a virtual machine, 4G of RAM, 100 GB HD and 4 cores.

Thanks! Lucas

asked 18 Jul '13, 11:42

Kalu06's gravatar image

accept rate: 0%

edited 18 Jul '13, 12:49

You have tagged this with nominatim, can we assume that your import uses osm2pgsql?

(18 Jul '13, 12:19) SK53 ♦

Yes. Sorry, I forgot to mention it.

(18 Jul '13, 12:39) Kalu06

As a first attempt to tune Postgres there's also a tool called pgtune: https://github.com/gregs1104/pgtune

permanent link

answered 20 Jul '13, 01:19

Geonick's gravatar image

accept rate: 0%

Tuning postgres is an art. For starters see the osm wiki, the postgres wiki, or the postgres doc. Some of the most important things you should try :

  • raise shared_buffers to 30-40% of available ram
  • raise work_mem until you stop seeing log_temp_file warnings in the logs, unless it gets so high that you'd run out of memory
  • raise maintenance_work_mem to twice that
  • raise checkpoint_segments a bit and checkpoint_completion_target to about 0.95
  • make sure you have the latest version of postgres (9.2 at the time of writing) and linux kernel
  • run postgres on the host instead of a virtual machine
  • give postgres an SSD drive and as much memory as you can afford (ideally enough to store the whole db, plus a bit to spare)
  • try formating the partition as XFS instead of EXT4; avoid btrfs for postres for now

You'll need to experiment a bit. Don't change all the settings at once, just change a few and measure the effect. Use a small (but not trivially smal) data extract (for example Auvergne instead of the whole of France) to iterate faster.

permanent link

answered 18 Jul '13, 12:52

Vincent%20de%20Phily's gravatar image

Vincent de P... ♦
accept rate: 19%


Thank you very much for this comprehensive answer. I'm sure it wil be very helpful. I'm going to try that right away.

(18 Jul '13, 12:56) Kalu06

With only 4GB of ram and a single HDD, importing France will likely always be slow. In case this is an option, adding an SSD to put your database on, or increasing your ram to at least 8Gb or better 16Gb (and setting the cache parameter in osm2pgsql correctly) is probably the only way to really speed things up substantially.

(18 Jul '13, 21:39) apmon
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 18 Jul '13, 11:42

question was seen: 13,023 times

last updated: 20 Jul '13, 01:19

powered by OSQA