[OSM-dev] Updated simplified osm2pgsql database dump available

[Rory McCann]

On 25/05/16 23:32, Sven Geggus wrote:
> I have always been wondering if it would be possible to get rid of the
> special tables used for the sole purpose of keeping the rendering database
> up-to-date by using a scheme like this:
> 
> osm2pgsql database with special tables  -> some psql replication scheme
> 
> --> many slave databases without special tables kept up-to date using psql
>     some replication scheme
> 
> This would have the advantage of a lower storage foot-print (only 4
> rendering tables left).

I've set up a few machines with this sort of set up. One master machine
has all the tables and has osm2pgsql updating the data, and then slony
is used to replicate some tables to a slave database, which is used for
rendering tiles, as a WMS, or as a backup. This way you can have a
read-only backup, or rendering server, which doesn't have to be as beefy
(and expensive) as your production machine.

Slony is useful because you can tell it to only replicate some tables,
rather than needing to replicate an entire database. It's a little
tricky to set up at the start, and there isn't enough documentation. It
requires a PRIMARY KEY column for each table, which osm2pgsql data
doesn't have, so you need to do "ALTER TABLE planet_osm_X add column gid
serial PRIMARY KEY;" *before* doing any replication.

I've done it with nominatim as well.

Rory