Thread: 9.4+ partial log-shipping possible?
Hello, I have a question regarding log-shipping replication, but let me first explain what I currently do. Usually Openstreetmap Tile Servers are currently set up using a PostgreSQL/Postgis Database which stores the data need for rendering raster map-tiles. After the initial import of a complete OSM dataset it is possible to replicate changes to this database using cyclic calls to openstreetmaps own tool called osm2pgsql. Unfortunately there is one major drawback with this approach: We need to somehow save the state of the osm database to be able to apply future changes. This is currently done using a few tables in the target database. However these tables are not needed for map rendering and are consuming by far the most disk space (still somewhat expensive on SSD)! So here is my question: Would it be possible to have a setup, where one master data database will act in the above matter (still running osm2pgsl) but will also provide publicly availabe data for log-shipping standby servers? We would need to be able to explicitely specify the tables to be replicated or the other way round explicitely exclude a couple of them. If this helps it would be possible to separate them by tablespaces. Regards Sven -- # Turn on/off security. Off is currently the default (found in MongoDB default configfile) /me is giggls@ircnet, http://sven.gegg.us/ on the Web
Sven Geggus <lists@fuchsschwanzdomain.de> wrote: > So here is my question: > > Would it be possible to have a setup, where one master data database will > act in the above matter (still running osm2pgsl) but will also provide > publicly availabe data for log-shipping standby servers? > > We would need to be able to explicitely specify the tables to be replicated > or the other way round explicitely exclude a couple of them. If this helps > it would be possible to separate them by tablespaces. you can use logical replication slots, but afaik there are no ready-to-use solution available. Read more: http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-basics-logical-decoding/ i think, this (logical replication) are still under construction... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
No idea if this is an option for you or not, but if you make tables unlogged they won't generate WAL and therefore won't be replicated: http://rhaas.blogspot.ca/2010/05/global-temporary-and-unlogged-tables.html Of course unlogged tables have several drawbacks to carefully consider. On 03/26/2015 08:32 AM, Sven Geggus wrote: > Hello, > > I have a question regarding log-shipping replication, but let me first > explain what I currently do. > > Usually Openstreetmap Tile Servers are currently set up using a > PostgreSQL/Postgis Database which stores the data need for rendering raster > map-tiles. > > After the initial import of a complete OSM dataset it is possible to > replicate changes to this database using cyclic calls to openstreetmaps own > tool called osm2pgsql. > > Unfortunately there is one major drawback with this approach: > > We need to somehow save the state of the osm database to be able to apply > future changes. This is currently done using a few tables in the target > database. However these tables are not needed for map rendering and are > consuming by far the most disk space (still somewhat expensive on SSD)! > > So here is my question: > > Would it be possible to have a setup, where one master data database will > act in the above matter (still running osm2pgsl) but will also provide > publicly availabe data for log-shipping standby servers? > > We would need to be able to explicitely specify the tables to be replicated > or the other way round explicitely exclude a couple of them. If this helps > it would be possible to separate them by tablespaces. > > Regards > > Sven > -- Mike
On Thu, Mar 26, 2015 at 04:32:00PM +0100, Sven Geggus wrote: > > We need to somehow save the state of the osm database to be able to apply > future changes. This is currently done using a few tables in the target > database. However these tables are not needed for map rendering and are > consuming by far the most disk space (still somewhat expensive on SSD)! This doesn't actually solve your problem, but you could mitigate the cost by putting those tables on spinning-rust disks using tablespaces or symlinks or whatever. Best regards, A -- Andrew Sullivan ajs@crankycanuck.ca