Prepare and Run Flyway

March 26, 2020

Configure the tables for running the Flyway data migration tool on the first Analytics node .

  1. Baseline the Flyway schema table.

    1. Clean out the /var/tmp/vertica-migrations folder.

      rm -rf /var/tmp/vertica-migrations/*

    2. Copy the new schema files into the vertica-migrations folder.

      cp /opt/msys/app/db/schema/* /var/tmp/vertica-migrations/

    3. Truncate the existing schema_version table.

      /opt/vertica/bin/vsql -U vertica_dba -c "TRUNCATE TABLE momo.schema_version;"

    4. Run the Flyway baseline, which adds a << BASELINE >> record to the table.

      /opt/msys/app/db/flyway baseline

    5. Update the baseline record to point to the current date formatted like the Flyway migration versions we use.

      Note

      The version should be formatted YYYY.MM.DD.HH.MM.SS.

      /opt/vertica/bin/vsql -U vertica_dba -c "UPDATE momo.schema_version SET version='2014.09.20.00.00.00' \
         WHERE version=1; COMMIT;"
      
  2. Run the Flyway data migration tool.

    /opt/msys/app/db/flyway migrate -locations=filesystem:/var/tmp/vertica-migrations

    Note

    Flyway will run a series of scripts, after which you may receive the warning Error Code: 6100. This warning is about best practices from Vertica and can safely be ignored as it does not apply once the scripts have run.

  3. Access Vertica via vsql.

    /opt/vertica/bin/vsql -U vertica_dba

    1. Fix the template column.

      set search_path='momo';
      \i /opt/msys/app/db/scripts/V2014.11.26_15.45.00__rename_template_column.sql
      
    2. Fix webhook batch status grants.

      grant select, insert on momo.webhook_batch_status to vertica_api;

    3. Delete the tdate_month column.

      \i /opt/msys/app/db/scripts/V2015.01.27_14.35.00__drop_tdate_month.sql

      Warning

      This command may take a while to complete, but it must be finished before you can proceed.

    4. Quit vsql.

      \q