Using the Liquibase Percona Toolkit Extension

Percona Toolkit is a collection of command-line tools, which help to perform MySQL, MongoDB, and system tasks that are too difficult or complex to perform manually.

There is a Liquibase extension that supports the Percona Toolkit pt-online-schema-change. This extension replaces some default changes to use pt-online-schema-change instead of SQL. The pt-online-schema-change tool will allow you to perform a database upgrade without locking tables.

Currently, MySQL is the only supported database. The liquibase-percona extension checks whether you run it against a MySQL database, and if your database is not MySQL, the extension falls back to the default changes provided by Liquibase.

Installing the Liquibase Percona Toolkit extension

To use the Liquibase Percona Toolkit extension, follow these steps:

  1. Ensure that you have installed the latest version of Liquibase. If you use earlier versions of Liquibase, you need to have a compatible version of the extension.

    2. Note: For more information about installation or upgrade, see Installing Liquibase.

  1. Download the .jar file from the liquibase-percona repository.
  2. Locate your liquibase-percona-<version>.jar file:
  • CLI

    • After extracting the .zip file of Liquibase, place liquibase-percona-<version>.jar file in the lib subdirectory. The shell script liquibase / liquibase.bat will automatically pick up the .jar file, and the extension will be available to use.

  • Maven

    • Add the following dependency to the Liquibase plugin:

    <dependency>
<groupId>org.liquibase.ext</groupId>
<artifactId>liquibase-percona</artifactId>
<version>4.3.0</version>
</dependency>

    After adding the dependency, you can start using the Liquibase Percona Toolkit extension.

    You can download the latest snapshot version for the liquibase-percona dependency on the liquibase-percona releases page and add it to your Maven configuration file. Snapshot builds contain the latest features. 4.3.1-SNAPSHOT example:

    <dependency>
<groupId>org.liquibase.ext</groupId>
<artifactId>liquibase-percona</artifactId>
<version>4.3.1-SNAPSHOT</version>
</dependency>

Configuring the Liquibase Percona Toolkit extension

You can use flags and system properties to control the use of Percona Toolkit when performing updates for your database.

Flags

To use Percona Toolkit flags, add the needed flag to your changelog:

  • usePercona

Note: Since liquibase-percona 1.3.0.

Each change allows you to enable or disable the use of Percona Toolkit by adding the usePercona flag. For more information, see the liquibase.percona.defaultOn system property.

YAML example:

- changeSet:
    id: 2
    author: Alice
    changes:
      - addColumn:
          tableName: person
          usePercona: false
          columns:
            - column:
                name: address
                type: varchar(255)

The flag is supported in a YAML format. Since Liquibase 3.6.0, you can use it in an XML format:

<addColumn tableName="person"
xmlns:liquibasePercona="http://www.liquibase.org/xml/ns/dbchangelog-ext/liquibase-percona"
    liquibasePercona:usePercona="false">
  <column name="address" type="varchar(255)"/>
</addColumn>
  • perconaOptions

Note: Since liquibase-percona 2.0.0.

Each change allows you to specify options that are used when executing pt-online-schema-change. If you specify the perconaOptions flag, it will override the liquibase.percona.options system property. If you don’t specify the perconaOptions flag, then the system property will be used.

YAML example:

- changeSet:
     id: 2
     author: Alice
     changes:
       - addColumn:
           tableName: person
           perconaOptions: "--alter-foreign-keys-method=auto"
           columns:
             - column:
                 name: address
                 type: varchar(255)

XML example:

<addColumn tableName="person"
xmlns:liquibasePercona="http://www.liquibase.org/xml/ns/dbchangelog-ext/liquibase-percona"
   liquibasePercona:perconaOptions="--alter-foreign-keys-method=auto">
  <column name="address" type="varchar(255)"/>
</addColumn>

System properties

You can set system properties by using the standard java -D option:

java -Dliquibase.percona.skipChanges=createIndex,dropColumn -jar liquibase.jar ...

Also, to set the system property, you need to specify a Liquibase .jar file and ensure that the liquibase-percona.jar file is on the classpath (the --classpath option).

When executing Liquibase through Maven, you can use the Properties Maven Plugin to set the system property.

Property Description
liquibase.percona.failIfNoPT: true/false Allows you to enforce the use of Percona Toolkit. If the property is set to true and the command pt-online-schema-change is not found, the database update will fail. The default value: false.
liquibase.percona.noAlterSqlDryMode: true/false Allows you to generate SQL statements and puts them into the migration file. When running update-sql or rollback-sql to generate a migration SQL file, the command line that is executed will be added as a comment. Additionally, the SQL statements will be generated and put into the migration file. This allows executing the generated migration SQL to perform an update; however, Percona Toolkit will not be used. If the liquibase.percona.noAlterSqlDryMode property is set to true, then no SQL statements will be put into the migration file. The default value: false.
liquibase.percona.skipChanges: a comma-separated list of changes Allows you to disable one or more changes. If a change is disabled when using Percona Toolkit, then this change will be executed by the default Liquibase implementation, and Percona Toolkit will not be used. By default, the liquibase.percona.skipChanges property is empty so that all supported changes are executed using Percona Toolkit. For example, if you don’t want to use Persona Toolkit for adding or dropping a column, set the property to addColumn, dropColumn.
liquibase.percona.options: string of options Allows you to pass additional command line options to pt-online-schema-change. For example, you can use this property in a complicated replication setup to change the way subordinate instances (also known as slaves) are detected and how their state is used. You can also specify a Percona configuration file using --config file.conf (See Configuration Files). Multiple options are separated by space. If an argument contains a space, use double quotation marks: --config "filename with spaces.conf".
Since liquibase-percona 1.2.1, the default value is --alter-foreign-keys-method=auto --nocheck-unique-key-change. The default value is changed starting with liquibase-percona 1.6.0.
liquibase.percona.defaultOn: true/false Allows you to change the default behavior for the usePercona flag. By default, all changes that are not specified with the usePercona flag use the value from this system property. Setting the liquibase.percona.defaultOn property to false allows controlling for each change whether to use Percona Toolkit. The default value: true. Since liquibase-percona 1.3.0.
liquibase.password: string with the password needed to connect to the database Allows you to shortcut the automatic detection of the password from the underlying java.sql.Connection (if it fails) or from the default Liquibase properties file. If the liquibase.password property is set, then it is used for the password when executing pt-online-schema-change. The default value: <empty>. Since liquibase-percona 1.4.0.
liquibase.percona.path: path to the Percona Toolkit directory where the tool pt-online-schema-change is installed Allows you to select a specific Percona Toolkit installation. If this property is not set, then Percona Toolkit will be searched on the PATH. You need to specify the bin subfolder of the Percona Toolkit distribution. The default value: <empty>. Since liquibase-percona 1.4.1.
liquibase.percona.ptdebug: true/false Allows you to enable the debug output of pt-online-schema-change by setting the environment variable PTDEBUG before starting pt-online-schema-change. The default value: false. Since liquibase-percona 1.5.0.

For more information about Liquibase and Percona Toolkit changes for each version, see the Changelog section.

Related Links