3

I'm investigating using pt-online-schema-change to assist in certain migrations where an ALTER TABLE command would cause a long maintenance window. I understand that pt-online-schema-change will create an empty copy of the table to perform the ALTER TABLE command on, then migrate rows over in batches from the old table to the new, and create triggers to manage data changes in the interim.

But at the moment when the new table is swapped with the old, is it possible to pause at that point so that we can time it with a new codebase deploy? I don't see this addressed in the documentation. Obviously our PHP ORM (doctrine) of our new release (using Symfony) will be expecting a certain schema to be in place and will cause problems if the swap happens either before or after the codebase deploy.

A related question is I understand foreign key constraints have to be updated on all child tables because otherwise they will still reference the old table. Does that mean this stage has be done behind a maintenance window? I don't see how you could do that ahead of time if we are timing the data migrations to coincide with the release of a particular codebase.

asolberg
  • 6,638
  • 9
  • 33
  • 46
  • As-written, no. I highly doubt that `pt-online-schema-change` will halt at an arbitrary point. That said, it's a Perl script. You can simply make the modification yourself and have it halt at whatever point you wish. – Sammitch Aug 15 '14 at 18:17
  • I'm thinking we must be missing something here. Whats the best practice? Does everybody re-invent they own custom plugin to do the same thing that 90% of web applications will require? I'm thinking probably not. – asolberg Aug 15 '14 at 18:23

2 Answers2

4

You can write code for the pause logic into a plugin that runs before_swap_tables.

For example (not tested, but this should give you the basic idea):

package pt_online_schema_change_plugin

sub new() {
    open HANDLE, ">>pause_please.txt" or die "$!";
    close HANDLE;
}

sub before_swap_tables() {
    print "Pausing until you rm pause_please.txt...\n";
    while (-f "pause_please.txt") {
      sleep(1);
    }
}

Then when you're ready, open another shell window to rm the file, and then pt-online-schema-change will continue.

See the "PLUGIN" section in the documentation for details.

Re your comment:

It might seem like everyone must have the problem you are facing at the current moment. But you are the first person I've heard ask for this feature.

Most people write code that can read and write the tables before and after the schema change. For example if you're adding a column, avoid SELECT * and INSERT ... VALUES() statements, so it doesn't surprise the app when a new column appears. So it's not necessary to time code pushes with schema changes.

But sometimes the nature of the schema change needs a code change. For example, renaming a column. For these cases when that is necessary, a few minutes of downtime is acceptable.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • So does everybody re-invent they own custom plugin to do the same thing that 90% of web applications will require? I'm thinking probably not. – asolberg Aug 15 '14 at 18:24
  • ok thanks, I appreciate the words of wisdom and the code snippet – asolberg Aug 15 '14 at 18:41
0

as of version pt-online-schema-change 2.2.20 there is option --pause-file documentation however this will only update schema and wait with copying data until file is deleted.

I have created this plugin that will copy data aswell.

Marcin Wasiluk
  • 4,675
  • 3
  • 37
  • 45