|
Home > Archive > Slony1 PostgreSQL Replication > September 2005 > Slony1-1.1.0: UNSUBSCRIBE SET and SUBSCRIBE SET
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Slony1-1.1.0: UNSUBSCRIBE SET and SUBSCRIBE SET
|
|
|
| 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=$MASTERDBNAM
E host=$MASTERHOST
port=$MASTERPORT user=$REPLICATIONUSE
R password=$MASTERPASS
';
node 2 admin conninfo = 'dbname=$SLAVE1DBNAM
E host=$SLAVE1HOST
port=$SLAVE1PORT user=$REPLICATIONUSE
R password=$SLAVE1PASS
';
init cluster ( id=1, comment = $CLUSTERNAME);
create set (id=1, origin=1, comment=$CLUSTERTABL
E1);
set add table (set id=1, origin=1, id=1, fully qualified name =
'public.[table]', comment=$CLUSTERTABL
E1);
--- CONFIG END ---
For the master table I use for starting the replication:
slon $CLUSTERNAME " dbname=$MASTERDBNAME
user=$REPLICATIONUSE
R
host=$MASTERHOST port=$MASTERPORT password=$MASTERPASS
"
For the slave table I use for starting the replication:
slon $CLUSTERNAME " dbname=$SLAVE1DBNAME
user=$REPLICATIONUSE
R
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=$MASTERDBNAM
E host=$MASTERHOST
port=$MASTERPORT user=$REPLICATIONUSE
R password=$MASTERPASS
';
node 2 admin conninfo = 'dbname=$SLAVE1DBNAM
E host=$SLAVE1HOST
port=$SLAVE1PORT user=$REPLICATIONUSE
R 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=$MASTERDBNAM
E host=$MASTERHOST
port=$MASTERPORT user=$REPLICATIONUSE
R password=$MASTERPASS
';
node 2 admin conninfo = 'dbname=$SLAVE1DBNAM
E host=$SLAVE1HOST
port=$SLAVE1PORT user=$REPLICATIONUSE
R 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=$MASTERDBNAM
E host=$MASTERHOST
port=$MASTERPORT user=$REPLICATIONUSE
R password=$MASTERPASS
';
node 2 admin conninfo = 'dbname=$SLAVE1DBNAM
E host=$SLAVE1HOST
port=$SLAVE1PORT user=$REPLICATIONUSE
R 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
| |
| Christopher Browne 2005-09-24, 8:24 pm |
| Aldor <an- 3jXkk0SXEtUhFhg+JK9F
0w@public.gmane.org> 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=$MASTERDBNAM
E host=$MASTERHOST
> port=$MASTERPORT user=$REPLICATIONUSE
R password=$MASTERPASS
';
> node 2 admin conninfo = 'dbname=$SLAVE1DBNAM
E host=$SLAVE1HOST
> port=$SLAVE1PORT user=$REPLICATIONUSE
R password=$SLAVE1PASS
';
>
> init cluster ( id=1, comment = $CLUSTERNAME);
>
> create set (id=1, origin=1, comment=$CLUSTERTABL
E1);
> set add table (set id=1, origin=1, id=1, fully qualified name =
> 'public.[table]', comment=$CLUSTERTABL
E1);
>
> --- CONFIG END ---
>
> For the master table I use for starting the replication:
>
> slon $CLUSTERNAME " dbname=$MASTERDBNAME
user=$REPLICATIONUSE
R
> host=$MASTERHOST port=$MASTERPORT password=$MASTERPASS
"
>
> For the slave table I use for starting the replication:
>
> slon $CLUSTERNAME " dbname=$SLAVE1DBNAME
user=$REPLICATIONUSE
R
> 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=$MASTERDBNAM
E host=$MASTERHOST
> port=$MASTERPORT user=$REPLICATIONUSE
R password=$MASTERPASS
';
> node 2 admin conninfo = 'dbname=$SLAVE1DBNAM
E host=$SLAVE1HOST
> port=$SLAVE1PORT user=$REPLICATIONUSE
R 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=$MASTERDBNAM
E host=$MASTERHOST
> port=$MASTERPORT user=$REPLICATIONUSE
R password=$MASTERPASS
';
> node 2 admin conninfo = 'dbname=$SLAVE1DBNAM
E host=$SLAVE1HOST
> port=$SLAVE1PORT user=$REPLICATIONUSE
R 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=$MASTERDBNAM
E host=$MASTERHOST
> port=$MASTERPORT user=$REPLICATIONUSE
R password=$MASTERPASS
';
> node 2 admin conninfo = 'dbname=$SLAVE1DBNAM
E host=$SLAVE1HOST
> port=$SLAVE1PORT user=$REPLICATIONUSE
R 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)
| |
|
| Hi Christopher,
okay, now I understand - I was sure I was making something wrong;-)
Thanks a lot!
Christopher Browne wrote:
> Aldor <an- 3jXkk0SXEtUhFhg+JK9F
0w@public.gmane.org> writes:
>
>
>
> That doesn't "pause" replication; that terminates replication for that
> set.
>
>
>
>
> 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.
|
|
|
|
|