[postgis-users] PostGIS over distributed worksites

rox rox at tara-lu.com
Mon Mar 7 13:08:33 PST 2016

You set can a Master up anywhere that your remote desktops can reach it.
One other datum of possible value to you...  - barman can be used to 
backup/restore individual tables as well... (as I understand it). Using 
a full backup might simplify configuring a new install.  Using table 
restorations might make a automated pseudo replication possible [e.g. 
auto-update a fixed set of tables for each client when the master is 


On 3/7/2016 12:44 PM, Lee Hachadoorian wrote:
> At the moment it looks like our use case is data is updated from 
> canonical sources (gov't agencies), and users are just using for 
> analysis and visualization, not making changes to original data. So 
> read-only/one-way sync will work for us.
> Don't know about AWS. Data sets are single-digit GB. Mostly concerned 
> about pricing of updates, and not sure how to estimate costs.
> Rox, thanks for pointer to barman.
> Larry, will look into cascading replication. Interesting idea, might 
> be overkill for this project.
> Thanks,
> --Lee
> On 03/07/2016 01:59 AM, Larry Price https://industrialintellect.com 
> wrote:
>> Distributing periodic updates via dropbox or rsync is not a bad 
>> approach.
>> Postgres will do cascading replication
>> http://www.postgresql.org/docs/current/static/warm-standby.html#CASCADING-REPLICATION 
>> ( since 9.2 )
>> So that is one possible way to distribute that data.
>> Be aware that using replication to peoples local postgres databases
>> means that the local databases are read only.
>> If you start having datasets that are updated constantly, replication
>> will keep the read only copies up to date with less effort.
>> You will need to ensure that postgis and it's supporting libraries are
>> installed for the replicas.
>> On Sun, Mar 6, 2016 at 8:50 PM, rox <rox at tara-lu.com> wrote:
>>> I'm not sure what portions / parts of your db are shared or vary on 
>>> a per
>>> seat basis, and this isn't my normal area of mucking... but I'll 
>>> throw some
>>> ideas out and let other people improve them :)
>>> What if you set up a master database - perhaps AWS.  Set each 
>>> distributed
>>> seat to replicate down.  Updates are pushed to the master by 
>>> someone, and
>>> each distributed seat is auto-updated.
>>> What won't work with a pure replicated server instance.. is local 
>>> changes,
>>> if you have them.... and so again - not my area of playing, but what 
>>> about
>>> two local databases?  One local for changes, and a second that is pure
>>> replication.
>>> That complicates your connection configuration and potentially your 
>>> tool
>>> interactions....
>>> Otherwise, as I understand it, barman can create a backup of one 
>>> instance
>>> and restore the full working database to another site - so you could 
>>> create
>>> a shared barman replication service where individual seats could 
>>> recover a
>>> master DB when updates are required.
>>> just some thoughts to stir the discussion.
>>> Roxanne
>>> On 3/6/2016 7:21 PM, Lee Hachadoorian wrote:
>>>> I usually use PostGIS in a localhost or a LAN setting, where I or my
>>>> workgroup can all be connected to the database all the time.
>>>> I recently set up a QGIS project using SpatiaLite with Dropbox 
>>>> syncing,
>>>> because project participants were at multiple worksites. However,
>>>> rendering--particularly rendering of QueryLayers--is quite slow, 
>>>> and QGIS
>>>> often crashes. Testing shows that QGIS is much more responsive with 
>>>> PostGIS
>>>> as the back end, and although it's difficult to prove a negative, 
>>>> it seems
>>>> that QGIS crashes don't happen (or at least clearly happen less 
>>>> often). So I
>>>> would like to transition the project to PostGIS.
>>>> I am asking for your recommendations or experiences with setting 
>>>> this up
>>>> in a way that will be fairly easy to show users who are technically 
>>>> savvy
>>>> but new to database management. Some details:
>>>> 1. Small workgroup (5 or so).
>>>> 2. Spatial layers are big releases from city government (parcels, 
>>>> streets,
>>>> infrastructure) which are updated at most a couple of times a year, 
>>>> often
>>>> less frequently. Each spatial layer is updated on a different 
>>>> schedule by
>>>> different agencies.
>>>> 3. Data releases are in shapefile (blecch!).
>>>> 4. Users do meet in person with some regularity.
>>>> As a suggestion, I am considering just having everyone set up 
>>>> localhost
>>>> installs of PostGIS. When there is a new data release, one person 
>>>> could load
>>>> and clean the data, then dump the table, copy the dump to Dropbox, 
>>>> and other
>>>> users could load the new table in their localhost database.
>>>> Obviously this solution is not scalable--more users or more frequent
>>>> updates would make it quite tedious--but it may work for this use 
>>>> case. I am
>>>> open to criticisms and suggestions.
>>>> Thanks,
>>>> --Lee
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/postgis-users

More information about the postgis-users mailing list