Slony1-1.1.0: UNSUBSCRIBE SET and SUBSCRIBE SET

From: Aldor <an(at)mediaroot(dot)de>
To: pgsql-admin(at)postgresql(dot)org
Subject: Slony1-1.1.0: UNSUBSCRIBE SET and SUBSCRIBE SET
Date: 2005-09-24 11:40:57
Message-ID: 43353B49.40303@mediaroot.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Browse pgsql-admin by date

  From Date Subject
Next Message Aldor 2005-09-24 11:47:25 Slony1-1.1.0: Triggers are making other things slower?
Previous Message Andy 2005-09-24 11:22:42 Re: PostgreSQL configuration problem