Обсуждение: syncronizing databases?

Hi All,

If this is the wrong list to post this question to forgive me, correct
me and I'll be out of your hair.

Here's my question:

I have two identical databases setup on separate servers and I'm
looking for ideas on how to keep the data syncronized.  I'm wondering if
there is some feature or function of PostgresQL that will compair tables
directly or do I need to create my own some how.  I'm running PostgresQL
v7.1 on RedHat 7.2 servers and have a couple of users that will be
maintaining data and need to find a simple method of updating one db
based on the other one.  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?

Any suggestions are greatly appreciated.

Thanks in advance,
Linn

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

> I have two identical databases setup on separate servers and I'm
> looking for ideas on how to keep the data syncronized.

PostgreSQL Inc. offers a replication package if you are looking for that
kind of thing...

                      - Thomas

Thanks to all who responded to my question.  Just to bring closure to
this thread it looks like the easiest approach is to build my own at
this point.  So that's what I'll do.

Thanks again,
Linn

>>> "Linn Kubler" <LKubler@ecw.org> 07/11/02 11:09AM >>>
Hi All,

If this is the wrong list to post this question to forgive me, correct
me and I'll be out of your hair.

Here's my question:

I have two identical databases setup on separate servers and I'm
looking for ideas on how to keep the data syncronized.  I'm wondering
if
there is some feature or function of PostgresQL that will compair
tables
directly or do I need to create my own some how.  I'm running
PostgresQL
v7.1 on RedHat 7.2 servers and have a couple of users that will be
maintaining data and need to find a simple method of updating one db
based on the other one.  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?

Any suggestions are greatly appreciated.

Thanks in advance,
Linn


---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly