[postgis-users] Syncing Two Databases with a subset of data

[Kevin Neufeld]

Have you considered Slony?

http://slony.info/


If your 'main' database tables were data modeled using table inheritance 
and organized such that a child table is your data subset of interest, 
then you could simply replicate the child table to the 'region' database.

-- Kevin

Oliver Monson wrote:
> Hi All,
>
> I was hoping to get some advice on whats the best way to sync two 
> databases. I have a 'main' db and  an  'regional' db, and they have 
> two identical tables, one that holds data with multi-line geometries 
> and a table that holds data with point geometries. The 'region' db is 
> really just a subset of data from the 'main' db (the records with 
> geometries that fall within the "region"s predefined boundary).
> So for our system to properly sync, I see theres 3 things that must occur:
>
> 1) new records in 'region' need to be inserted into 'main'
> 2) new records in 'main' need to be inserted into 'region'
> 3) update existing records in 'region' that were modified in 'main'
>
>
> My current solution is to first update 'main' with any new records 
> created in 'region', and then delete every record in 'region', and 
> mass insert all the records back into it (again, only those records 
> with the proper geometry for the region).
> But I'm imagining this might become problematic and inefficient if 
> we're only updating 5,000 records and already have millions of records 
> total in 'main' and 'region'. Deleting and reinserting everything 
> would become really slow. So I thought there might be some other 
> method of just syncing modified/new data.
>
> Another idea was to have a "timestamp_modified" column in both tables, 
> allowing us to identify the last time a record was modified in 'main'. 
> If that timestamp differs in 'region', that record would be updated.
> But is that query to find differing timestamps just as slow as 
> deleting and reinserting everything, since it has to check all the 
> records in both instances?
>
> Anyway, any ideas would be helpful
> (maybe I'm missing some obvious syncing options in PostGres)
> Thanks,
>
> - Oliver
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>