[postgis-users] PostGIS over distributed worksites

[Lee Hachadoorian]

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

-- 
Lee Hachadoorian
Assistant Professor of Instruction, Geography & Urban Studies
Assistant Director, Professional Science Master's in GIS
Temple University
http://geospatial.commons.gc.cuny.edu
http://freecity.commons.gc.cuny.edu