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

[Kai Krueger]

On 01/-10/-28163 12:59 PM, Jukka Rahkonen wrote:
> Frederik Ramm wrote:
[...]
>>> 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).
Yes, the indexing stage typically takes about half or more of the total
time. On a planet import I just did, it took 14 hours for writing of
data and another 41 hours for the indexing. I have not tried how long it
would take to restore a dump, but I suspect in that case not much less.
> 
> 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.

Can you describe a bit more of what your setup is, and where it fails?
Which version of Postgresql, PostGis and Osm2pgsql? 32bit or 64bit?
700Mb of ram really is very low, but I would like osm2pgsql to work with
little resources even if very slow.

> 
> 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.

Not sure how problematic those polygons are, but it doesn't seem to bad
to me. I have just tried a Finnish import and it only took 10 minutes
for the import. 5 minutes for writing of data and 5 minutes for the
indexing. OK, my laptop sounds like it is more powerful than your server
with 2.4GHz dual-core and 4 Gb ram, but osm2pgsql only used about 150Mb
of ram.

Non slim mode took overall less than 4 minutes, but that did take a
little under 2 Gb of Ram, so wouldn't be feasible on your server.

Kai

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