Martijn van Oosterhout <kleptog <at> gmail.com> writes: > > On 8 June 2011 17:11, Martijn van Oosterhout <kleptog <at> gmail.com> wrote: > > Actually, it looks like it's breaking on the pending_ways query. It's > > possible there's a lot of those, perhaps it's just running out of > > memory there. The code was designed that pending would only be for > > changes, and I wouldn't have expected that code to be used for an > > initial import. but I haven't look at the code recently so maybe that > > changed? > > > > If it is the intention that this code is used for initial imports it > > really needs to be changed to use cursors. > > Actually, it occurred to me that the system is optimised to the case > where the file contains nodes, then ways, then relations. What you're > seeing could be caused by the file having the ways first, then the > nodes. Can you examine the file to see how it looks? > > Have a nice day,
Hi, We're having a problem importing planet file into our PostGIS database. The import ran for a few days and then terminated with an "out of memory" error (full details below). After much googling and trying to fix things ourselves, we've finally admitted defeat and need to ask for some help! Are we trying to do the impossible by importing the whole planet with just 12Gb of RAM? Is there some trickery we can use to do the import on this machine, or maybe another method we can use for the import? Thanks in advance for any advice, Andrei & Doug MARSS Full details of our setup below: OpenStreetMap importing planet issue task: Importing planet.osm.bz2 file into a PostgreSQL 8.4 + PostGis database. hardware used: Intel Xeon CPU I7(2.67Ghz), with 12GB of RAM process used (from weait.com blog - "build your own OSM server" (http://weait.com/content/build-your-own-openstreetmap-server)): 1. downloaded planet file (17GB) 2. prepared the postGIS database - installed postGIS and postGIS extensions for PostreSQL sudo apt-get install postgresql-8.4-postgis postgresql-contrib-8.4 sudo apt-get install postgresql-server-dev-8.4 sudo apt-get install build-essential libxml2-dev libtool sudo apt-get install libgeos-dev libpq-dev libbz2-dev proj 3. installed osm2pgsql (latest version (0.70.5)) from the SVN repository cd ~/bin svn co http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/ cd osm2pgsql ./autogen.sh ./configure make 4. configured the PostGIS database shared_buffers = 128MB # 16384 for 8.1 and earlier checkpoint_segments = 20 maintenance_work_mem = 256MB # 256000 for 8.1 and earlier autovacuum = off 5. edited kesrnel parameter shmmax to increase maximum size of shared memory. sudo sysctl -w kernel.shmmax=268435456 sudo sysctl -p /etc/sysctl.conf 6. restarted postgres to enable the changes sudo /etc/init.d/postgresql-8.4 restart 7. created a database sudo -u postgres -i createuser username # answer yes for superuser createdb -E UTF8 -O username gis_planet createlang plpgsql gis exit 8. have set up PostGIS on the postresql database. psql -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql -d gis 9. have set the tables owners to the same user-name as mapnik will use to generate tiles echo "ALTER TABLE geometry_columns OWNER TO username; ALTER TABLE spatial_ref_sys OWNER TO username;" | psql -d gis 10. have set the Spatial Reference Identifier (SRID) on the new database. psql -f ~/bin/osm2pgsql/900913.sql -d gis 11. loaded planet into the database with osm2pgsql ./osm2pgsql -S default.style --slim -d gis -C 2048 ~/planet/planet.osm.bz2 error encountered: Node stats: total(1137985868), max(1350873143) Way stats: total(100214404), max(120480121) Relation stats: total(1037036), max(1652480) Going over pending ways OpenStreetMap importing planet issue task: Importing planet.osm.bz2 file into a PostgreSQL 8.4 + PostGis database. hardware used: Intel Xeon CPU I7(2.67Ghz), with 12GB of RAM process used (from weait.com blog - "build your own OSM server" (http://weait.com/content/build-your-own-openstreetmap-server)): 1. downloaded planet file (17GB) 2. prepared the postGIS database - installed postGIS and postGIS extensions for PostreSQL sudo apt-get install postgresql-8.4-postgis postgresql-contrib-8.4 sudo apt-get install postgresql-server-dev-8.4 sudo apt-get install build-essential libxml2-dev libtool sudo apt-get install libgeos-dev libpq-dev libbz2-dev proj 3. installed osm2pgsql (latest version (0.70.5)) from the SVN repository cd ~/bin svn co http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/ cd osm2pgsql ./autogen.sh ./configure make 4. configured the PostGIS database shared_buffers = 128MB # 16384 for 8.1 and earlier checkpoint_segments = 20 maintenance_work_mem = 256MB # 256000 for 8.1 and earlier autovacuum = off 5. edited kesrnel parameter shmmax to increase maximum size of shared memory. sudo sysctl -w kernel.shmmax=268435456 sudo sysctl -p /etc/sysctl.conf 6. restarted postgres to enable the changes sudo /etc/init.d/postgresql-8.4 restart 7. created a database sudo -u postgres -i createuser username # answer yes for superuser createdb -E UTF8 -O username gis_planet createlang plpgsql gis exit 8. have set up PostGIS on the postresql database. psql -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql -d gis 9. have set the tables owners to the same user-name as mapnik will use to generate tiles echo "ALTER TABLE geometry_columns OWNER TO username; ALTER TABLE spatial_ref_sys OWNER TO username;" | psql -d gis 10. have set the Spatial Reference Identifier (SRID) on the new database. psql -f ~/bin/osm2pgsql/900913.sql -d gis 11. loaded planet into the database with osm2pgsql ./osm2pgsql -S default.style --slim -d gis -C 2048 ~/planet/planet.osm.bz2 error encountered: Node stats: total(1137985868), max(1350873143) Way stats: total(100214404), max(120480121) Relation stats: total(1037036), max(1652480) Going over pending ways pending_ways failed: out of memory for query result (7) Error occurred, cleaning up osm2pgsql SVN version 0.70.5 Error occurred, cleaning up osm2pgsql SVN version 0.70.5 _______________________________________________ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
