[postgis-users] synchronise /replicate postgis database

[Stephen Woodbridge]

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).
> 
> PRODUCTION DATABASE:
> Will have read/write access on this database for  only two people.
> 
> PUBLICATION DATABASE:
> 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 
your PRODUCTION and PUBLICATION data.

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
  http://imaptools.com/