I have a client that I am developing a web-based business application for
using Perl / PHP, DHTML, and postgreSQL on a FreeBSD server with Apache...
The client realized (or hopes) that this application may become mission
critical (to his clients / end-users) and was asking about the possibility
of having a replicated database using postgreSQL in case the database server
takes a dive (paranoia). He is somewhat familiar with MS Access (I gathered
he was thinking of the MS Access replication / front-end / back-end /
synchronized database concept).
I have read through all 7 chapters of the PG documentation and didn't see
anything about replicating a postgreSQL database on a secondary database
server and having the primary server push all data manipulations on through
to the secondary server. The secondary server would be available (through
the connect script) if the primary server died.
Does anyone know of a reasonable means of synchronizing two (or more)
postgreSQL database servers in order to maintain two COMPLETE concurrent
copies of the same database ?
Since I can already see REFINT problems in maintaining the integrity of
serial values, trigger problems, and stored procedure problems (UDF's),
perhaps I should just suggest a robust database backup and restore scheme
with a standby postgreSQL server ready to accept a pgdumpall.
Although the documentation claims that CREATE DATABASE is not intended as a
"copy database" utility, maybe a CRON event (say every 4 hours) on the
secondary server to:
DROP database 'backupdatabase';
CREATE DATABASE 'backupdatabase' WITH TEMPLATE = 'livedatabase';
would do the trick....
I am completely open to suggestions here...
TIA.
GP