[OSM-dev] speeding up loading an OSM dump into PostGIS?

[Frederik Ramm]

Hi,

On 11/30/2011 07:25 AM, Ákos Maróy wrote:
> What I've tried so far is importing the current planet-XXX.osm.bz2 file
> into PostGIS via osm2pgsl, which I have used with the --slim option, as
> without it the memory load exceeded the 16GB memory I had in my system
> significantly (it was using about 26GB when I shut the process down).

A planet import without --slim takes more than 48 GB of RAM.

> this way, it took about a week to import the current planet.osm file, on
> a Xeon 4 core system running 64 bit Linux.

Have you got the latest osm2pgsql checked out? Kai has made some 
improvements in the last few weeks (albeit someone else just now 
reported that this made things slower for them - my results were rather 
good with the new code).

If you do not need differential updates, newer versions of osm2pgsql 
also have a somewhat experimental --drop command line flag that avoids 
creating some indexes and cleans up temporary data after a --slim 
import, leaving you with about the same that you would have when you do 
an import without --slim.

> After this succeeded, I wanted to try to replicate this database, so I
> created a pg_dump using the -Fc switch

This is a bad idea because a significant amount of osm2pgsql import time 
is spent building indexes, and these are not dumped, so after restoring 
the data the same time is spent again. The only efficient way to copy an 
imported planet between two systems is to copy the raw postgresql data 
files directly, and this only works reliably if postgres, postgis and 
geos have identical versions on both systems (and of course both systems 
have the same architecture).

Bye
Frederik