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.
-- 
let name="cbbrowne" and tld="ca.afilias.info" in name ^ "@" ^ tld;;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)