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

[Jukka Rahkonen]

Frederik Ramm wrote:
> 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).

It can be slow but it is not always a bad idea. I have a very lean Linux
virtual server with about 700 MB of memory and it is very slow to import
even Finnish excerpt with osm2pgsql. In addition import tends to fail
totally about every second time. However, the virtual box has no troubles
if I run osm2pgsql at home, upload PG dump files into server and run
restore there. Even running restore from my home computer with pgAdmin III
through a 1 Mb line upwards is faster than making import with osm2pgsql on
the Linux box.  Ogr2ogr from the home PostGIS to remote PostGIS has also
worked reliably for me.

Finnish data may be heavy for osm2pgsql because there are rather many
polygons (407625 vs. 452944 linear features) to construct, including big
lake and land use relations. Perhaps native support for areas in OSM will
make is faster.

-Jukka Rahkonen-


> Bye
> Frederik
>
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/dev
>