Hi,
Our configuration is as follows:
1. A staging server, which receives new data and updates the DB
2. Two web servers that have copies of the DB (essentially read-only) and answer user queries (with load balancer)
Currently we use dump (to SQL file, i.e. pg_dump with no args) + file copy to replicate the DB daily between the staging and web servers, and then restore (via psql) the servers one at the time. In our application we expect that average daily change is only to 3% of the records. My question is what would be the best way to do this replication?
I read about
continuous archiving and PITR. My understanding however (e.g. from
this) is that I cannot do a base backup once and then e.g. apply WAL files on a daily basis, starting from yesterday's DB, but must instead redo the full base backup before starting recovery?
Then there are Slony-I, Buchardo, Mamoth Replicator from CMO, simple replication in Postgres 8.4 and other projects...
Suggestions?
Thanks,
-- Shaul