Thread: 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