Hello, I would like some thoughts on the folowing plan. I plan to
replicate several remote databases with a master db.
The setup is in a small retail chain, so each store will have it's own
postgres db making changes during the day. After hours it will didla up
head office twice.
The first time it will upload all it's new data, then upload any updated
data. Then disconect allowing other stores ti dial up.
The second dial up, some several hours later it will then downloadall
new and changed data from the master db, their by being in sync with all
stores before sunrise (on a good day :-)
All replicated tables have the folowing columns:
CREATE TABLE "sale_lines" (
"loc_seq_pkey" text DEFAULT set_primary_key() NOT NULL,
"timestamp" timestamp DEFAULT 'now()',
"version" int4 DEFAULT 0,
"f_new" character varying,
"f_update" character varying,
PRIMARY KEY ("loc_seq_pkey")
);
The loc_seq_pkey is basically a sequence on the locla machine with the
store location id prepended. eg : 1-46 is location 1, 46th new row added
anywhere in the db. This alows me to look at an incoming new row to be
added and say "No. thats mine, no need to add it" and then check to see
if it need updating instead.
There is a triger on all replicated tables:
CREATE FUNCTION "version_control" ( ) RETURNS opaque AS 'BEGIN
IF TG_OP = ''UPDATE'' THEN
IF NEW.f_update = ''RESET'' THEN
NEW.f_update := ''NO'';
NEW.f_new := ''FALSE'';
ELSE
NEW.f_update := ''YES'';
NEW.version := OLD.version + 1;
END IF;
RETURN NEW;
END IF;
IF TG_OP = ''INSERT'' THEN
NEW.f_new := ''TRUE'';
RETURN NEW;
END IF;
IF TG_OP = ''DELETE'' THEN
RETURN OLD;
END IF;
END;
' LANGUAGE 'plpgsql';
I was thinking of useing teh version field for checking if the data has
been changed buy other sites before being sent back to the original
site.
Is this going to work or will it bog down and die?
--
*
* Rob Brown-Bayliss
*