MicroStrategy ONE

Migrate Data from a MySQL Database to a PostgreSQL Database

  • Windows
  • Linux

The Platform Analytics Data Migration tool is used to help existing customers migrate their data from MySQL to the newly supported PostgreSQL repository. This tool can help migrate both new and old versions of MySQL dump files to the latest version of Platform Analytics.

Backup Prerequisites:

  • C:\Program Files (x86)\MicroStrategy\Platform Analytics\PAConsumerConfig.yaml populated with:

    warehouseDbConnection:

    • whHost: 127.0.0.1
    • whUser: root
    • whPasswd: encrypted_password
    • whPort: 3306
    • whDb: platform_analytics_wh
  • mysql-connector-java.jar is present in PlatformAnalytics\lib directory.
  • Disk space sufficient to hold a backup of your MySQL platform_analytics_wh database.

Restore Prerequisites:

  • PAConsumerConfig.yaml populated with:

    pgWarehouseDbConnection:

    • pgWhHost: 127.0.0.1

    • pgWhUser: postgres

    • pgWhPasswd: encrypted password

    • pgWhPort: 5432

    • pgWhDb: platform_analytics_wh

  • Path to .csv files from a previous backup of platform_analytics_wh.

  • Enough disk space available to PostgreSQL to restore the backed up ..csv files from MySQL.

Launching the Platform Analytics Data Migration Tool

  1. Navigate to your Platform Analytics home directory and go into the bin directory:

    C:\Program Files (x86)\MicroStrategy\Platform Analytics\bin

  2. Call the following script:

    platform-analytics-data-migration-tool.bat

  3. You will then be prompted with the following :

    Copy 
    This is the Platform Analytics Data Migration Tool. The purpose of this tool is to help migrate your data from an existing Mysql Warehouse to a new PostgreSQL Warehouse.
    Please select from the following options:
    1) Backup
    2) Restore
    3) Backup and Restore
    0) Exit

Migration Workflow

Backup

  1. Provide the path to the directory where the MySQL backup will be stored.

  2. The tool will then begin backing up the MySQL platform_analytics_wh specified in your PAConsumerConfig.yaml file, placing the backup in your specified path.

Restore

  1. Provide the path to the directory where the MySQL backup is stored.

  2. The tool will prompt you again if you are sure you are okay to drop your PostgreSQL platform_analytics_wh schema.

  3. If yes is selected, the platform_analytics_wh schema will be dropped and recreated matching the version of your MySQL dump.

  4. The backup data is then imported into the newly created platform_analytics_wh schema.

  5. The platform_analytics_wh schema will then be upgraded to the latest version of Platform Analytics.

Recommended Upgrade Procedures

In-Place Upgrades

If you are performing an in-place upgrade the best practice steps would be as followed:

  1. Confirm that the Platform Analytics Consumer is stopped. Data migration should not take place while new entries are still being processed.
  2. Confirm that the PAConsumerConfig.yaml has the MySQL and PostgreSQL information shown in the prerequisites above.
  3. Go to your Platform Analytics bin directory and call the platform-analytics-data-migration-tool.bat file.
  4. Select the Backup and Restore option (3).
  5. Enter the full desired directory path for the database to be backed up to and restored from.
  6. Wait until the backup is complete. The tool you will then prompt if it is okay to recreate the PostgreSQL warehouse and select yes.
  7. The program will then restore your MySQL backup files into your new PostgreSQL warehouse and the data migration will be complete.

  8. If you have installed Workstation and Service Registration, the service's grouping and dependency information in MicroStrategy Workstation's Topology view should be updated. When Topology is not updated, the view will show Consumer as dependent on five other services, including Repository (MySQL).

    To update Topology in Workstation:

    1. Locate the MicroStrategy-shipped java path. By default, this is C:\Program Files (x86)\Common Files\MicroStrategy\JRE\180_222.
    2. Locate the Services Registration's installation directory. By default, this is C:\Program Files (x86)\MicroStrategy\Services Registration\jar.

    3. Execute the following command:

      Copy 
      "C:\Program Files (x86)\Common Files\MicroStrategy\JRE\180_222\Win64\bin\java" -jar 
      "C:\Program Files (x86)\MicroStrategy\Services Registration\jar\svcsreg-admin.jar" migrate MicroStrategy-Platform-Analytics-Consumer MySQL PostgreSQL
    4. Open Workstation and select the Topology tab. Consumer should now appear to depend on and belong to the same group as Store (PostgreSQL).

Parallel Upgrades

  1. On your new MicroStrategy ONE machine, populate the PAConsumerConfig.yaml has the MySQL and PostgreSQL information shown in the prerequisites above.

  2. Copy the mysql-connector-java.jar from your previous installation to the Platform Analytics\lib directory on the new machine.
  3. Go to your Platform Analytics bin directory and call the platform-analytics-data-migration-tool.bat file.
  4. Select the Backup and Restore option (3).
  5. Enter the full desired directory path for the database to be backed up to and restored from.
  6. Wait until the backup is complete. The tool you will then prompt if it is okay to recreate the PostgreSQL warehouse and select yes.
  7. The program will then restore your MySQL backup files into your new PostgreSQL warehouse and the data migration will be complete.

  8. If you have installed Workstation and Service Registration, the service's grouping and dependency information in MicroStrategy Workstation's Topology view should be updated. When Topology is not updated, the view will show Consumer as dependent on five other services, including Repository (MySQL).

    To update Topology in Workstation:

    1. Locate the MicroStrategy-shipped java path. By default, this is C:\Program Files (x86)\Common Files\MicroStrategy\JRE\180_222.
    2. Locate the Services Registration's installation directory. By default, this is C:\Program Files (x86)\MicroStrategy\Services Registration\jar.

    3. Execute the following command:

      Copy 
      "C:\Program Files (x86)\Common Files\MicroStrategy\JRE\180_222\Win64\bin\java" -jar 
      "C:\Program Files (x86)\MicroStrategy\Services Registration\jar\svcsreg-admin.jar" migrate MicroStrategy-Platform-Analytics-Consumer MySQL PostgreSQL
    4. Open Workstation and select the Topology tab. Consumer should now appear to depend on and belong to the same group as Store (PostgreSQL).

The Platform Analytics Data Migration tool is used to help existing customers migrate their data from MySQL to the newly supported PostgreSQL repository. This tool can help migrate both new and old versions of MySQL dump files to the latest version of Platform Analytics.

Backup Prerequisites:

  • /MicroStrategy/install/PlatformAnalytics/PAConsumerConfig.yaml populated with:

    warehouseDbConnection:

    • whHost: 127.0.0.1
    • whUser: root
    • whPasswd: encrypted_password
    • whPort: 3306
    • whDb: platform_analytics_wh
  • mysql-connector-java.jar is present in PlatformAnalytics/lib directory.
  • Disk space sufficient to hold a backup of your MySQL platform_analytics_wh database.

Restore Prerequisites:

  • PAConsumerConfig.yaml populated with:

    pgWarehouseDbConnection:

    • pgWhHost: 127.0.0.1

    • pgWhUser: postgres

    • pgWhPasswd: encrypted password

    • pgWhPort: 5432

    • pgWhDb: platform_analytics_wh

  • Path to .csv files from a previous backup of platform_analytics_wh.

  • Enough disk space available to PostgreSQL to restore the backed up ..csv files from MySQL.

Launching the Platform Analytics Data Migration Tool

  1. Navigate to your Platform Analytics home directory and go into the bin directory:

    /opt/mstr/MicroStrategy/PlatformAnalytics/bin

  2. Run the following script:

    ./platform-analytics-data-migration-tool.sh

  3. You will then be prompted with the following :

    Copy 
    This is the Platform Analytics Data Migration Tool. The purpose of this tool is to help migrate your data from an existing Mysql Warehouse to a new PostgreSQL Warehouse.
    Please select from the following options:
    1) Backup
    2) Restore
    3) Backup and Restore
    0) Exit

Migration Workflow

Backup

  1. Provide the path to the directory where the MySQL backup will be stored.

  2. The tool will then begin backing up the MySQL platform_analytics_wh specified in your PAConsumerConfig.yaml file, placing the backup in your specified path.

Restore

  1. Provide the path to the directory where the MySQL backup is stored.

  2. The tool will prompt you again if you are sure you are okay to drop your PostgreSQL platform_analytics_wh schema.

  3. If yes is selected, the platform_analytics_wh schema will be dropped and recreated matching the version of your MySQL dump.

  4. The backup data is then imported into the newly created platform_analytics_wh schema.

  5. The platform_analytics_wh schema will then be upgraded to the latest version of Platform Analytics.

Recommended Upgrade Procedures

In-Place Upgrades

If you are performing an in-place upgrade the best practice steps would be as followed:

  1. Confirm that the Platform Analytics Consumer is stopped. Data migration should not take place while new entries are still being processed.
  2. Confirm that the PAConsumerConfig.yaml has the MySQL and PostgreSQL information shown in the prerequisites above.
  3. Go to your PlatformAnalytics/bin directory and call the platform-analytics-data-migration-tool.sh file.
  4. Select the Backup and Restore option (3).
  5. Enter the full desired directory path for the database to be backed up to and restored from.
  6. Wait until the backup is complete. The tool will then prompt if it is okay to recreate the PostgreSQL warehouse and select yes.
  7. The program will then restore your MySQL backup files into your new PostgreSQL warehouse and the data migration will be complete.

  8. The service's grouping and dependency information needs to be updated in MicroStrategy Workstation's Topology view. When Topology is not updated, Telemetry Consumer does not appear to be dependent on Store (PostgreSQL).

    To update Topology in Workstation:

    1. Find the owner of the MicroStrategy installation directory. By default, the owner is the mstr user.
    2. Locate the MicroStrategy-shipped java path. By default, this is /opt/MicroStrategy/_jre.
    3. Locate the Services Registration's installation directory. By default, this is /opt/MicroStrategy/ServicesRegistration.

    4. Execute the following command:

      Copy 
      $ su - mstr
      $ /opt/MicroStrategy/_jre/bin/java -jar /opt/MicroStrategy/ServicesRegistration/jar/svcsreg-admin.jar migrate MicroStrategy-Platform-Analytics-Consumer MySQL PostgreSQL

    5. Open MicroStrategy Workstation and select the Topology tab. Consumer should now depend on Store (PostgreSQL).

Parallel Upgrades

  1. On your new MicroStrategy ONE machine, populate the PAConsumerConfig.yaml has the MySQL and PostgreSQL information shown in the prerequisites above.

  2. Copy the mysql-connector-java.jar from your previous installation to the PlatformAnalytics/lib directory on the new machine.
  3. Go to your PlatformAnalytics/bin directory and call the platform-analytics-data-migration-tool.sh file.
  4. Select the Backup and Restore option (3).
  5. Enter the full desired directory path for the database to be backed up to and restored from.
  6. Wait until the backup is complete. The tool will then prompt if it is okay to recreate the PostgreSQL warehouse and select yes.
  7. The program will then restore your MySQL backup files into your new PostgreSQL warehouse and the data migration will be complete.

  8. The service's grouping and dependency information needs to be updated in MicroStrategy Workstation's Topology view. When Topology is not updated, Telemetry Consumer does not appear to be dependent on Store (PostgreSQL).

    To update Topology in Workstation:

    1. Find the owner of the MicroStrategy installation directory. By default, the owner is the mstr user.
    2. Locate the MicroStrategy-shipped java path. By default, this is /opt/MicroStrategy/_jre.
    3. Locate the Services Registration's installation directory. By default, this is /opt/MicroStrategy/ServicesRegistration.

    4. Execute the following command:

      Copy 
      $ su - mstr
      $ /opt/MicroStrategy/_jre/bin/java -jar /opt/MicroStrategy/ServicesRegistration/jar/svcsreg-admin.jar migrate MicroStrategy-Platform-Analytics-Consumer MySQL PostgreSQL

    5. Open MicroStrategy Workstation and select the Topology tab. Consumer should now depend on Store (PostgreSQL).