Re: syncronizing databases

On Thursday 11 Jul 2002 5:09 pm, Linn Kubler wrote:
> If this is the wrong list to post this question to forgive me, correct
> me and I'll be out of your hair.

Right place afaict

> One of our servers is in production, serving
> our website, the other is a backup server. I'd like to have the users
> only need to enter data once, preferably on the backup server and then
> either periodically or on demand have the two servers sync. Make
> sense?

I'm guessing that just pg_dump'ing the whole thing involves too much
data/time.

There are some replication tools around for PostgreSQL but none of them are
particularly plug and play (see the contrib directory in the source package
for one).

If you can meet three conditions it's not too tricky to build your own.
1. All changes are made on one server and batched over to the other.
2. Changes are relatively infrequent
3. Deletes are even rarer.

Add a trigger to the tables to monitor and either set a timestamp/sequence
value or just an "updated" flag every time a row is inserted/updated.
Deleting rows is a bit tricker - you either need to make an entry in a log
table (table deleted from, key fields). Or, you can mark them deleted (set a
status field="D") and remove them after syncing.

Then, a simple Perl (or other) script to scan for changed items, dump to a
file and then reset flags if necessary (all in 1 transaction).

I'd recommend a timestamp/sequence value on each row, since you'll want to be
able to check whether the two systems are in sync after something's crashed.

- Richard Huxton