How do I Drop Table from slony

Question

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:

1. Recreate the table
2. Get the OID for the recreated table: SELECT OID from pg_class WHERE relname = <your_table>' AND relkind = 'r';
3. Update the tab_reloid in sl_table for the problem table.
4. Execute SET DROP TABLE ( ORIGIN = N, ID = ZZZ); where N is the NODE # for the MASTER, and ZZZ is the ID # in sl_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?

Answer 1

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:

ERROR:  "table_pkey" is an index

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.

Answer 2

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