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
