Here is a patch for dbmirror to be applied for the next beta.

-It fixes up some bugs with handling setval calls
-Adds upgrade instructions from prior versions
-Improved the sample config file
-Fixed some things in the clean_pending script


Thanks

-- 
Steven Singer                                       [EMAIL PROTECTED]
Dispatch Systems                            Phone:  519-747-1170 ext 282
Navtech Systems Support Inc.                AFTN:   CYYZXNSX SITA: YYZNSCR
Waterloo, Ontario                           ARINC:  YKFNSCR
Index: MirrorSetup.sql
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/dbmirror/MirrorSetup.sql,v
retrieving revision 1.6
diff -c -r1.6 MirrorSetup.sql
*** MirrorSetup.sql     17 Feb 2004 03:34:35 -0000      1.6
--- MirrorSetup.sql     3 Sep 2004 01:24:31 -0000
***************
*** 1,6 ****
  BEGIN;
  
- SET autocommit TO 'on';
  
  CREATE FUNCTION "recordchange" () RETURNS trigger AS
  '$libdir/pending.so', 'recordchange' LANGUAGE 'C';
--- 1,5 ----
***************
*** 25,31 ****
  PRIMARY KEY (SeqId)
  );
  
! CREATE INDEX "dbmirror_Pending_XID_Index" ON dbmirror_Pending (XID);
  
  CREATE TABLE dbmirror_PendingData (
  SeqId int4 NOT NULL,
--- 24,30 ----
  PRIMARY KEY (SeqId)
  );
  
! CREATE INDEX dbmirror_Pending_XID_Index ON dbmirror_Pending (XID);
  
  CREATE TABLE dbmirror_PendingData (
  SeqId int4 NOT NULL,
***************
*** 50,61 ****
  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
--- 49,62 ----
  UPDATE pg_proc SET proname='nextval_pg' WHERE proname='nextval';
  
  CREATE FUNCTION pg_catalog.nextval(text) RETURNS int8  AS
! '$libdir/pending.so', 'nextval' LANGUAGE 'C' STRICT;
  
  
  UPDATE pg_proc set proname='setval_pg' WHERE proname='setval';
  
! CREATE FUNCTION pg_catalog.setval("unknown",integer,boolean) RETURNS int8  AS
! '$libdir/pending.so', 'setval' LANGUAGE 'C' STRICT;
! CREATE FUNCTION pg_catalog.setval("unknown",integer) RETURNS int8  AS
! '$libdir/pending.so', 'setval' LANGUAGE 'C' STRICT;
  
! COMMIT;
Index: README.dbmirror
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/dbmirror/README.dbmirror,v
retrieving revision 1.8
diff -c -r1.8 README.dbmirror
*** README.dbmirror     17 Feb 2004 03:34:35 -0000      1.8
--- README.dbmirror     3 Sep 2004 01:24:31 -0000
***************
*** 61,70 ****
--- 61,89 ----
  -PgPerl (http://gborg.postgresql.org/project/pgperl/projdisplay.php)
  
  
+ Upgrading from versions prior to 8.0
+ ---------------------------------------
+ Users upgrading from a version of dbmirror prior to the one shipped with 
+ Postgresql 8.0 will need to perform the following steps
+ 
+ 1. Dump the database then drop it (dropdb no not use the -C option)
+ 2. Create database with createdb.
+ 3. Run psql databasename -f MirrorSetup.sql
+ 4. Restore the database(do not use the -C option of pg_dump/pg_restore)
+ 5. run the SQL commands: DROP "Pending";DROP "PendingData"; DROP "MirrorHost";
+    DROP "MirroredTransaction";
+ 
+ The above steps are needed A) Because the names of the tables used by dbmirror
+ to store data have changed and B) In order for sequences to be mirrored properly
+ all serial types must be recreated.
+ 
+ 
+ 
  Installation Instructions
  ------------------------------------------------------------------------
  
  
+ 
  1) Compile pending.c
  
  The file pending.c contains the recordchange trigger.  This runs every
Index: clean_pending.pl
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/dbmirror/clean_pending.pl,v
retrieving revision 1.4
diff -c -r1.4 clean_pending.pl
*** clean_pending.pl    29 Nov 2003 22:39:19 -0000      1.4
--- clean_pending.pl    3 Sep 2004 01:24:31 -0000
***************
*** 77,89 ****
  
  #delete all transactions that have been sent to all mirrorhosts
  #or delete everything if no mirror hosts are defined.
! # Postgres takes the "SELECT COUNT(*) FROM "MirrorHost"  and makes it into
  # an InitPlan.  EXPLAIN show's this.  
! my $deletePendingQuery = 'DELETE FROM "Pending" WHERE (SELECT ';
! $deletePendingQuery .= ' COUNT(*) FROM "MirroredTransaction" WHERE ';
! $deletePendingQuery .= ' "XID"="Pending"."XID") = (SELECT COUNT(*) FROM ';
! $deletePendingQuery .= ' "MirrorHost") OR (SELECT COUNT(*) FROM ';
! $deletePendingQuery .= ' "MirrorHost") = 0';
  
  my $result = $dbConn->exec($deletePendingQuery);
  unless ($result->resultStatus == PGRES_COMMAND_OK ) {
--- 77,89 ----
  
  #delete all transactions that have been sent to all mirrorhosts
  #or delete everything if no mirror hosts are defined.
! # Postgres takes the "SELECT COUNT(*) FROM dbmirror_MirrorHost  and makes it into
  # an InitPlan.  EXPLAIN show's this.  
! my $deletePendingQuery = 'DELETE FROM dbmirror_Pending WHERE (SELECT ';
! $deletePendingQuery .= ' COUNT(*) FROM dbmirror_MirroredTransaction WHERE ';
! $deletePendingQuery .= ' XID=dbmirror_Pending.XID) = (SELECT COUNT(*) FROM ';
! $deletePendingQuery .= ' dbmirror_MirrorHost) OR (SELECT COUNT(*) FROM ';
! $deletePendingQuery .= ' dbmirror_MirrorHost) = 0';
  
  my $result = $dbConn->exec($deletePendingQuery);
  unless ($result->resultStatus == PGRES_COMMAND_OK ) {
***************
*** 91,105 ****
      die;
  }
  $dbConn->exec("COMMIT");
! $result = $dbConn->exec('VACUUM "Pending"');
  unless ($result->resultStatus == PGRES_COMMAND_OK) {
     printf($dbConn->errorMessage);
  }
! $result = $dbConn->exec('VACUUM "PendingData"');
  unless($result->resultStatus == PGRES_COMMAND_OK) {
     printf($dbConn->errorMessage);
  }
! $result = $dbConn->exec('VACUUM "MirroredTransaction"');
  unless($result->resultStatus == PGRES_COMMAND_OK) {
    printf($dbConn->errorMessage);
  }
--- 91,105 ----
      die;
  }
  $dbConn->exec("COMMIT");
! $result = $dbConn->exec('VACUUM dbmirror_Pending');
  unless ($result->resultStatus == PGRES_COMMAND_OK) {
     printf($dbConn->errorMessage);
  }
! $result = $dbConn->exec('VACUUM dbmirror_PendingData');
  unless($result->resultStatus == PGRES_COMMAND_OK) {
     printf($dbConn->errorMessage);
  }
! $result = $dbConn->exec('VACUUM dbmirror_MirroredTransaction');
  unless($result->resultStatus == PGRES_COMMAND_OK) {
    printf($dbConn->errorMessage);
  }
Index: pending.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/dbmirror/pending.c,v
retrieving revision 1.19
diff -c -r1.19 pending.c
*** pending.c   29 Aug 2004 05:06:35 -0000      1.19
--- pending.c   3 Sep 2004 01:24:31 -0000
***************
*** 63,69 ****
  
  #define BUFFER_SIZE 256
  #define MAX_OID_LEN 10
! #define DEBUG_OUTPUT 1
  extern Datum recordchange(PG_FUNCTION_ARGS);
  
  PG_FUNCTION_INFO_V1(recordchange);
--- 63,69 ----
  
  #define BUFFER_SIZE 256
  #define MAX_OID_LEN 10
! /*#define DEBUG_OUTPUT 1 */
  extern Datum recordchange(PG_FUNCTION_ARGS);
  
  PG_FUNCTION_INFO_V1(recordchange);
***************
*** 596,613 ****
  
        text       *sequenceName;
  
!       Oid                     setvalArgTypes[2] = {TEXTOID, INT4OID};
        int                     nextValue;
        void       *setvalPlan = NULL;
!       Datum           setvalData[2];
!       const char *setvalQuery = "SELECT setval_pg($1,$2)";
        int                     ret;
  
        sequenceName = PG_GETARG_TEXT_P(0);
        nextValue = PG_GETARG_INT32(1);
  
        setvalData[0] = PointerGetDatum(sequenceName);
        setvalData[1] = Int32GetDatum(nextValue);
  
        if (SPI_connect() < 0)
        {
--- 596,623 ----
  
        text       *sequenceName;
  
!       Oid                     setvalArgTypes[3] = {TEXTOID, INT4OID,BOOLOID};
        int                     nextValue;
        void       *setvalPlan = NULL;
!       Datum           setvalData[3];
!       const char *setvalQuery = "SELECT setval_pg($1,$2,$3)";
        int                     ret;
+         char                    is_called;
  
        sequenceName = PG_GETARG_TEXT_P(0);
        nextValue = PG_GETARG_INT32(1);
+       is_called = PG_GETARG_BOOL(2);
  
        setvalData[0] = PointerGetDatum(sequenceName);
        setvalData[1] = Int32GetDatum(nextValue);
+       if(PG_NARGS() > 2)
+         {
+           setvalData[2] = BoolGetDatum(is_called);
+         }
+       else
+         {
+           setvalData[2]=1;
+         }
  
        if (SPI_connect() < 0)
        {
***************
*** 616,622 ****
                return -1;
        }
  
!       setvalPlan = SPI_prepare(setvalQuery, 2, setvalArgTypes);
        if (setvalPlan == NULL)
        {
                ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION),
--- 626,632 ----
                return -1;
        }
  
!       setvalPlan = SPI_prepare(setvalQuery, 3, setvalArgTypes);
        if (setvalPlan == NULL)
        {
                ereport(ERROR, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION),
Index: slaveDatabase.conf
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/dbmirror/slaveDatabase.conf,v
retrieving revision 1.2
diff -c -r1.2 slaveDatabase.conf
*** slaveDatabase.conf  29 Nov 2003 22:39:19 -0000      1.2
--- slaveDatabase.conf  3 Sep 2004 01:24:31 -0000
***************
*** 4,10 ****
  # It contains configuration information to mirror data from 
  # the master database to a single slave system.
  #
! # $PostgreSQL: pgsql-server/contrib/dbmirror/slaveDatabase.conf,v 1.2 2003/11/29 
22:39:19 pgsql Exp $
  #######################################################################
  
  $masterHost = "masterMachine.mydomain.com";
--- 4,10 ----
  # It contains configuration information to mirror data from 
  # the master database to a single slave system.
  #
! # $Id: slaveDatabase.conf,v 1.5 2004/01/27 20:59:37 ssinger Exp $
  #######################################################################
  
  $masterHost = "masterMachine.mydomain.com";
***************
*** 15,22 ****
--- 15,35 ----
  # Where to email Error messages to
  # $errorEmailAddr = "[EMAIL PROTECTED]";
  
+ $slaveInfo->{"slaveName"} = "backupMachine";
  $slaveInfo->{"slaveHost"} = "backupMachine.mydomain.com";
  $slaveInfo->{"slaveDb"} = "myDatabase";
+ $slaveInfo->{"slavePort"} = 5432;
  $slaveInfo->{"slaveUser"} = "postgres";
  $slaveInfo->{"slavePassword"} = "postgrespassword";
+ # If uncommented then text files with SQL statements are generated instead
+ # of connecting to the slave database directly. 
+ # slaveDb should then be commented out.
+ # $slaveInfo{"TransactionFileDirectory"} = '/tmp';
  
+ #
+ # The number of seconds dbmirror should sleep for between checking to see
+ # if more data is ready to be mirrored.
+ $sleepInterval = 60;
+ 
+ #If you want to use syslog
+ # $syslog = 1;
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to