X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 065B9D6F35 for ; Sat, 24 Sep 2005 08:40:52 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 98924-10 for ; Sat, 24 Sep 2005 11:40:48 +0000 (GMT) Received: from 217-20-115-93.internetserviceteam.com (217-20-115-93.internetserviceteam.com [217.20.115.93]) by svr1.postgresql.org (Postfix) with ESMTP id 305E1D6EB0 for ; Sat, 24 Sep 2005 08:40:49 -0300 (ADT) Received: from [192.168.1.48] (103.Red-81-44-2.dynamicIP.rima-tde.net [81.44.2.103]) by 217-20-115-93.internetserviceteam.com (Postfix) with ESMTP id B1B1339BF3 for ; Sat, 24 Sep 2005 12:56:55 +0200 (CEST) Message-ID: <43353B49.40303@mediaroot.de> Date: Sat, 24 Sep 2005 12:40:57 +0100 From: Aldor User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Slony1-1.1.0: UNSUBSCRIBE SET and SUBSCRIBE SET Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.103 required=5 tests=[AWL=-0.103, UPPERCASE_25_50=0.207] X-Spam-Level: X-Archive-Number: 200509/281 X-Sequence-Number: 19018 Hi, I have a master database and a slave database replicated with Slony. The config of the cluster: --- CONFIG START --- cluster name = $CLUSTERNAME; node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST port=$MASTERPORT user=$REPLICATIONUSER password=$MASTERPASS'; node 2 admin conninfo = 'dbname=$SLAVE1DBNAME host=$SLAVE1HOST port=$SLAVE1PORT user=$REPLICATIONUSER password=$SLAVE1PASS'; init cluster ( id=1, comment = $CLUSTERNAME); create set (id=1, origin=1, comment=$CLUSTERTABLE1); set add table (set id=1, origin=1, id=1, fully qualified name = 'public.[table]', comment=$CLUSTERTABLE1); --- CONFIG END --- For the master table I use for starting the replication: slon $CLUSTERNAME "dbname=$MASTERDBNAME user=$REPLICATIONUSER host=$MASTERHOST port=$MASTERPORT password=$MASTERPASS" For the slave table I use for starting the replication: slon $CLUSTERNAME "dbname=$SLAVE1DBNAME user=$REPLICATIONUSER host=$SLAVE1HOST port=$SLAVE1PORT password=$SLAVE1PASS" Then I have a script which starts the replication: --- SCRIPT START --- cluster name = $CLUSTERNAME; node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST port=$MASTERPORT user=$REPLICATIONUSER password=$MASTERPASS'; node 2 admin conninfo = 'dbname=$SLAVE1DBNAME host=$SLAVE1HOST port=$SLAVE1PORT user=$REPLICATIONUSER password=$SLAVE1PASS'; subscribe set ( id = 1, provider = 1, receiver = 2, forward = no); --- SCRIPT STOP --- When I have to do any maintenance work on the table, I do them on the master database. Before starting maintenance work on the data of that table I pause the replication of this set by: --- SCRIPT START --- cluster name = $CLUSTERNAME; node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST port=$MASTERPORT user=$REPLICATIONUSER password=$MASTERPASS'; node 2 admin conninfo = 'dbname=$SLAVE1DBNAME host=$SLAVE1HOST port=$SLAVE1PORT user=$REPLICATIONUSER password=$SLAVE1PASS'; unsubscribe set ( id = 1, receiver = 2); --- SCRIPT STOP --- When I have finished the maintenance work on the data of that table I start again replication by: --- SCRIPT START --- cluster name = $CLUSTERNAME; node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST port=$MASTERPORT user=$REPLICATIONUSER password=$MASTERPASS'; node 2 admin conninfo = 'dbname=$SLAVE1DBNAME host=$SLAVE1HOST port=$SLAVE1PORT user=$REPLICATIONUSER password=$SLAVE1PASS'; subscribe set ( id = 1, provider = 1, receiver = 2, forward = no); --- SCRIPT STOP --- Usually the replication should only transfer the transactions which are buffered, but instead of doing it - it does on the slave database: select "[clustername]".truncateTable('"public"."[table]"'); copy "public"."[table]" from stdin; (noticed in pg_stat_activity) I don't want that it truncates the whole table and then put in all data again by COPY, I want that it only performs the buffered transactions which were made in the meantime on the master database. What do I have to do to get this type of behavior? Thanks, Aldor