can pt-online-schema-change pause after copy but before table rename

Polyglot Projects Percona Toolkit
1

We have a scenario where pt-online-schema-change is causing issues with our downstream ETL tool (Attunity) happening when the new table gets renamed to the old.

Is it possible to have pt-online-schema-change copy all the data (including all incoming changes) into the new altered table and then pause (and still process incoming records into both the original and the new table). That would allow us to stop our always-on ETL tool. Then manually trigger pt-online-schema-change to do the table rename (without losing any records after the pause).

Here’s the command we are currently using for pt-online-schema-change if it provides insight.

pt-online-schema-change
–nocheck-replication-filters
–recursion-method=“dsn=D=dba,t=dsn”
–max-load Threads_running=150
–critical-load Threads_running=300
–execute
–alter “DROP FOREIGN KEY __fk_tradeline_comp_client_id,
DROP FOREIGN KEY __fk_tradeline_comp_eq_id,
DROP FOREIGN KEY __fk_tradeline_comp_ex_id,
DROP FOREIGN KEY __fk_tradeline_comp_tu_id,
MODIFY tu_reportitemserialid BIGINT UNSIGNED DEFAULT NULL,
MODIFY eq_reportitemserialid BIGINT UNSIGNED DEFAULT NULL,
MODIFY ex_reportitemserialid BIGINT UNSIGNED DEFAULT NULL”
h=localhost,D=SQL_AuditPro,t=tradeline_comp

2

Hi,

You can use the pause file: [URL]pt-online-schema-change — Percona Toolkit Documentation

Regards

3

Thanks Carlos. I’m assuming this will pause the entire execution of [LEFT][COLOR=#252C2F]pt-online-schema-change, even during the copy phase?

Would there be a way to generate this pause file once the copy phase completes or would someone have to manually watch and create the file?[/LEFT]

4

Hello again,

Yes, it is possible. At lines 9847~9850 in pt-online-schema-change, it calls plugins hooks before swapping tables.

The code looks like this:

# --plugin hook
if ( $plugin && $plugin->can('before_swap_tables') ) {
$plugin->before_swap_tables();
}

so, you could write a custom plugin that does anything you need before swapping the tables, like signaling Attunity to stop.
I hope it helps.

Regards

5

Exactly what I need! Thanks again Carlos