Re: Dockerizált osm postgres DB telepítési jegyzet
Kovacs Szilveszter
hunsly at gmail.com
2018. Nov. 18., V, 01:32:54 UTC
Bár számomra nem kritikusan fontos, de osmose nem tudott csatlakozni rá.
Sokféle képen próbáltam de sehogyan sem ment. Elméletileg így működnie
kellene:
*osmosis --read-apidb host="172.17.0.2" database="postgres" user="postgres"
password="lQVdf4d3nj" --write-xml file="planet.osm"*
Nov 18, 2018 2:12:09 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.46
Nov 18, 2018 2:12:09 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Nov 18, 2018 2:12:09 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Nov 18, 2018 2:12:09 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
log4j:WARN No appenders could be found for logger
(org.springframework.jdbc.datasource.DataSourceTransactionManager).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for
more info.
Nov 18, 2018 2:12:09 AM
org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager
waitForCompletion
SEVERE: Thread for task 1-read-apidb failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to create
resultset.
at
org.openstreetmap.osmosis.apidb.common.DatabaseContext.executeQuery(DatabaseContext.java:431)
at
org.openstreetmap.osmosis.apidb.v0_6.impl.SchemaVersionValidator.validateDBVersion(SchemaVersionValidator.java:79)
at
org.openstreetmap.osmosis.apidb.v0_6.impl.SchemaVersionValidator.validateVersion(SchemaVersionValidator.java:53)
at
org.openstreetmap.osmosis.apidb.v0_6.ApidbReader.runImpl(ApidbReader.java:74)
at
org.openstreetmap.osmosis.apidb.v0_6.ApidbReader$1.doInTransactionWithoutResult(ApidbReader.java:102)
at
org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:34)
at
org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
at
org.openstreetmap.osmosis.apidb.common.DatabaseContext2.executeWithinTransaction(DatabaseContext2.java:94)
at
org.openstreetmap.osmosis.apidb.v0_6.ApidbReader.run(ApidbReader.java:99)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: relation
"schema_migrations" does not exist
Position: 21
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:303)
at
org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:289)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:266)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:233)
at
org.openstreetmap.osmosis.apidb.common.DatabaseContext.executeQuery(DatabaseContext.java:426)
... 9 more
Nov 18, 2018 2:12:09 AM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks
failed.
at
org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
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(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:330)
at
org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
at
org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
at
org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
at org.codehaus.classworlds.Launcher.main(Launcher.java:47)
2018. november 18., vasárnap 1:27:49 UTC+1 időpontban Kovacs Szilveszter a
következőt írta:
>
> Sziasztok!
>
> Egy rövid leírást csináltam egy Dockerizált osm postgres adatbázis
> klónozásról. Azaz csak a magyar területről. Részben magamnak is csináltam
> emlékeztetőnek.
>
> Ha tetszik, akkor később beemelhetem a wikibe is.
>
> Docker fájlok letöltése GIT-ről
> sly at sly: ~/Documents/HOME/mypgisdb/osm2pgsql$* git clone
> https://github.com/OsmHackTW/osm2pgsql-docker.git
> <https://github.com/OsmHackTW/osm2pgsql-docker.git>*
> Cloning into 'osm2pgsql-docker'...
> remote: Enumerating objects: 116, done.
> remote: Total 116 (delta 0), reused 0 (delta 0), pack-reused 116
> Receiving objects: 100% (116/116), 16.41 KiB | 190.00 KiB/s, done.
> Resolving deltas: 100% (51/51), done.
>
>
> sly at sly:~/Documents/HOME/mypgisdb/osm2pgsql$ *cd osm2pgsql-docker/*
>
> Futtathatóvá tétel
> sly at sly:~/Documents/HOME/mypgisdb/osm2pgsql/osm2pgsql-docker$ *chmod +x
> run.sh*
> sly at sly:~/Documents/HOME/mypgisdb/osm2pgsql/osm2pgsql-docker$ *chmod +x *
> *postgis.sh*
>
>
> Postgis DB letöltése és indítása
> sly at sly:~/Documents/HOME/mypgisdb/osm2pgsql/osm2pgsql-docker$ *sudo
> ./postgis.sh*
> [sudo] password for sly:
> Unable to find image 'osmtw/postgis:latest' locally
>
> latest: Pulling from osmtw/postgis
> f2aa67a397c4: Pull complete
> *[...]*
> d5d0857dfa92: Pull complete
> Digest:
> sha256:7b5096f988ca99e7ea904e968dacf48f0c4dd4c9f3cfe11f025d2ed6e2153d1e
> Status: Downloaded newer image for osmtw/postgis:latest
> d12745342012af7bb5f9ae643864d9821eb172e61afc8c59dc212440aa49d386
>
>
> Postgis DB frissítő letöltése és indítása
> sly at sly:~/Documents/HOME/mypgisdb/osm2pgsql/osm2pgsql-docker$ *sudo
> ./run.sh*
> Unable to find image 'osmtw/osm2pgsql:0.96.0' locally
> docker: Error response from daemon: manifest for osmtw/osm2pgsql:0.96.0
> not found.
> See 'docker run --help'.
>
>
>
> Valamiért nem működik a v96-al ezért átállítom a run.sh-ban verziót. Ha
> már erre járok az országot is:
> #!/bin/bash
> POSTGIS_INSTANCE=${1:-"osmdb"}
> REGION=${2:-"europe/hungary"}
> DATADIR=/osm
> LOOP=600
> VERSION=${3:-"0.90.1"}
>
> docker run -t -i --rm \
> --link ${POSTGIS_INSTANCE}:pg \
> -e REGION=$REGION \
> -e DATADIR=$DATADIR \
> -e LOOP=$LOOP \
> -v ${POSTGIS_INSTANCE}-volume:$DATADIR \
> --name osm2pgsql \
> osmtw/osm2pgsql:${VERSION}
>
>
> Ezekután újra próbálkozás
>
> sly at sly:~/Documents/HOME/mypgisdb/osm2pgsql/osm2pgsql-docker$ *sudo
> ./run.sh*
> Unable to find image 'osmtw/osm2pgsql:0.90.1' locally
> 0.90.1: Pulling from osmtw/osm2pgsql
> 297061f60c36: Already exists
> [...]
> eafdf1df5b82: Pull complete
> Digest:
> sha256:5b4a511fb29cf30b12275e9cf84b21e497211045d002aeb7c905fc14ef95daaa
> Status: Downloaded newer image for osmtw/osm2pgsql:0.90.1
> REGION=europe/hungary
> COUNTRY=hungary
> DATADIR=/osm
> PBF=/osm/hungary-latest.osm.pbf
> LOOP=600
> ?column?
> ----------
> 1
> (1 row)
>
> --2018-11-17 23:34:35--
> http://download.geofabrik.de/europe/hungary-latest.osm.pbf
> Resolving download.geofabrik.de (download.geofabrik.de)... 88.99.105.98,
> 88.99.24.113
> Connecting to download.geofabrik.de (download.geofabrik.de)|88.99.105.98|:80...
> connected.
> HTTP request sent, awaiting response... 200 OK
> Length: 152091283 (145M) [application/octet-stream]
> Saving to: '/osm/sy8.pbf'
>
> /osm/sy8.pbf
> 100%[=====================================================================================================================>]
> 145.04M 2.64MB/s in 64s
>
> 2018-11-17 23:35:39 (2.26 MB/s) - '/osm/sy8.pbf' saved
> [152091283/152091283]
>
> osm2pgsql SVN version 0.90.1-dev (64 bit id space)
>
> Using built-in tag processing pipeline
> Using projection SRS 900913 (Spherical Mercator)
> Setting up table: planet_osm_point
> Setting up table: planet_osm_line
> Setting up table: planet_osm_polygon
> Setting up table: planet_osm_roads
> Allocating memory for dense node cache
> Allocating dense node cache in one big chunk
> Allocating memory for sparse node cache
> Sharing dense sparse
> Node-cache: cache=2000MB, maxblocks=32000*65536, allocation method=11
> Mid: pgsql, scale=100 cache=2000
> Setting up table: planet_osm_nodes
> Setting up table: planet_osm_ways
> Setting up table: planet_osm_rels
>
> Reading in file: /osm/sy8.pbf
> Using PBF parser.
> Processing: Node(16565k 233.3k/s) Way(2264k 73.06k/s) Relation(81750
> 286.84/s) parse time: 387s
> Node stats: total(16565415), max(6067328962) in 71s
> Way stats: total(2264858), max(645435423) in 31s
> Relation stats: total(81751), max(8971464) in 285s
> Committing transaction for planet_osm_point
> [...]
> Setting up table: planet_osm_rels
> Using built-in tag processing pipeline
>
> Going over pending ways...
> 1326922 ways are pending
>
> Using 8 helper-processes
> Finished processing 1326922 ways in 58 s
>
> 1326922 Pending ways took 58s at a rate of 22877.97/s
> Committing transaction for planet_osm_point
> [...]
> Stopped table: planet_osm_rels in 1s
> node cache: stored: 16565415(100.00%), storage efficiency: 50.17% (dense
> blocks: 64, sparse nodes: 16247848), hit rate: 100.20%
>
> Osm2pgsql took 596s overall
> '/osm/sy8.pbf' -> '/osm/hungary-latest.osm.pbf'
>
>
> ?column?
> ----------
> 1
> (1 row)
>
> osmupdate Parameter: --base-url=
> download.geofabrik.de/europe/hungary-updates
> osmupdate Parameter: /osm/hungary-latest.osm.pbf
> osmupdate Parameter: /osm/kNe.pbf
> osmupdate: timestamp of /osm/hungary-latest.osm.pbf: 2018-11-16T21:14:03Z
> osmupdate: newest sporadic timestamp: 2018-11-16T21:14:03Z
> osmupdate: Found status information in base URL root.
> osmupdate: Ignoring subdirectories "minute", "hour", "day".
> osmupdate: Creating output file.
> osmupdate: Your OSM file is already up-to-date.
>
>
> Ekkor lefutott az import is.
>
> *Elérhetőség ellenőrzése.*
>
> Információk lekérése:
> *sudo docker exec osmdb env*
>
> PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/postgresql/10/bin
> HOSTNAME=d12745342012
> *POSTGRES_PASSWORD=lQVddc4d3nj*
> *POSTGRES_USER=postgres*
> *POSTGRES_DB=postgres*
> GOSU_VERSION=1.10
> LANG=en_US.utf8
> PG_MAJOR=10
> PG_VERSION=10.4-2.pgdg90+1
> PGDATA=/var/lib/postgresql/data
> POSTGIS_MAJOR=2.4
> POSTGIS_VERSION=2.4.4+dfsg-1.pgdg90+1
> HOME=/root
>
> IP:
> *sudo docker exec osmdb bash -c 'hostname -I'*
> 172.17.0.2
>
> Kapcsolódás az adatbázishoz:
> *psql -h 172.17.0.2 -U postgres -p 5432 -l*
> Password for user postgres:
> List of databases
> Name | Owner | Encoding | Collate | Ctype |
> Access privileges
>
> ------------------+----------+----------+------------+------------+-----------------------
> postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
> template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
> =c/postgres +
> | | | | |
> postgres=CTc/postgres
> template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
> =c/postgres +
> | | | | |
> postgres=CTc/postgres
> template_postgis | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
> (4 rows)
>
> * sudo docker ps*
> [sudo] password for sly:
> CONTAINER ID IMAGE COMMAND
> CREATED STATUS PORTS NAMES
> 847f6527107c osmtw/osm2pgsql:0.90.1 "/bin/bash -i /usr/l…" 8
> minutes ago Up 7 minutes osm2pgsql
> d12745342012 osmtw/postgis "docker-entrypoint.s…" 22
> minutes ago Up 21 minutes 5432/tcp osmdb
>
>
>
> *Megjegyzés:*
> Ha jól sejtem 600 másodpercenként frissül az adatbázis, amit az osm2pgsql
> docker csinál.
> osmdb maga az adatbázis.
>
--------- következő rész ---------
Egy csatolt HTML állomány át lett konvertálva...
URL: <http://lists.openstreetmap.org/pipermail/talk-hu/attachments/20181117/fced6ad3/attachment.htm>
További információk a(z) Talk-hu levelezőlistáról