This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
From: "Simon Windsor" <swindsor@nildram.co.uk> To: <pgsql-general@postgresql.org> Subject: [GENERAL] Replication options Date: Wed, 18 Feb 2004 20:58:28 -0000 I am fairly news to Postgres, but have spent many years using Oracle (15) and MySQL(5). Whilst I find Postgres very easy to 'pickup', the number of replication options are puzzling. There are a number of replication options available to Postgres. Can anyone on this list advise suitable methods (ie Replicator, dbexperts, GBORG etc) for MASTER-MASTER and MASTER-SLAVE(Multiple) replication. === Date: Fri, 20 Feb 2004 17:28:02 -0500 From: Andrew Sullivan <ajs@crankycanuck.ca> To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Replication options Simon Windsor wrote: > for MASTER-MASTER None. Postgres-R is intended to offer it, but it requires work still. > and MASTER-SLAVE(Multiple) replication. All the rest, except for contrib/rserv, which does only one slave. I have used the erserver on gborg and none of the rest, so I cannot comment on their relative merits. === Date: Tue, 24 Feb 2004 04:48:19 -0800 (PST) From: merino silva <merinosilva@yahoo.com> Subject: [HACKERS] converting the DBMirror as peer-to-peer replicator To: pgsql-hackers@postgresql.org Cc: shihan@codegen.net, merino_silva@yahoo.com --0-1461884395-1077626899=:49393 Content-Type: text/plain; charset=us-ascii Content-Id: Content-Disposition: inline Hi, I've already subscribed my e-mail address to the pgsql-hackers. The method I've used to convert DBMirror to a peer-to-peer replicator was two DBMirror instances with one considered slave of other as the master. Here, I've stopped the loop back by dropping the trigger when INSERT, DELETE, UPDATE through the DBMirror and then creating the trigger again. The system was tested on the ManddrakeLinux 9.2 Posgresql 7.3.4 and Perl v5.8.1 A 'LOCK TABLE <table name> IN SHARE MODE' was used before dropping the trigger to prevent updating the client database, because updates for that database wont trigger to the remaining one while the trigger is dropped. The modified version is working when inserting large number of data (10000), but when deleting the client (one database) seems to be not responding. The modified DBMirror.pl is attached with this. #!/usr/bin/perl ############################################################################# # # DBMirror.pl # Contains the Database mirroring script. # This script queries the pending table off the database specified # (along with the associated schema) for updates that are pending on a # specific host. The database on that host is then updated with the changes. # # # Written by Steven Singer (ssinger@navtechinc.com) # (c) 2001-2002 Navtech Systems Support Inc. # ALL RIGHTS RESERVED; # # Permission to use, copy, modify, and distribute this software and its # documentation for any purpose, without fee, and without a written agreement # is hereby granted, provided that the above copyright notice and this # paragraph and the following two paragraphs appear in all copies. # # IN NO EVENT SHALL THE AUTHOR OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR # DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING # LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS # DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE # POSSIBILITY OF SUCH DAMAGE. # # THE AUTHOR AND DISTRIBUTORS SPECIFICALLY DISCLAIMS ANY WARRANTIES, # INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY # AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS # ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO # PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. # # # # ############################################################################## # $Id: DBMirror.pl,v 1.3.2.2 2002/12/05 21:04:14 momjian Exp $ # ############################################################################## =head1 NAME DBMirror.pl - A Perl module to mirror database changes from a master database to a slave. =head1 SYNPOSIS DBMirror.pl slaveConfigfile.conf =head1 DESCRIPTION This Perl script will connect to the master database and query its pending table for a list of pending changes. The transactions of the original changes to the master will be preserved when sending things to the slave. =cut =head1 METHODS =over 4 =cut BEGIN { # add in a global path to files # Pg should be included. } use strict; use Pg; use IO::Handle; sub mirrorCommand($$$$$$); sub mirrorInsert($$$$$); sub mirrorDelete($$$$$); sub mirrorUpdate($$$$$); sub sendQueryToSlaves($$); sub logErrorMessage($); sub openSlaveConnection($); sub updateMirrorHostTable($$); sub extractData($$); local $::masterHost; local $::masterDb; local $::masterUser; local $::masterPassword; local $::masterPort; local $::errorThreshold=5; local $::errorEmailAddr=undef; local $::mirrorSleepTime; my %slaveInfoHash; local $::slaveInfo = \%slaveInfoHash; my $lastErrorMsg; my $repeatErrorCount=0; my $lastXID; my $commandCount=0; my $masterConn; Main(); sub Main() { #run the configuration file. if ($#ARGV != 0) { die "usage: DBMirror.pl configFile\n"; } if( ! defined do $ARGV[0]) { logErrorMessage("Invalid Configuration file $ARGV[0]"); die; } my $mirrorSleepTime = $::mirrorSleepTime; my $connectString = "host=$::masterHost dbname=$::masterDb port=$::masterPort user=$::masterUser password=$::masterPassword"; $masterConn = Pg::connectdb($connectString); unless($masterConn->status == PGRES_CONNECTION_OK) { logErrorMessage("Can't connect to master database\n" . $masterConn->errorMessage); die; } my $setQuery; $setQuery = "SET search_path = public"; my $setResult = $masterConn->exec($setQuery); if($setResult->resultStatus!=PGRES_COMMAND_OK) { logErrorMessage($masterConn->errorMessage . "\n" . $setQuery); die; } my $setQuery2; $setQuery2 = "SET autocommit TO 'on'"; my $setResult2 = $masterConn->exec($setQuery2); if($setResult2->resultStatus!=PGRES_COMMAND_OK) { logErrorMessage($masterConn->errorMessage . "\n" . $setQuery2); die; } while(1) { # Open up the connection to the slave. if(! defined $::slaveInfo->{"status"} || $::slaveInfo->{"status"} == -1) { openSlaveConnection($::slaveInfo); } sendQueryToSlaves(undef,"SET autocommit TO 'on'"); sendQueryToSlaves(undef,"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); sendQueryToSlaves(undef,"SET CONSTRAINTS ALL DEFERRED"); #Obtain a list of pending transactions using ordering by our approximation #to the commit time. The commit time approximation is taken to be the #SeqId of the last row edit in the transaction. my $pendingTransQuery = "SELECT pd.\"XID\",MAX(\"SeqId\") FROM \"Pending\" pd"; $pendingTransQuery .= " LEFT JOIN \"MirroredTransaction\" mt INNER JOIN"; $pendingTransQuery .= " \"MirrorHost\" mh ON mt.\"MirrorHostId\" = "; $pendingTransQuery .= " mh.\"MirrorHostId\" AND mh.\"HostName\"="; $pendingTransQuery .= " '$::slaveInfo->{\"slaveHost\"}' "; $pendingTransQuery .= " ON pd.\"XID\""; $pendingTransQuery .= " = mt.\"XID\" WHERE mt.\"XID\" is null "; $pendingTransQuery .= " GROUP BY pd.\"XID\" "; $pendingTransQuery .= " ORDER BY MAX(pd.\"SeqId\")"; my $pendingTransResults = $masterConn->exec($pendingTransQuery); unless($pendingTransResults->resultStatus==PGRES_TUPLES_OK) { logErrorMessage("Can't query pending table\n" . $masterConn->errorMessage); die; } my $numPendingTrans = $pendingTransResults->ntuples; my $curTransTuple = 0; # # This loop loops through each pending transaction in the proper order. # The Pending row edits for that transaction will be queried from the # master and sent + committed to the slaves. while($curTransTuple < $numPendingTrans) { my $XID = $pendingTransResults->getvalue($curTransTuple,0); my $maxSeqId = $pendingTransResults->getvalue($curTransTuple,1); my $seqId; my $pendingQuery = "SELECT pnd.\"SeqId\",pnd.\"TableName\","; $pendingQuery .= " pnd.\"Op\",pnddata.\"IsKey\", pnddata.\"Data\" AS \"Data\" "; $pendingQuery .= " FROM \"Pending\" pnd, \"PendingData\" pnddata "; $pendingQuery .= " WHERE pnd.\"SeqId\" = pnddata.\"SeqId\" AND "; $pendingQuery .= " pnd.\"XID\"=$XID ORDER BY \"SeqId\", \"IsKey\" DESC"; my $pendingResults = $masterConn->exec($pendingQuery); unless($pendingResults->resultStatus==PGRES_TUPLES_OK) { logErrorMessage("Can't query pending table\n" . $masterConn->errorMessage); die; } my $numPending = $pendingResults->ntuples; my $curTuple = 0; sendQueryToSlaves(undef,"BEGIN"); while ($curTuple < $numPending) { $seqId = $pendingResults->getvalue($curTuple,0); my $tableName = $pendingResults->getvalue($curTuple,1); my $op = $pendingResults->getvalue($curTuple,2); $curTuple = mirrorCommand($seqId,$tableName,$op,$XID, $pendingResults,$curTuple) +1; if($::slaveInfo->{"status"}==-1) { last; } # sleep 10; } #Now commit the transaction. if($::slaveInfo->{"status"}==-1) { last; } sendQueryToSlaves(undef,"COMMIT"); updateMirrorHostTable($XID,$seqId); if($commandCount > 5000) { $commandCount = 0; $::slaveInfo->{"status"} = -1; $::slaveInfo->{"slaveConn"}->reset; #Open the connection right away. openSlaveConnection($::slaveInfo); } $pendingResults = undef; $curTransTuple = $curTransTuple +1; }#while transactions left. $pendingTransResults = undef; sleep $mirrorSleepTime; # set by slaveDatabase.conf }#while(1) }#Main =item mirrorCommand(SeqId,tableName,op,transId,pendingResults,curTuple) Mirrors a single SQL Command(change to a single row) to the slave. =over 4 =item * SeqId The id number of the change to mirror. This is the primary key of the pending table. =item * tableName The name of the table the transaction takes place on. =item * op The type of operation this transaction is. 'i' for insert, 'u' for update or 'd' for delete. =item * transId The Transaction of of the Transaction that this command is part of. =item * pendingResults A Results set structure returned from Pg::execute that contains the join of the Pending and PendingData tables for all of the pending row edits in this transaction. =item * currentTuple The tuple(or row) number of the pendingRow for the command that is about to be edited. If the command is an update then this points to the row with IsKey equal to true. The next row, curTuple+1 is the contains the PendingData with IsKey false for the update. =item returns The tuple number of last tuple for this command. This might be equal to currentTuple or it might be larger (+1 in the case of an Update). =back =cut sub mirrorCommand($$$$$$) { my $seqId = $_[0]; my $tableName = $_[1]; my $op = $_[2]; my $transId = $_[3]; my $pendingResults = $_[4]; my $currentTuple = $_[5]; if($op eq 'i') { $currentTuple = mirrorInsert($seqId,$tableName,$transId,$pendingResults ,$currentTuple); } if($op eq 'd') { $currentTuple = mirrorDelete($seqId,$tableName,$transId,$pendingResults, $currentTuple); } if($op eq 'u') { $currentTuple = mirrorUpdate($seqId,$tableName,$transId,$pendingResults, $currentTuple); } $commandCount = $commandCount +1; if($commandCount % 100 == 0) { # print "Sent 100 commmands on SeqId $seqId \n"; # flush STDOUT; } return $currentTuple } =item mirrorInsert(transId,tableName,transId,pendingResults,currentTuple) Mirrors an INSERT operation to the slave database. A new row is placed in the slave database containing the primary key from pendingKeys along with the data fields contained in the row identified by sourceOid. =over 4 =item * transId The sequence id of the INSERT operation being mirrored. This is the primary key of the pending table. =item * tableName The name of the table the transaction takes place on. =item * sourceOid The OID of the row in the master database for which this transaction effects. If the transaction is a delete then the operation is not valid. =item * transId The Transaction Id of transaction that this insert is part of. =item * pendingResults A Results set structure returned from Pg::execute that contains the join of the Pending and PendingData tables for all of the pending row edits in this transaction. =item * currentTuple The tuple(or row) number of the pendingRow for the command that is about to be edited. In the case of an insert this should point to the one row for the row edit. =item returns The tuple number of the last tuple for the row edit. This should be currentTuple. =back =cut sub mirrorInsert($$$$$) { my $seqId = $_[0]; my $tableName = $_[1]; my $transId = $_[2]; my $pendingResults = $_[3]; my $currentTuple = $_[4]; my $counter; my $column; my $firstIteration=1; my %recordValues = extractData($pendingResults,$currentTuple); #Now build the insert query. my $insertQuery = "INSERT INTO $tableName ("; my $valuesQuery = ") VALUES ("; foreach $column (keys (%recordValues)) { if($firstIteration==0) { $insertQuery .= " ,"; $valuesQuery .= " ,"; } $insertQuery .= "\"$column\""; if(defined $recordValues{$column}) { my $quotedValue = $recordValues{$column}; $quotedValue =~ s/\\/\\\\/g; $quotedValue =~ s/'/\\'/g; $valuesQuery .= "'$quotedValue'"; } else { $valuesQuery .= "null"; } $firstIteration=0; } $valuesQuery .= ")"; my @namearray = split /\"/,$tableName; sendQueryToSlaves(undef,"LOCK TABLE ".$namearray[3]." IN SHARE MODE"); # sleep 10; sendQueryToSlaves(undef,"DROP TRIGGER \"".$namearray[3]."_Trig\" ON ".$namearray[3]); sendQueryToSlaves(undef,$insertQuery . $valuesQuery); sendQueryToSlaves(undef,"CREATE TRIGGER \"".$namearray[3]."_Trig\" AFTER INSERT OR DELETE OR UPDATE ON ".$namearray[3]." FOR EACH ROW EXECUTE PROCEDURE \"recordchange\" ()"); return $currentTuple; } =item mirrorDelete(SeqId,tableName,transId,pendingResult,currentTuple) Deletes a single row from the slave database. The row is identified by the primary key for the transaction in the pendingKeys table. =over 4 =item * SeqId The Sequence id for this delete request. =item * tableName The name of the table to delete the row from. =item * transId The Transaction Id of the transaction that this command is part of. =item * pendingResults A Results set structure returned from Pg::execute that contains the join of the Pending and PendingData tables for all of the pending row edits in this transaction. =item * currentTuple The tuple(or row) number of the pendingRow for the command that is about to be edited. In the case of a delete this should point to the one row for the row edit. =item returns The tuple number of the last tuple for the row edit. This should be currentTuple. =back =cut sub mirrorDelete($$$$$) { my $seqId = $_[0]; my $tableName = $_[1]; my $transId = $_[2]; my $pendingResult = $_[3]; my $currentTuple = $_[4]; my %dataHash; my $currentField; my $firstField=1; %dataHash = extractData($pendingResult,$currentTuple); my $counter=0; my $deleteQuery = "DELETE FROM $tableName WHERE "; foreach $currentField (keys %dataHash) { if($firstField==0) { $deleteQuery .= " AND "; } my $currentValue = $dataHash{$currentField}; $deleteQuery .= "\""; $deleteQuery .= $currentField; if(defined $currentValue) { $deleteQuery .= "\"='"; $deleteQuery .= $currentValue; $deleteQuery .= "'"; } else { $deleteQuery .= " is null "; } $counter++; $firstField=0; } my @namearray = split /\"/,$tableName; sendQueryToSlaves(undef,"LOCK TABLE ".$namearray[3]." IN SHARE MODE"); # sleep 10; sendQueryToSlaves(undef,"DROP TRIGGER \"".$namearray[3]."_Trig\" ON ".$namearray[3]); sendQueryToSlaves($transId,$deleteQuery); sendQueryToSlaves(undef,"CREATE TRIGGER \"".$namearray[3]."_Trig\" AFTER INSERT OR DELETE OR UPDATE ON ".$namearray[3]." FOR EACH ROW EXECUTE PROCEDURE \"recordchange\" ()"); return $currentTuple; } =item mirrorUpdate(seqId,tableName,transId,pendingResult,currentTuple) Mirrors over an edit request to a single row of the database. The primary key from before the edit is used to determine which row in the slave should be changed. After the edit takes place on the slave its primary key will match the primary key the master had immediatly following the edit. All other fields will be set to the current values. Data integrity is maintained because the mirroring is performed in an SQL transcation so either all pending changes are made or none are. =over 4 =item * seqId The Sequence id of the update. =item * tableName The name of the table to perform the update on. =item * transId The transaction Id for the transaction that this command is part of. =item * pendingResults A Results set structure returned from Pg::execute that contains the join of the Pending and PendingData tables for all of the pending row edits in this transaction. =item * currentTuple The tuple(or row) number of the pendingRow for the command that is about to be edited. In the case of a delete this should point to the one row for the row edit. =item returns The tuple number of the last tuple for the row edit. This should be currentTuple +1. Which points to the non key row of the update. =back =cut sub mirrorUpdate($$$$$) { my $seqId = $_[0]; my $tableName = $_[1]; my $transId = $_[2]; my $pendingResult = $_[3]; my $currentTuple = $_[4]; my $counter; my $quotedValue; my $updateQuery = "UPDATE $tableName SET "; my $currentField; my %keyValueHash; my %dataValueHash; my $firstIteration=1; #Extract the Key values. This row contains the values of the # key fields before the update occours(the WHERE clause) %keyValueHash = extractData($pendingResult,$currentTuple); #Extract the data values. This is a SET clause that contains #values for the entire row AFTER the update. %dataValueHash = extractData($pendingResult,$currentTuple+1); $firstIteration=1; foreach $currentField (keys (%dataValueHash)) { if($firstIteration==0) { $updateQuery .= ", "; } $updateQuery .= " \"$currentField\"="; my $currentValue = $dataValueHash{$currentField}; if(defined $currentValue ) { $quotedValue = $currentValue; $quotedValue =~ s/\\/\\\\/g; $quotedValue =~ s/'/\\'/g; $updateQuery .= "'$quotedValue'"; } else { $updateQuery .= "null "; } $firstIteration=0; } $updateQuery .= " WHERE "; $firstIteration=1; foreach $currentField (keys (%keyValueHash)) { my $currentValue; if($firstIteration==0) { $updateQuery .= " AND "; } $updateQuery .= "\"$currentField\"="; $currentValue = $keyValueHash{$currentField}; if(defined $currentValue) { $quotedValue = $currentValue; $quotedValue =~ s/\\/\\\\/g; $quotedValue =~ s/'/\\'/g; $updateQuery .= "'$quotedValue'"; } else { $updateQuery .= " null "; } $firstIteration=0; } my @namearray = split /\"/,$tableName; sendQueryToSlaves(undef,"LOCK TABLE ".$namearray[3]." IN SHARE MODE"); # sleep 10; sendQueryToSlaves(undef,"DROP TRIGGER \"".$namearray[3]."_Trig\" ON ".$namearray[3]); sendQueryToSlaves($transId,$updateQuery); sendQueryToSlaves(undef,"CREATE TRIGGER \"".$namearray[3]."_Trig\" AFTER INSERT OR DELETE OR UPDATE ON ".$namearray[3]." FOR EACH ROW EXECUTE PROCEDURE \"recordchange\" ()"); return $currentTuple+1; } =item sendQueryToSlaves(seqId,sqlQuery) Sends an SQL query to the slave. =over 4 =item * seqId The sequence Id of the command being sent. Undef if no command is associated with the query being sent. =item * sqlQuery SQL operation to perform on the slave. =back =cut sub sendQueryToSlaves($$) { my $seqId = $_[0]; my $sqlQuery = $_[1]; if($::slaveInfo->{"status"} == 0) { my $queryResult = $::slaveInfo->{"slaveConn"}->exec($sqlQuery); unless($queryResult->resultStatus == PGRES_COMMAND_OK) { my $errorMessage; $errorMessage = "Error sending query $seqId to " ; $errorMessage .= $::slaveInfo->{"slaveHost"}; $errorMessage .=$::slaveInfo->{"slaveConn"}->errorMessage; $errorMessage .= "\n" . $sqlQuery; logErrorMessage($errorMessage); $::slaveInfo->{"slaveConn"}->exec("ROLLBACK"); $::slaveInfo->{"status"} = -1; } } } =item logErrorMessage(error) Mails an error message to the users specified $errorEmailAddr The error message is also printed to STDERR. =over 4 =item * error The error message to log. =back =cut sub logErrorMessage($) { my $error = $_[0]; if(defined $lastErrorMsg and $error eq $lastErrorMsg) { if($repeatErrorCount<$::errorThreshold) { $repeatErrorCount++; warn($error); return; } } $repeatErrorCount=0; if(defined $::errorEmailAddr) { my $mailPipe; open (mailPipe, "|/bin/mail -s DBMirror.pl $::errorEmailAddr"); print mailPipe "=====================================================\n"; print mailPipe " DBMirror.pl \n"; print mailPipe "\n"; print mailPipe " The DBMirror.pl script has encountred an error. \n"; print mailPipe " It might indicate that either the master database has\n"; print mailPipe " gone down or that the connection to a slave database can\n"; print mailPipe " not be made. \n"; print mailPipe " Process-Id: $$ on $::masterHost database $::masterDb\n"; print mailPipe "\n"; print mailPipe $error; print mailPipe "\n\n\n=================================================\n"; close mailPipe; } warn($error); $lastErrorMsg = $error; } sub openSlaveConnection($) { my $slavePtr = $_[0]; my $slaveConn; my $slaveConnString = "host=" . $slavePtr->{"slaveHost"}; $slaveConnString .= " dbname=" . $slavePtr->{"slaveDb"}; $slaveConnString .= " port=" . $slavePtr->{"slavePort"}; $slaveConnString .= " user=" . $slavePtr->{"slaveUser"}; $slaveConnString .= " password=" . $slavePtr->{"slavePassword"}; $slaveConn = Pg::connectdb($slaveConnString); if($slaveConn->status != PGRES_CONNECTION_OK) { my $errorMessage = "Can't connect to slave database " ; $errorMessage .= $slavePtr->{"slaveHost"} . "\n"; $errorMessage .= $slaveConn->errorMessage; logErrorMessage($errorMessage); $slavePtr->{"status"} = -1; } else { $slavePtr->{"slaveConn"} = $slaveConn; $slavePtr->{"status"} = 0; #Determine the MirrorHostId for the slave from the master's database my $resultSet = $masterConn->exec('SELECT "MirrorHostId" FROM ' . ' "MirrorHost" WHERE "HostName"' . '=\'' . $slavePtr->{"slaveHost"} . '\''); if($resultSet->ntuples !=1) { my $errorMessage .= $slavePtr->{"slaveHost"} ."\n"; $errorMessage .= "Has no MirrorHost entry on master\n"; logErrorMessage($errorMessage); $slavePtr->{"status"}=-1; return; } $slavePtr->{"MirrorHostId"} = $resultSet->getvalue(0,0); } } =item updateMirrorHostTable(lastTransId,lastSeqId) Updates the MirroredTransaction table to reflect the fact that this transaction has been sent to the current slave. =over 4 =item * lastTransId The Transaction id for the last transaction that has been succesfully mirrored to the currently open slaves. =item * lastSeqId The Sequence Id of the last command that has been succefully mirrored =back =cut sub updateMirrorHostTable($$) { my $lastTransId = shift; my $lastSeqId = shift; if($::slaveInfo->{"status"}==0) { my $deleteTransactionQuery; my $deleteResult; my $updateMasterQuery = "INSERT INTO \"MirroredTransaction\" "; $updateMasterQuery .= " (\"XID\",\"LastSeqId\",\"MirrorHostId\")"; $updateMasterQuery .= " VALUES ($lastTransId,$lastSeqId,$::slaveInfo->{\"MirrorHostId\"}) "; my $updateResult = $masterConn->exec($updateMasterQuery); unless($updateResult->resultStatus == PGRES_COMMAND_OK) { my $errorMessage = $masterConn->errorMessage . "\n"; $errorMessage .= $updateMasterQuery; logErrorMessage($errorMessage); die; } # print "Updated slaves to transaction $lastTransId\n" ; # flush STDOUT; #If this transaction has now been mirrored to all mirror hosts #then it can be deleted. $deleteTransactionQuery = 'DELETE FROM "Pending" WHERE "XID"=' . $lastTransId . ' AND (SELECT COUNT(*) FROM "MirroredTransaction"' . ' WHERE "XID"=' . $lastTransId . ')=(SELECT COUNT(*) FROM' . ' "MirrorHost")'; $deleteResult = $masterConn->exec($deleteTransactionQuery); if($deleteResult->resultStatus!=PGRES_COMMAND_OK) { logErrorMessage($masterConn->errorMessage . "\n" . $deleteTransactionQuery); die; } } } sub extractData($$) { my $pendingResult = $_[0]; my $currentTuple = $_[1]; my $fnumber; my %valuesHash; $fnumber = 4; my $dataField = $pendingResult->getvalue($currentTuple,$fnumber); while(length($dataField)>0) { # Extract the field name that is surronded by double quotes $dataField =~ m/(\".*?\")/s; my $fieldName = $1; $dataField = substr $dataField ,length($fieldName); $fieldName =~ s/\"//g; #Remove the surronding " signs. if($dataField =~ m/(^= )/s) { #Matched null $dataField = substr $dataField , length($1); $valuesHash{$fieldName}=undef; } elsif ($dataField =~ m/(^=\')/s) { #Has data. my $value; $dataField = substr $dataField ,2; #Skip the =' LOOP: { #This is to allow us to use last from a do loop. #Recommended in perlsyn manpage. do { my $matchString; #Find the substring ending with the first ' or first \ $dataField =~ m/(.*?[\'\\])?/s; $matchString = $1; $value .= substr $matchString,0,length($matchString)-1; if($matchString =~ m/(\'$)/s) { # $1 runs to the end of the field value. $dataField = substr $dataField,length($matchString)+1; last; } else { #deal with the escape character. #It The character following the escape gets appended. $dataField = substr $dataField,length($matchString); $dataField =~ s/(^.)//s; $value .= $1; } } until(length($dataField)==0); } $valuesHash{$fieldName} = $value; }#else if else { logErrorMessage "Error in PendingData Sequence Id " . $pendingResult->getvalue($currentTuple,0); die; } } #while return %valuesHash; } === Date: Thu, 27 Feb 2003 14:56:33 -0500 (EST) From: Richard Welty <rwelty@averillpark.net> Subject: DBMirror mailing list (was Re: [GENERAL] 7.4?) To: PostgreSQL General <pgsql-general@postgresql.org> "Ed L." <pgsql@bluepolka.net> wrote: > I had little > success with rserv, but DBMirror worked ok. I'm enhancing DBMirror > up to work better for our environment. this actually brings up a question: is there currently a mailing list for DBMirror users? i'm about to start using it, and a place to discuss it with other users might be a useful subgroup. if not, i can certainly set one up on my server, although there might be more appropriate places for one to be set up (e.g., on postgresql.org) === From: "Ed L." <pgsql@bluepolka.net> To: Richard Welty <rwelty@averillpark.net>, Subject: Re: DBMirror mailing list (was Re: [GENERAL] 7.4?) Date: Thu, 27 Feb 2003 14:23:26 -0700 Richard Welty wrote: > > is there currently a mailing list for DBMirror users? i'm about to > start using it, and a place to discuss it with other users might be > a useful subgroup. No comment on a new mailing list, but I'm happy to collaborate if there is interest. The key changes in which I'm presently interested are: * master/slave port specification (done); * configurable sync delay (done); * "current-as-of-snapshot" described by Andrew Sullivan; * tools for easy setup and activation; * multi-slave; === Date: Thu, 27 Feb 2003 17:27:01 -0500 From: Andrew Sullivan <andrew@libertyrms.info> To: PostgreSQL General <pgsql-general@postgresql.org> Subject: Re: DBMirror mailing list (was Re: [GENERAL] 7.4?) Richard Welty wrote: > is there currently a mailing list for DBMirror users? i'm about to start There has been some discussion about it on the replication list. The replication list is pretty low-traffic, and it's a natural place to discuss it. === From: Bruce Momjian <pgman@candle.pha.pa.us> Subject: Re: [GENERAL] [ADMIN] Conditional row grained replication with DBMirror To: Achilleus Mantzios <achill@matrix.gatewaynet.com> Date: Sun, 10 Aug 2003 23:00:01 -0400 (EDT) Cc: ssinger@navtechinc.com, pgsql-admin@postgresql.org, Achilleus Mantzios wrote: > > > I made a modification on DBMirror.pl, > an addition in the slavedatabase.conf file, > and added another replication table "specialtables". > > The goal was to have a way of controlling > when a row of table (if the table is present in specialtables), > will be mirrored to the remote slave. > > Lets assume, we have a bank's central IT DEPT master DB > and some branches abroad. > > Rows in some tables (e.g. personel, branches, statistics on transactions, > etc...) > have no reason to be replicated to all sites, but only > those sites that these rows refer to: > > E.G. > we have > test=# \d personel > Table "public.personel" > Column | Type | Modifiers > ---------------+------------------------+----------- > id | integer | not null > name | character varying(20) | > branchid | integer | > test=# > > or > test=# \d branch > Table "public.personel" > Column | Type | Modifiers > ---------------+------------------------+----------- > id | integer | not null > country | character varying(20) | > address | text | > test=# > > And we require that insert, updates on table branch with id = 1 > go only to remote site (the ip of) branch 1, > and that inserts,updates on personel with branchid = 2, > in the same fashion, go only to (the ip of) branch 2. > > I retain the table specialtables > test=# \d specialtables > Table "public.specialtables" > Column | Type | Modifiers > ---------------+------------------------+----------- > tblname | character varying(100) | not null > siteidkeyname | character varying(20) | > Indexes: specialtables_pkey primary key btree (tblname) > > test=# > > in which for each conditional table, the corresponding > column name (the value of siteidkeyname) is given > which indicates against which column in the PendingData.Data > column is to be examined for equality against the site id. > > The site id for each slave is defined in an additional field > in slaveInfo struct in slavedatabase.conf. > > Please tell me if i should send the patches > to DBMirror.pl, MirrorSetup.sql, and slavedatabase.conf. Sure, send the patches to the patches list and we will get them applied. I am not sure if I can get them into 7.4. It depends if the dbmirror author can review them. === From: Bruce Momjian <pgman@candle.pha.pa.us> Subject: Re: [GENERAL] [ADMIN] Conditional row grained replication with DBMirror To: Achilleus Mantzios <achill@matrix.gatewaynet.com> Date: Fri, 26 Sep 2003 15:35:33 -0400 (EDT) Cc: ssinger@navtechinc.com, pgsql-admin@postgresql.org, Achilleus Mantzios wrote: > any news on applying the patches to dbmirror?? This has been saved for the 7.5 release: http:/momjian.postgresql.org/cgi-bin/pgpatches2 === From: "Simon Windsor" <simon.windsor@cornfield.org.uk> To: <pgsql-general@postgresql.org> Subject: [GENERAL] Replication options Date: Thu, 19 Feb 2004 18:24:26 -0000 I am used to using Oracle (15 years) and MySQL(5 years), but I am planning to move an existing application from MySQL to Postgres. The reasons are very simple, * New requirements means we need views, or a significant re-write * Better query/index performance essential. * Postgres and MySQL share a very common implementation of SQL-92, and what is missing can easily be implanted in functions The only negative issue is replication. I have checked several Postgres Replication options and unsure which way to go. Can anyone recommend a replication option that meets the following: * Does not use triggers. Usually slow, and one action that modifies several records, can trigger many actions on slaves/peers. * Does use WAL, or other log, so that SQL DDL/DML is copied to slave/peer, rather than the result of the DDL/DML. * Must provide master-master and master-slave replication * Simple to configure and maintain === Subject: Re: [GENERAL] Replication options Date: Fri, 20 Feb 2004 15:40:34 +1100 From: "Merrall, Graeme" <gmerrall@team.aol7.com.au> To: <pgsql-general@postgresql.org> The only negative issue is replication. I have checked several Postgres Replication options and unsure which way to go. Can anyone recommend a replication option that meets the following: * Does not use triggers. Usually slow, and one action that modifies several records, can trigger many actions on slaves/peers.=20 * Does use WAL, or other log, so that SQL DDL/DML is copied to slave/peer, rather than the result of the DDL/DML.=20 * Must provide master-master and master-slave replication=20 * Simple to configure and maintain=20 Is there any comparative analysis of the different pgsql replication systems out there? SO far I can think of erserver (free + payware), mammoth, dbbalancer, pgreplication. Have I missed any? If such a thing doesn't exist I could cobble few servers together and have a crack. === Date: Thu, 19 Feb 2004 12:06:29 -0800 From: "Joshua D. Drake" <jd@commandprompt.com> To: Andrew Rawnsley <ronz@ravensfield.com> Subject: Re: [GENERAL] Replication options >> Does not use triggers. Usually slow, and one action that >> modifies several records, can trigger many actions on slaves/peers. >> Does use WAL, or other log, so that SQL DDL/DML is copied to >> slave/peer, rather than the result of the DDL/DML. > I think only Mammoth's ships WAL logs. Josh? No we do not ship WAL logs. We ship our own transaction logs. One of the reasons we do this is so you can bring up new slaves live. We do not support replication of the SQL DDL but you do not need to stop the replication or stop the database to add tables to the replicated list. >> Must provide master-master and master-slave replication > I don't think any of the solutions will do master-master. Only that new clusgres thing from LinuxLabs but I would have to see it in production before I would get anywhere near it. === From: Andrew Rawnsley <ronz@ravensfield.com> Subject: Re: [GENERAL] Replication options Date: Thu, 19 Feb 2004 14:43:04 -0500 To: "Simon Windsor" <simon.windsor@cornfield.org.uk> Cc: <pgsql-general@postgresql.org> Simon Windsor wrote: > I am used to using Oracle (15 years) and MySQL(5 years), but I am > planning to move an existing application from MySQL to Postgres. The > reasons are very simple, > New requirements means we need views, or a significant re-write > Better query/index performance essential. > Postgres and MySQL share a very common implementation of SQL-92, > and what is missing can easily be implanted in functions > >