Please apply this patch to contrib/dbmirror

In incorperates changes from myself and a number of contributors.

This update to dbmirror provides:

-replication of sequence operations via setval/nextval
-DBMirror.pl support for logging to syslog
-changed the names of the tables to dbmirror_*  (no quotes required)
-Support for writitng SQL statements to files instead of directly to
 a slave database
-More options for DBMirror.pl in the config files.

Steven Singer

1 files changed, 44 insertions, 26 deletions

diff --git a/contrib/dbmirror/MirrorSetup.sql b/contrib/dbmirror/MirrorSetup.sql
index 4227ca5f39..cae686f929 100644
--- a/contrib/dbmirror/MirrorSetup.sql
+++ b/contrib/dbmirror/MirrorSetup.sql
@@ -1,43 +1,61 @@
+BEGIN;
+
+SET autocommit TO 'on';
 
 CREATE FUNCTION "recordchange" () RETURNS trigger AS
-'/usr/local/pgsql/lib/pending.so', 'recordchange' LANGUAGE 'C';
+'$libdir/pending.so', 'recordchange' LANGUAGE 'C';
+
 
-CREATE TABLE "MirrorHost" (
-"MirrorHostId" serial,
-"HostName" varchar NOT NULL,
-PRIMARY KEY("MirrorHostId")
-);
 
+CREATE TABLE dbmirror_MirrorHost (
+MirrorHostId serial not null,
+SlaveName varchar NOT NULL,
+PRIMARY KEY(MirrorHostId)
+);
 
 
 
 
-CREATE TABLE "Pending" (
-"SeqId" serial,
-"TableName" varchar NOT NULL,
-"Op" character,
-"XID" int4 NOT NULL,
-PRIMARY KEY ("SeqId")
 
+CREATE TABLE dbmirror_Pending (
+SeqId serial,
+TableName Name NOT NULL,
+Op character,
+XID int4 NOT NULL,
+PRIMARY KEY (SeqId)
 );
 
-CREATE INDEX "Pending_XID_Index" ON "Pending" ("XID");
+CREATE INDEX "dbmirror_Pending_XID_Index" ON dbmirror_Pending (XID);
 
-CREATE TABLE "PendingData" (
-"SeqId" int4 NOT NULL,
-"IsKey" bool NOT NULL,
-"Data" varchar,
-PRIMARY KEY ("SeqId", "IsKey") ,
-FOREIGN KEY ("SeqId") REFERENCES "Pending" ("SeqId") ON UPDATE CASCADE  ON DELETE CASCADE
+CREATE TABLE dbmirror_PendingData (
+SeqId int4 NOT NULL,
+IsKey bool NOT NULL,
+Data varchar,
+PRIMARY KEY (SeqId, IsKey) ,
+FOREIGN KEY (SeqId) REFERENCES dbmirror_Pending (SeqId) ON UPDATE CASCADE  ON DELETE CASCADE
 );
 
 
-CREATE TABLE "MirroredTransaction" (
-"XID" int4 NOT NULL,
-"LastSeqId" int4 NOT NULL,
-"MirrorHostId" int4 NOT NULL,
-PRIMARY KEY  ("XID","MirrorHostId"),
-FOREIGN KEY ("MirrorHostId") REFERENCES "MirrorHost" ("MirrorHostId") ON UPDATE CASCADE ON DELETE CASCADE,
-FOREIGN KEY ("LastSeqId") REFERENCES "Pending" ("SeqId")  ON UPDATE
+CREATE TABLE dbmirror_MirroredTransaction (
+XID int4 NOT NULL,
+LastSeqId int4 NOT NULL,
+MirrorHostId int4 NOT NULL,
+PRIMARY KEY  (XID,MirrorHostId),
+FOREIGN KEY (MirrorHostId) REFERENCES dbmirror_MirrorHost (MirrorHostId) ON UPDATE CASCADE ON DELETE CASCADE,
+FOREIGN KEY (LastSeqId) REFERENCES dbmirror_Pending (SeqId)  ON UPDATE
 CASCADE ON DELETE CASCADE
 );
+
+
+UPDATE pg_proc SET proname='nextval_pg' WHERE proname='nextval';
+
+CREATE FUNCTION pg_catalog.nextval(text) RETURNS int8  AS
+'/usr/local/postgresql-7.4/lib/pending.so', 'nextval' LANGUAGE 'C' STRICT;
+
+
+UPDATE pg_proc set proname='setval_pg' WHERE proname='setval';
+
+CREATE FUNCTION pg_catalog.setval(text,int4) RETURNS int8  AS
+'/usr/local/postgresql-7.4/lib/pending.so', 'setval' LANGUAGE 'C' STRICT;
+
+COMMIT;
\ No newline at end of file