|
Hi Alex_Z, 'fraid this is post is not an answer but a me-too. I am hoping you resolved your problem and can let me know how... I have got my system set up a bit simpler, with only the one database 'gis', used for rendering tiles. I set everything up mostly according to the instructions on switch2osm.org but it is proving a bit of a struggle, mostly because I only have a slow old machine available to use for the installation. Because of space and time constraints I did not use the full planet but used a map of GB downloaded from geofabrik. When rendered it I was able to use mobac to supply myself and some friends with some atlases for our mobile phones, so the main aim was achieved. After about 10 days I thought I had better set up a system to update the database so I followed the instructions in Minutely_Mapnik for a daily update. The resulting merged update file was 1G+ which seemed a bit large but I went ahead. Unfortunately the m/c hung that evening and I think it was a memory problem so I ran it again the next day without anything much else running and the osm2pgsql run completed in about 10 hours.New tiles were being rendered and everything seemed ok. So the day after that success I thought I would continue with the latest daily update (from geofabrik) but for no reason I can understand yet this won't enter the database, and I have the same errors you reported: %osm2pgsql -a -v -dgis -C1600 -e18 -oexpire-list -s --drop changes.osc.gz osm2pgsql SVN version 0.81.0 (64bit id space) Using projection SRS 900913 (Spherical Mercator) Setting up table: planet_osm_point NOTICE: table "planet_osm_point_tmp" does not exist, skipping Setting up table: planet_osm_line etc... ..until Allocating memory for sparse node cache Node-cache: cache=1600MB, maxblocks=0*204801, allocation method=8192 Mid: pgsql, scale=100 cache=1600 Setting up table: planet_osm_nodes PREPARE insert_node (int8, int4, int4, text[]) AS INSERT INTO planet_osm_nodes VALUES ($1,$2,$3,$4); PREPARE get_node (int8) AS SELECT lat,lon,tags FROM planet_osm_nodes WHERE id = $1 LIMIT 1; PREPARE delete_node (int8) AS DELETE FROM planet_osm_nodes WHERE id = $1; failed: ERROR: relation "planet_osm_nodes" does not exist LINE 1: ...rt_node (int8, int4, int4, text[]) AS INSERT INTO planet_osm... Error occurred, cleaning up I don't know how to query the database effectively from a command line to confirm if planet_osm_nodes is there or not, but I am right now running renderd, which is querying the db of course, and I am watching the logs in pg_activity, all seems well and tiles are appearing. Minutely Mapnik had another suggestion for updating the database using osmosis, so I tried that too. Perhaps a bit foolish of me 'cos I don't know what it is doing exactly. I got errors again :-( %osmosis --read-xml-change file="changes.osc.gz" --write-pgsql-change database=gis Apr 14, 2013 8:31:39 AM org.openstreetmap.osmosis.core.Osmosis run INFO: Osmosis Version 0.43-SNAPSHOT Apr 14, 2013 8:31:42 AM org.java.plugin.registry.xml.ManifestParser <init> INFO: got SAX parser factory - org.apache.xerces.jaxp.SAXParserFactoryImpl@681db8 ...more stuff until... Apr 14, 2013 8:19:43 AM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion SEVERE: Thread for task 1-read-xml-change failed org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT version FROM schema_info]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "schema_info" does not exist ...and so on... Anybody any ideas about what I have done wrong? I have a copy of the database from before the updates and I will probably go back to that and go through the update steps again to see if I can find where I went wrong. |
|
This is guessing a bit of what might have gone wrong, but one possible issue is your use of "--drop" osm2pgsql creates two sets of tables. 1) The rendering tables "planet_osm_point" "planet_osm_line" "planet_osm_roads" and "planet_osm_polygon" and 2) the "slim mode" tables "planet_osm_nodes" "planet_osm_ways" and "planet_osm_rels". For rendering, only the rendering tables are needed. These contain a lossy (not all tags and data are needed), transformed ( using postgis geometries rather than OSM nodes and ways) version of the OSM data optimised for rendering. In the slim mode tables on the other hand, the (more or less) original OSM data is stored. This is necessary during the initial import if you don't have enough memory to store everything in RAM and for updating the data with the diff files. It is however not necessary for rendering with renderd. The --drop option deletes the slim mode tables after the initial import. This is useful if you don't intend to run updates, as it saves time during the initial import and reduces the amount of disk space you need by about half. If you do want to run updates, you will need to re-import the entire data (UK) from scratch in slim mode and without the --drop option. After that you should be able to do diff imports as described above. But again make sure you don't have the --drop option specified. 1Gb+ for a single days update seems excessive (if that is the compressed size). You can get daily diffs covering the entire planet at http://planet.openstreetmap.org/replication/day/000/000/ and they are about 50 - 100Mb compressed. You can also get daily diff files only for GB at http://download.geofabrik.de/europe/great-britain-updates/000/000/ which are about 1 - 5Mb in size. With regard to osmosis, I don't think you can mix osm2pgsql and osmosis databases, as they use an entirely different data layout in the database. |
|
Thank you very much apmon , that was very clear and helpful, not least because you confirm what I was beginning to suspect about the --drop flag. I think there were 3 possible contributing causes for the problem I was having:
The results of these 3 things may have been cumulative but I suspect my inclusion of the --drop flag would have been enough. The rest of this email is just an extension on the above observation.apmon> osm2pgsql creates two sets of tables... I don't know anything about the structure of the database so this is interesting to find out. Thanks for the explanation. After I wrote to help.openstreetmap I continued trying to check out what I had done wrong. First I made a new directory-dump of my database. When I compared that dump with the original one it was much smaller in size and contained fewer files. So then I looked up the appropriate pgsql syntax to list the tables and confirmed (as per the error message) that my problem, planet_osm_nodes, among others was indeed missing. apmon> The --drop option deletes the slim mode tables after the initial import... osm2pgsql --help suggested that --drop deleted "temporary tables after import", so that misled me, though checking again I see there is a rider: "(no updates)"which was doubtless intended to warn against using --drop with updates. So that would completely explain my lost tables. I 'drop'ed the existing (broken) database and reloaded the good copy from the dump I made at the end of March. apmon>If you do want to run updates, you will need to re-import the entire data (UK) from scratch in slim mode ... In normal circumstances it would be a bit daft rebuild the db from a dump, wouldn't it? It took about as long to load it back in as it takes to download and instal an up-to-date version of the UK db. But it did take me back to where I was so I could try to check the wrong steps I had made. With the database back I used wget to get the next change file: %wget http://download.geofabrik.de/europe/great-britain-updates/000/000/026.osc.g (I used wget to get one file because I was not entirely sure if I had set up the osmosis replication command correctly). and used osm2pgsql to add that (Even before your reply I was suspicious about te --drop flag and did not include it): %osm2pgsql -a -dgis -v -e6-15 -oexpire-list26 -s 026.osc.gz All the tables were still there. So that was clearly a step in the right direction for me. So next I set the maxInterval option in configuration.txt to 86400 sec (1 day) and used osmosis to get the next change file in my sequence: osmosis --read-replication-interval workingDirectory=./ --simplify-change --write-xml-change change27.osc.gz and added it to the database. All ok. apmon>...make sure you don't have the --drop option specified. I was going to try adding a group of changes with the --drop flag included in the osm2pgsql line (just to see what would happen) but I think basically, and thanks to your reply, I can now say "QED" and not risk destroying the db again. apmon>1Gb+ for a single days update seems excessive (if that is the compressed size) Originally I had used osmosis to download and merge about 10 days worth of change files, which might explain why the change.osc.gz file was so large. I have deleted the file now so can't confirm the size again. Maybe I just read the output from ls -l wrong. I am quite capable of such a thing. I have experimented a bit more, and currently, with my setup I can only download one daily file at a time with the above osmosis command. If I put a period of two days (172800 secs) in the config.txt file (or a 0) and try to download and merge two (or more) daily files the command drops out with errors. The errors scrolled off the top of the screen and were not obvious so this is what leads me to think I had previously been using a corrupted change file. I don't think it is worth reporting this as a bug as the m/c I am using is only a 5 year old laptop installed with fedora18 and with <1G memory and a 1.6GHz Pentium III CPU. I expect it is complicit with whatever faults I might see. BTW. There is also a bit-count difference between the files returned by osmosis and those downloaded by wget: ls -l changes-28.osc.gz 028.osc.gz -rw-rw-r--. 1 paka paka 1505042 Mar 31 02:43 028.osc.gz -rw-rw-r--. 1 paka paka 1505053 Apr 15 14:03 changes-28.osc.gz If you unzip them and run od -x on them they don't look like the same file. I don't know what the significance of that is. Though I assume it is a result of the --simplify-change flag. apmon>With regard to osmosis, I don't think you can mix osm2pgsql and osmosis databases, as they use an entirely different data layout in the database. Thank you again. That is worth noting too. Looking back through the osmosis_detailed_usage in the wiki I realised that, as you say, it explains that I would need to do some more work to alter the configuration of the db to work with osmosis. As I don't exactly understand what it is that should be done - The wiki admits at one point that the term `schema' (the subject of the error when I ran the osmosis command) is context dependant - so I think I will leave well alone, (for now:-) and stick with using osm2pgsql for updating the db. |
Converted to question rather than just a 'me too' on someone else's post.