Hallo!
Der Import von kleineren Gebieten (Bundesland) hat geklappt. Für ganz Deutschland bekomme ich immer einen Out-Of-Memory-Fehler.
Es gibt ja neben --write-pgsql auch noch --write-pgsql-dump, welches die Daten zunächst in Text-Datein schreibt, die dann mittels SQL-Befehlen die Datenbank kopiert werden. Für ein einzelnes Bundesland klappt es, genau wie das anschliessende Kopieren in die Datenbank. Probiere ich das Ganze mit einem Deutschland-Extrakt, gibts aber auch damit Probleme
Das Ganze habe ich unter Windows 7 Prof. 64 Bit gemacht.
Der erste Versuch mit direktem Schreiben in die Datenbank:
c:\OSM\osmosis>call osmosis --read-xml file=“c:\osm\planet\germany.osm.bz2” --b bufferCapacity=50000 --lp --write-pgsql user=“xxx” database=“osm_ger”
[...]
INFO: Processing Way 97719199, 4366.014376996805 objects/second.
07.02.2011 19:22:17 org.openstreetmap.osmosis.core.progress.v0_6.EntityProgressLogger process
INFO: Processing Way 97878026, 4303.774715398442 objects/second.
07.02.2011 19:22:22 org.openstreetmap.osmosis.core.progress.v0_6.EntityProgressLogger process
INFO: Processing Relation 363743, 13120.621019108281 objects/second.
07.02.2011 19:22:29 org.openstreetmap.osmosis.core.progress.v0_6.EntityProgressLogger process
INFO: Processing Relation 1178704, 8805.22674865488 objects/second.
07.02.2011 19:22:31 org.openstreetmap.osmosis.core.progress.v0_6.EntityProgressLogger complete
INFO: Processing completion steps.
07.02.2011 19:43:19 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinition
s
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-er
ror-codes.xml]
07.02.2011 19:43:19 org.springframework.jdbc.support.SQLErrorCodesFactory <init>
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Syba
se]
07.02.2011 19:43:20 org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompleti
on
SCHWERWIEGEND: Thread for task 2-b failed
org.springframework.dao.DataAccessResourceFailureException: StatementCallback; SQL [ALTER TABLE ONLY
nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id)]; ERROR: out of memory
Detail: Failed on request of size 536870912.; nested exception is org.postgresql.util.PSQLExceptio
n: ERROR: out of memory
Detail: Failed on request of size 536870912.
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorC
odeSQLExceptionTranslator.java:253)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(Abstrac
tFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:518)
at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:24
8)
at org.openstreetmap.osmosis.pgsnapshot.v0_6.impl.IndexManager.completeAfterLoad(IndexManage
r.java:130)
at org.openstreetmap.osmosis.pgsnapshot.v0_6.impl.CopyFilesetLoader.run(CopyFilesetLoader.ja
va:111)
at org.openstreetmap.osmosis.pgsnapshot.v0_6.PostgreSqlCopyWriter.complete(PostgreSqlCopyWri
ter.java:108)
at org.openstreetmap.osmosis.core.progress.v0_6.EntityProgressLogger.complete(EntityProgress
Logger.java:61)
at org.openstreetmap.osmosis.core.buffer.v0_6.EntityBuffer.run(EntityBuffer.java:77)
at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: out of memory
Detail: Failed on request of size 536870912.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062
)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:
353)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:299
)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTem
plate.java:508)
at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTem
plate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
... 8 more
07.02.2011 19:43:20 org.openstreetmap.osmosis.core.Osmosis main
SCHWERWIEGEND: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:1
46)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:329)
at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:239)
at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:409)
at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:352)
at org.codehaus.classworlds.Launcher.main(Launcher.java:31)
c:\OSM\Osmosis>
Als zweiten Versuch habe nun zunächst mit --write-pgsql-dump die Daten in Textdateien geschrieben, das ging auch soweit durch. Dann scheiterte aber das Kopieren in die Datenbank:
c:\OSM\Data>psql -d osm_ger -f C:\OSM\Osmosis\osmosis-SNAPSHOT-r25162\script\pgsnapshot_load_0.6.sql
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
DROP INDEX
DROP INDEX
DROP INDEX
DROP INDEX
DROP INDEX
dropgeometrycolumn
---------------------------------------
public.ways.bbox effectively removed.
(1 row)
dropgeometrycolumn
---------------------------------------------
public.ways.linestring effectively removed.
(1 row)
psql:C:/OSM/Osmosis/osmosis-SNAPSHOT-r25162/script/pgsnapshot_load_0.6.sql:146688457: NOTICE: ALTER
TABLE / ADD PRIMARY KEY will create implicit index "pk_nodes" for table "nodes"
psql:C:/OSM/Osmosis/osmosis-SNAPSHOT-r25162/script/pgsnapshot_load_0.6.sql:146688457: ERROR: out of
memory
DETAIL: Failed on request of size 536870912.
psql:C:/OSM/Osmosis/osmosis-SNAPSHOT-r25162/script/pgsnapshot_load_0.6.sql:146688458: NOTICE: ALTER
TABLE / ADD PRIMARY KEY will create implicit index "pk_ways" for table "ways"
ALTER TABLE
psql:C:/OSM/Osmosis/osmosis-SNAPSHOT-r25162/script/pgsnapshot_load_0.6.sql:146688459: NOTICE: ALTER
TABLE / ADD PRIMARY KEY will create implicit index "pk_way_nodes" for table "way_nodes"
psql:C:/OSM/Osmosis/osmosis-SNAPSHOT-r25162/script/pgsnapshot_load_0.6.sql:146688459: ERROR: out of
memory
DETAIL: Failed on request of size 536870912.
psql:C:/OSM/Osmosis/osmosis-SNAPSHOT-r25162/script/pgsnapshot_load_0.6.sql:146688460: NOTICE: ALTER
TABLE / ADD PRIMARY KEY will create implicit index "pk_relations" for table "relations"
ALTER TABLE
psql:C:/OSM/Osmosis/osmosis-SNAPSHOT-r25162/script/pgsnapshot_load_0.6.sql:146688461: NOTICE: ALTER
TABLE / ADD PRIMARY KEY will create implicit index "pk_relation_members" for table "relation_member
s"
ALTER TABLE
CREATE INDEX
psql:C:/OSM/Osmosis/osmosis-SNAPSHOT-r25162/script/pgsnapshot_load_0.6.sql:146688463: ERROR: out of
memory
DETAIL: Failed on request of size 536870912.
CREATE INDEX
addgeometrycolumn
--------------------------------------------------
public.ways.bbox SRID:4326 TYPE:GEOMETRY DIMS:2
(1 row)
addgeometrycolumn
--------------------------------------------------------
public.ways.linestring SRID:4326 TYPE:GEOMETRY DIMS:2
(1 row)
psql:C:/OSM/Osmosis/osmosis-SNAPSHOT-r25162/script/pgsnapshot_load_0.6.sql:146688476: ERROR: out of
memory
DETAIL: Failed on request of size 268435456.
psql:C:/OSM/Osmosis/osmosis-SNAPSHOT-r25162/script/pgsnapshot_load_0.6.sql:146688484: ERROR: out of
memory
DETAIL: Failed on request of size 268435456.
CREATE INDEX
CREATE INDEX
Physiches RAM Sollte eigentlich genug vorhanden sein (12 GB). Wahrscheinlich hat PostgreSQL (Version 8.3.12, 32 Bit) aber ein Problem sich genug Speicher zu holen.
Soweit ich weiß gibt es aber für Windows nur PostgreSQL 9.0in der 64-Bit Version vorliegt, für welches es aber kein PostGIS gibt.
Gibt es sonst einen Trick?
Könnte man sonst die DB unter Linux erstellen und dann ins Windows-PostgreSQL kopieren? Käme die Windows-Version dann überhaupt mit der Datenbank klar, wenn man z.B. Diffs einspielt, oder ist das Kritische nur der Erstimport?
Christian