[OSM-dev] Updated simplified osm2pgsql database dump available

[Paul Norman]

On 5/25/2016 2:32 PM, 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).

You'd still have the full storage footprint on one machine, it just 
helps with the requirements on your read-only replica slaves.

This can be done a few ways right now

- pglogical or trigger-based (e.g. slony) replication

Streaming replication doesn't work here since it replicates the entire 
cluster including slim tables

- PostgreSQL FDW magic

It should be fairly easily to post-import move the rendering tables to 
another cluster and use any replication method with that cluster.

- Allow connection information to be set per-table in osm2pgsql

This may be possible with the multi- backend. Adding it to all the 
backends shouldn't be too hard as osm2pgsql already uses independent 
connections for each table. The slim tables and rendering tables could 
then be directly placed on databases on different clusters

What is not possible is to centralize this. When doing updates what goes 
to the rendering tables depends on the osm2pgsql style, so your 
rendering table updates will not be the same as other people with a 
different style.