<div dir="ltr">Hi Sven<div><br></div><div>Can you explain liitle bit more the setup of the servers you have in mind? </div><div>You seem to have a potent machine which has a PostGIS database and uses osm2pgsql to populate and update it periodically.<br>
</div><div>Then you want to attach a semi-potent server to that "master"?</div><div><br></div><div>Yours, Stefan</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2013/11/25 Sven Geggus <span dir="ltr"><<a href="mailto:lists@fuchsschwanzdomain.de" target="_blank">lists@fuchsschwanzdomain.de</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hello,<br>
<br>
on my day-job I recently had to solve the Problem of setting up a Postgis<br>
database contaning a full-planet extract using one of those cheap 180GB SSD<br>
on a semi-potent machine (only 8GB of RAM).<br>
<br>
I first tried to use osm2pgsql for this purpose which is almost impossible<br>
for a couple of reasons:<br>
<br>
* I would have needed more than 180GB of disk space (at least during import)<br>
* The import would take a _very_ long time (several days rather than hours)<br>
<br>
So I had to look for a backup strategy!<br>
<br>
I found one which proved to be that good, that I would like to discuss it<br>
here as an alternative way for rendering-database setup.<br>
<br>
The first thing I discovered is the fact, that we currently use roughly twice<br>
the disk-space for intermediate tables (or file in case of flatnode) than<br>
for the processed data itself.<br>
<br>
Here is how this looks like on <a href="http://tile.openstreetmap.de" target="_blank">tile.openstreetmap.de</a>:<br>
<br>
relation | total_size<br>
---------------------------+------------<br>
public.planet_osm_line | 61 GB<br>
public.planet_osm_polygon | 59 GB<br>
public.planet_osm_point | 10 GB<br>
public.planet_osm_roads | 8 GB<br>
<br>
public.planet_osm_ways | 174 GB<br>
public.planet_osm_rels | 4 GB<br>
<br>
+ flatnode.dat 20 GB<br>
<br>
processed data: 61GB+59GB+10GB+8GB=138GB<br>
intermediate data: 20GB+174GB+4GB=202GB<br>
<br>
The most annoying part is the 174GB planet_osm_ways table.<br>
<br>
So what I did to solve my problem was using pg_dump for the processed data<br>
tables only and setting up my target database using pg_restore.<br>
<br>
Advantages:<br>
* Very fast data import even on machines where import using osm2pgsql would<br>
be practically impossible<br>
* Decent size of database dump (32GB in case of a --hstore-match-only database<br>
which is about the same size as a planet dump)<br>
<br>
Disadvantages:<br>
* Currently no update strategy available<br>
* Will need a "master" osm2pgsql database<br>
* Will "inherit" table scheme from "master" database<br>
<br>
Conclusion:<br>
<br>
IMO the disadvantages can be resolved or at least mitigated in the following<br>
way:<br>
<br>
* we generate a downloadable dump of the database on<br>
<a href="http://tile.openstreetmap.de" target="_blank">tile.openstreetmap.de</a> (e.g. weekly)<br>
* osm2pgsql needs to be patched to output the changes to the processed data<br>
tables as SQL commands which can then be used to replicated the slave databases<br>
* We already use --hstore-match-only database format. So flexibility in<br>
table-layout is not that much of a concern as views to hstore column can be used<br>
for rendering instead of tables.<br>
<br>
I would like to hear your comments to this proposial.<br>
<br>
Regards<br>
<br>
Sven<br>
<span class="HOEnZb"><font color="#888888"><br>
--<br>
Threading is a performance hack.<br>
(The Art of Unix Programming by Eric S. Raymond)<br>
<br>
/me is giggls@ircnet, <a href="http://sven.gegg.us/" target="_blank">http://sven.gegg.us/</a> on the Web<br>
<br>
<br>
--<br>
"Thinking of using NT for your critical apps?<br>
Isn't there enough suffering in the world?"<br>
(Advertisement of Sun Microsystems in Wall Street Journal)<br>
/me is giggls@ircnet, <a href="http://sven.gegg.us/" target="_blank">http://sven.gegg.us/</a> on the Web<br>
<br>
_______________________________________________<br>
dev mailing list<br>
<a href="mailto:dev@openstreetmap.org">dev@openstreetmap.org</a><br>
<a href="https://lists.openstreetmap.org/listinfo/dev" target="_blank">https://lists.openstreetmap.org/listinfo/dev</a><br>
</font></span></blockquote></div><br></div>