Glyn Astill <[email protected]> writes:
> We have a replicated table that stores errors from an application that
> recently went mad and logged about 650000 records each about 75kb in size.
>
> Our local replication is fine, but we have servers on the end of a vpn that
> are now lagging massively.
>
> If I drop the table from replication will the entries in sl_log be cleared
> out and replication cease for that table - or will the drop wait until the
> tables are in sync?
SET DROP TABLE does not clear out the entries from sl_log_*, so you'd
find that the DROP would wait until the tables get into sync :-(.
I suppose that what you could do, if you plan to nuke the table, is to
delete those records from sl_log_* by hand.
That is... If the table's ID is 17...
delete from _my_schema.sl_log_1 where log_tableid = 17;
delete from _my_schema.sl_log_2 where log_tableid = 17;
I think I'd want to order it thus...
0. Back up the table some place where it contains the data that you think it
ought to have
e.g. - use "pg_dump -t my_log_table > my_log_table.sql" to
preserve you from losing contents.
1. Slonik script to drop table #17
2. delete from sl_log_* against all the nodes
3. Replication should then fairly quickly catch up.
4. You probably want to induce log rotations everywhere because
sl_log_* will be pretty bloated after all this.
select _my_schema.logswitch_start()
wait 10-ish minutes for the log switch to complete
Do it again...
select _my_schema.logswitch_start()
Be very very careful about step #2, that you're deleting the right
data, as picking the wrong table could rather ruin your day! I'd not
call this "notably safe," but it's not heinously dangerous.
--
"cbbrowne","@","ca.afilias.info"
Christopher Browne
"Bother," said Pooh, "Eeyore, ready two photon torpedoes and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
