slony学习笔记(从复制系统中删除表)

http://slony.info/documentation/dropthings.html

13.4. Dropping A Table From Replication
          从复制系统中删除一个表

13.4.1. Using the altperl tools

If the altperl utilities are installed, you can use the slonik_drop_table helper script to drop a table from replication. Simply run slonik_drop_table with no arguments to review the correct usage of the script. After dropping the table, you should also remove it from slon_tools.conf.

13.4.2. Using Direct slonik commands

In Slony-I 1.0.5 and above, there is a Slonik command SET DROP TABLE that allows dropping a single table from replication without forcing the user to drop the entire replication set.

If you are running an earlier version, there is a "hack" to do this:

You can fiddle this by hand by finding the table ID for the table you want to get rid of, which you can find in sl_table, and then run the following three queries, on each host:

select _slonyschema.alterTableRestore(40);
select _slonyschema.tableDropKey(40);
delete from _slonyschema.sl_table where tab_id = 40;

 

The schema will obviously depend on how you defined the Slony-I cluster. The table ID, in this case, 40, will need to change to the ID of the table you want to have go away.

You'll have to run these three queries on all of the nodes, preferably firstly on the origin node, so that the dropping of this propagates properly. Implementing this via a slonik statement with a new Slony-I event would do that. Submitting the three queries using EXECUTE SCRIPT could do that; see Section 15 for more details. Also possible would be to connect to each database and submit the queries by hand.

先在HOST_MASTER执行delete操作
然后在HOST_SLAVE1，HOST_SLAVE2等执行同样操作


列出复制系统中表
select * from  _sina.sl_table order by tab_id;

删除tab_id>=251的表
delete from _sina.sl_table where tab_id >= 251;

 

13.5. Dropping A Sequence From Replication
          从复制系统中删除Sequence

Just as with SET DROP TABLE, version 1.0.5 introduces the operation SET DROP SEQUENCE.

If you are running an earlier version, here are instructions as to how to drop sequences:

The data that needs to be deleted to stop Slony-I from continuing to replicate the two sequences identified with Sequence IDs 93 and 59 are thus:

delete from _oxrsorg.sl_seqlog where seql_seqid in (93, 59);
delete from _oxrsorg.sl_sequence where seq_id in (93,59);

 

Those two queries could be submitted to all of the nodes via schemadocddlscript_complete( integer, text, integer ) / EXECUTE SCRIPT, thus eliminating the sequence everywhere "at once." Or they may be applied by hand to each of the nodes.

select * from  _sina.sl_sequence order by seq_id;

delete from _sina.sl_sequence where seq_id >= 252;

------------

select * from  _sina.sl_seqlog order by seql_seqid;

delete from  _sina.sl_seqlog where seql_seqid >= 252;

 

表：test
tab_id=1
delete from _sina.sl_table where tab_id = 1;

select _sina.alterTableRestore(1);
select _sina.tableDropKey(1);
delete from _sina.sl_table where tab_id = 1;

注意：NAME_CLUSTER=sina

其他异常处理
<stdin>:8: PGRES_FATAL_ERROR select "_sina".setAddTable(2, 253, 'public.ad_list', 'ad_list_pkey', 'public.ad_list'); - ERROR: trigger "_sina_logtrigger_253" for relation "ad_list" already exists
处理方法：
DROP TRIGGER _sina_logtrigger_253 on ad_list;