> From: Christopher Browne <cbbrowne at ca.afilias.info>
> Subject: Re: [Slony1-general] Dropping table with lots of entries in sl_log
> To: "Glyn Astill" <glynastill at yahoo.co.uk>
> Cc: slony1-general at lists.slony.info
> Date: Friday, 2 July, 2010, 18:55
> Glyn Astill <glynastill at yahoo.co.uk>
> 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.

Thanks Chris,

In the end I just took the laissez-faire option, and everything caught up nicely in a much shorter space of time than I thought it would.

Nice to know for the future though.

Glyn