如何从 slony 中删除表
我有一个由 slony 备份的数据库。我从复制的数据库中删除了一个表，并使用 sql 脚本重新创建了同一个表，而没有通过 slony 脚本执行任何操作。
我在一篇文章中找到了这个并尝试了它：
- 重新创建表
- 获取重新创建表的 OID：
从 pg_class WHERE relname =' 中选择 OID AND relkind = 'r'; - 更新问题表的 sl_table 中的 tab_reloid。
- 执行
SET DROP TABLE ( ORIGIN = N, ID = ZZZ);，其中N是 MASTER 的 NODE #，ZZZ 是sl_table< 中的 ID # /代码>。
但这似乎不起作用。
如何从复制数据库中删除表？或者有没有办法使用新创建的表代替旧表？
I have a database which is being backed up by slony. I dropped a table from the replicated DB and re-created the same table using sql scripts and nothing through slony scripts.
I found this on a post and tried it:
- Recreate the table
- Get the OID for the recreated table:
SELECT OID from pg_class WHERE relname = <your_table>' AND relkind = 'r'; - Update the tab_reloid in sl_table for the problem table.
- Execute
SET DROP TABLE ( ORIGIN = N, ID = ZZZ);whereNis the NODE # for the MASTER, and ZZZ is the ID # insl_table.
But it doesn't seem to work.
How do I drop the table from the replicated DB? Or is there a way to use the newly created table in place of the old one?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
有关从 Slony 删除内容的权威文档位于此处。
在运行上面的命令之前，并不清楚事情处于什么状态，并且您还没有澄清“似乎不起作用”。
我知道，使用 Slony 从复制中删除表有一个重要的“陷阱”。从复制中删除表后，您可能会在使用 Slony 1.2 实际物理删除从属服务器上的表（但不是主服务器上的表）时遇到麻烦，并出现如下神秘错误：
This might be returned in Slony 2.0, but the Problem here问题在于从属设备上的未复制表和复制表之间存在引用完整性关系，而 slony 1.2 在设计时故意损坏了系统表，从而导致了此问题。
解决方案是通过 slonik_execute_script 运行“DROP TABLE”命令。如果您已经在主服务器上物理删除了表，则可以使用选项“EXECUTE ONLY ON”仅在特定从服务器上运行该命令。有关详细信息，请参阅 EXECUTE SCRIPT 的文档。
The authoritative documentation on dropping things from Slony is here.
It's not really clear what state things were in before you ran the commands above, and you haven't clarified "doesn't seem to work".
There is one significant "gotcha" that I know off with dropping tables from replication with Slony. After you remove a table from replication, you can have trouble actually physically dropping the table on the slaves (but not on the master) with Slony 1.2, getting a cryptic error like this:
This may be fixed in Slony 2.0, but the problem here is that there is a referential integrity relationship between the unreplicated table on the slave and the replicated table, and slony 1.2 has intentionally corrupted the system table some as part of it's design, causing this issue.
A solution is to run the "DROP TABLE" command through slonik_execute_script. If you have already physically dropped the table on the master, you can use the option "EXECUTE ONLY ON" to run the command only on a specific slave. See the docs for EXECUTE SCRIPT for details.
您已从数据库中删除该表，但尚未从 _YOURCLUSTERNAME.sl_table 中删除。
YOURCLUSTERNAME 前面的“_”很重要。
解决混乱的 4 个步骤：
1.获取 tab_id
select tab_id from _YOURCLUSTERNAME.sl_table where tab_relname='MYTABLENAME' and tab_nspname='MYSCHEMANAME'
它返回 MYDATABASE 2 中的数字 2
。删除触发器
select _YOURCLUSTERNAME.altertablerestore(2);
这可能会返回错误。因为它试图删除原始表中的触发器，而现在有一个新的触发器。
3.如果创建了 slony 索引，则删除
select _YOURCLUSTERNAME.tableDropKey(2);
这可能会返回错误。
因为它试图删除原始表中的索引，而现在有一个新表。
4.从 sl_table 中删除表
delete from _YOURCLUSTERNAME.sl_table where tab_id = 2;
删除表格的最佳方法是：
1.从集群中删除表：
select tab_id from _YOURCLUSTERNAME.sl_table where tab_relname='MYTABLENAME' and tab_nspname='MYSCHEMANAME'
它在 MYDATABASE 中返回数字 2
用 slonik <执行myfile.slonik
其中 myfile.slonik 是：
集群名称=MYCLUSTER；
节点 1 ADMIN CONNINFO = 'dbname=DATABASENAME 主机=HOST1_MASTER 用户=postgres 端口=5432';
节点 2 ADMIN CONNINFO = 'dbname=DATABASENAME 主机=HOST2_SLAVE 用户=postgres 端口=5432';
设置删除表（id = 2，origin = 1）；
2 是 sl_table 中的 tab_id，1 是 NODE 1、HOST1_MASTER
2。 从从服务器删除表
使用 SQL DROP TABLE
you have dropped the table from the database but you haven't dropped from the _YOURCLUSTERNAME.sl_table.
It's importatnt de "_" before YOURCLUSTERNAME.
4 STEPS to solve the mess:
1. Get the tab_id
select tab_id from _YOURCLUSTERNAME.sl_table where tab_relname='MYTABLENAME' and tab_nspname='MYSCHEMANAME'
It returna a number 2 in MYDATABASE
2. Delete triggers
select _YOURCLUSTERNAME.altertablerestore(2);
This can return an error. Because It's trying to delete triggers in the original table, and now there is a new one.
3. Delete slony index if were created
select _YOURCLUSTERNAME.tableDropKey(2);
This can return an error.
Because It's trying to delete a index in the original table, and now there is a new table.
4. Delete the table from sl_table
delete from _YOURCLUSTERNAME.sl_table where tab_id = 2;
The best way for dropping a table is:
1. Drop the table form the cluster:
select tab_id from _YOURCLUSTERNAME.sl_table where tab_relname='MYTABLENAME' and tab_nspname='MYSCHEMANAME'
It returna a number 2 in MYDATABASE
Execute with slonik < myfile.slonik
where myfile.slonik is:
cluster name=MYCLUSTER;
NODE 1 ADMIN CONNINFO = 'dbname=DATABASENAME host=HOST1_MASTER user=postgres port=5432';
NODE 2 ADMIN CONNINFO = 'dbname=DATABASENAME host=HOST2_SLAVE user=postgres port=5432';
SET DROP TABLE (id = 2, origin = 1);
2 is the tab_id from sl_table and 1 is NODE 1, HOST1_MASTER
2. Drop the table from slave
with SQL DROP TABLE