Hi Christopher,

okay, now I understand - I was sure I was making something wrong;-)

Thanks a lot!

Christopher Browne wrote:
> Aldor <an at mediaroot.de> writes:
> 
>>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 ---
> 
> 
> That doesn't "pause" replication; that terminates replication for that
> set.
> 
> 
>>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?
> 
> 
> Maintenance should be done via the EXECUTE SCRIPT facility if you want
> that sort of behaviour.
> 
> What you're doing instead is to terminate and restart (from scratch)
> replication of the set.