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

[Oliver Monson]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080503/456a2c2b/attachment.html>