[postgis-users] options to scaleout POSTGIS DATABASE

[Greg Williamson]

Pierluigi -- 


This is perhaps better asked on the postgres admin mailing list, but for now:

How much lag is acceptable between the servers ?

How much data needs to get moved ?

Do all the servers need to be able to write to the database, or can they be used in a read-only mode ?

---

Postgres 9.1 has a streaming replication that is respectably fast but the client database can't be written to at all, not even temp tables. On the plus side, DDL changes get propagated to the clients transparently. Tools like repmgr make managing them easier. The whole database gets replicated.


Londiste (and as far as I know Slony and Bucardo) are trigger based and make only data changes; DDL changes (new columns, etc.) need to be handled carefully. The client database can be written to (this includes the ability to shoot replication in the foot as a side effect), and you ca pick and choose which table to replicate. Different sets (at least in Londiste) can be given priority over others.

Long transactions on the primary server tend to do wicked things to replication, but how tolerant they are and exactly what fails, varies.

If you can provide some more information about your requirements that might suggest a cleared focus on one tool or another.

HTH,

Greg Williamson

>________________________________
> From: Pierluigi Santin <santinpierluigi at hotmail.com>
>To: postgis-users at postgis.refractions.net 
>Sent: Saturday, April 28, 2012 12:02 AM
>Subject: [postgis-users] options to scaleout POSTGIS DATABASE
> 
>
> 
>
>Hi all
>Which are the best options to replicate a POSTGIS db across several machines, to scale out the DB layer of a BIG geospatial application?
>
>I read about several options such as postgresql streaming replication, pgpoolII, slony-I, but i miss the big picture or the advice from someone that experienced the problem.
>The postgis db will be use by a series of client apps (gvsig, arcgis, qgis,udig ecc) but also will be queries by a stack of replicated Geoservers to serve WFS-WMS to the internet.
>
>Thanks for any advice
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>