[postgis-users] synchronise /replicate postgis database

Stephen Woodbridge woodbri at swoodbridge.com
Wed Mar 12 10:22:13 PDT 2008

Intengu Technologies wrote:
> Hello list members, please assist this newbie how to implement the 
> scenario below.
> What I would want to do is to have a production and publication database 
> on a single physical machine also on this machine we will have our web 
> mapping server(do not have funds to buy an additional server).
> Will have read/write access on this database for  only two people.
> Will have read only access for our users (20).  They could use web or 
> desktop clients to access our data.
> Will also want to update the publication database with changes that have 
> been made on the production database at predetermined intervals.
> Any additional advice would be greatly appreciated.

You might want to do one database instance and use schemas to separate 

This has the nice advantage of being able to just do an
   UPDATE with a join to update existing records in PUBLICATION
   SELECT INTO with a join add new records from PRODUCTION to PUBLICATION.

Where the join is done between the two schemas. This is much easier than 
need to do a dump and restore between to databases. You can also write a 
stored procedure to do the update so it is mostly automated.

-Steve W

More information about the postgis-users mailing list