From: Aldor <an@mediaroot.de>
To: pgsql-admin@postgresql.org
Subject: Slony1-1.1.0: UNSUBSCRIBE SET and SUBSCRIBE SET
Date: Sat, 24 Sep 2005 12:40:57 +0100
Message-ID: <43353B49.40303@mediaroot.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: an@mediaroot.de
Subject: Re: Slony1-1.1.0: UNSUBSCRIBE SET and SUBSCRIBE SET
In-Reply-To: <43353B49.40303@mediaroot.de>
References: <43353B49.40303@mediaroot.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox