MicroStrategy ONE

MicroStrategy Repository Upgrade Guidelines and Troubleshooting

Introduction to Repository Upgrade

To ensure the continued security and stability of the MicroStrategy suite, MicroStrategy Repository upgrades PostgreSQL to version 14.7 in 2021 Update 10.

This upgrade:

  • Provides resolutions to known security vulnerabilities in older versions of PostgreSQL

  • Prevents users from experiencing a product outage

  • Enables users to take advantage of additional functionality and increased performance from the latest PostgreSQL releases

Only MicroStrategy’s locally installed PostgreSQL, not a remote repository, is covered in this upgrade workflow.

The upgrade contains two parts:

  • PostgreSQL server binaries update

  • Data migration

The upgrade does not change any underlying PostgreSQL folder structure, so any components that use PostgreSQL are not be affected.

There are several utilities/tools you can use to backup the out-of-the-box MicroStrategy Repository.

  1. MicroStrategy Repository Administration Tool

  2. Native PostgreSQL Backup and Restore Utility

    pg_dump, pg_dumpall

  3. MicroStrategy Branded PostgreSQL Backup and Restore Utility

    mstr_pg_dump, mstr_pg_dumpall

Databases in MicroStrategy Repository

For a freshly installed environment on Windows and Linux, the following databases are installed in the out-of-the-box MicroStrategy Repository:

Database Name Windows Express Install Windows Custom Install Linux Corresponding MicroStrategy Component
advdw_wh ü     Used for the MicroStrategy Tutorial project
mstr_collab ü ü ü MicroStrategy Collaboration Server
mstr_library ü ü ü MicroStrategy Library Server
operationaldm_wh ü     Used for the MicroStrategy Tutorial project
platform_analytics_wh ü ü ü Platform Analytics Project
poc_metadata ü     Tutorial Metadata
tutorial_wh ü     Tutorial warehouse

For a Linux installation and Windows custom installation, only the selected components' corresponding warehouse is installed in MicroStrategy Repository. For example, if you don’t install a Platform Analytics component, then platform_analytics_wh is not in MicroStrategy Repository.

MicroStrategy Repository Administration Tool

The MicroStrategy Repository Administration tool provides MicroStrategy users with a command line interface to backup, restore, and vacuum databases based on the database information in DBAdminConfig.yaml.

For more information on the MicroStrategy Repository Administration tool, see Repository Administration.

The MicroStrategy Repository Administration tool’s backup operation is based on the database information in DBAdminConfig.yaml. To backup all databases in MicroStrategy Repository, add the database names to DBAdminConfig.yaml. Only the database names listed in DBAdminConfig.yaml are backed up by this tool.

By default, the mstr database user and its encrypted password is used to access databases with this tool. You can find them in DBAdminConfig.yaml. If the password is modified, the tool may not function as expected.

Native PostgreSQL Backup and Restore Utility

The native PostgreSQL backup and restore utility can be used to backup and restore MicroStrategy Repository. It is shipped out-of-the-box with MicroStrategy Repository.

  • pg_dump Dumps a single database.

    Example:

    Copy 
    # To dump a database called mydb into an SQL-script file:
    pg_dump mydb > db.sql

  • pg_dumpall Backs up all databases in a given cluster, and also preserves cluster-wide data such as role and tablespace definitions.

    Example:

    Copy 
    # To dump all databases
    pg_dumpall > db.out

  • To restore, use psql or pg_restore.

    Example:

    Copy 
    # To reload pg_dump file into a (freshly created) database named newdb
    psql -d newdb -f db.sql

    # To restore pg_dumpall file db.out
    psql -f db.out postgres

Since there are many dump parameters for the backup option, you can choose to use them based on your own needs.

For the list of the parameters as well as usage examples, see pg_dump ,pg_dumpall, and pg_restore.

Windows

Default Path: C:\Program Files (x86)\Common Files\MicroStrategy\Repository\pgsql\bin

Example:

Copy 
 #dump mstr_collab
 pg_dump -d mstr_collab -Umstr > mstr_collab.sql
 
 #dump the whole postgres cluster
 pg_dumpall -Umstr > postgres.sql

Linux

Default Path: /opt/mstr/MicroStrategy/install/Repository/postgres11/bin

Before running the utilities on Linux, you must:

  • Source the setenv.sh file under /opt/mstr/MicroStrategy/install/Repository/bin.

    Failure to do so results in the following error:

    Error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory.

  • Declare the Unix-domain socket to use the /tmp path, since the native PostgreSQL back utility uses the /var/run/postgresql/ default path.

    If the path is not declared, the following error occurs:

    pg_dump: [archiver (db)] connection to database "mstr" failed: could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Example:

Copy 
#source setenv.sh
source /opt/mstr/MicroStrategy/install/Repository/bin/setenv.sh

#declare unix-domain socket and use pg_dump to dump metadata
cd /opt/mstr/MicroStrategy/install/Repository/postgres11/bin
./pg_dump -h /tmp -d poc_metadata -Umstr > md.dump

#dump the whole postgres cluster
./pg_dumpall -h /tmp -Umstr > postgres.dump

MicroStrategy Branded PostgreSQL Backup and Restore Utility

MicroStrategy also provides a “branded“ backup and restore utility on Linux. This utility is built based on the native PostgreSQL backup and restore utility. You do not need to source setenv.sh and declare Unix-domain socket with this tool.

Since this utility is built on a native PostgreSQL backup/restore utility, the parameters used with the native PostgreSQL back/restore utility can also be used.

Default path: /opt/mstr/MicroStrategy/install/Repository/bin

Example:

Copy 
#dump metadata
cd /opt/mstr/MicroStrategy/install/Repository/bin
./mstr_pg_dump -d poc_metadata -Umstr > md.dump

#dump the whole postgres cluster
./mstr_pg_dumpall -Umstr > postgres.dump

Upgrade the MicroStrategy Repository via the Platform Installer

Linux

  1. Start the installer and select Upgrade to upgrade your local MicroStrategy version to 2021 Update 10.

  2. If MicroStrategy Platform Analytics was selected in a previous installation, it is automatically selected in Select Components. Verify the components you want to upgrade and click Next.

  3. If the connection information to the MicroStrategy Repository has changed since the last installation, you are prompted to provide the database login username, password, and port. Complete the fields and click Next.

  4. You are prompted to backup the repository before proceeding. Review the sections above for information about backing up and restoring your repository. To proceed with the upgrade, you must confirm you have backed up your repository by entering upgrade. Click Next.

  5. The installer checks if there is enough free disk space available on the system. If not, the missing requirements are listed and the upgrade is aborted.

  6. If there is enough disk space, the upgrade process for the repository as well as other MicroStrategy components continues. However, if the repository upgrade fails, the entire upgrade aborts and the following screen appears.

    Click the link for detailed steps on how to analyze and troubleshoot the root causes. Once issues are resolved, rerun the installation to complete the upgrade.

Check install.log in the logs folder (Example: /var/log/MicroStrategy/) to see if it includes the PostgreSQL upgrade logs shown below. If they are included, the upgrade is successful

PostgreSQL upgrade log example:

Copy 
12 Apr 2023, 07:29:41 AM:INFO: Configuring pg_hba.conf file completed
12 Apr 2023, 07:29:49 AM:INFO: Successfully stopped MicroStrategy Repository Administration.
12 Apr 2023, 07:29:49 AM:INFO: Successfully stopped PostgreSQL.
12 Apr 2023, 07:29:49 AM:INFO: Backing up PostgreSQL bin files.
12 Apr 2023, 07:29:49 AM:INFO: Backing up pgdata files.
12 Apr 2023, 07:29:49 AM:INFO: Backing up Repository Administration Tool files.
12 Apr 2023, 07:29:49 AM:INFO: MicroStrategy Repository bin files and pgdata backup completed.
12 Apr 2023, 07:29:49 AM:INFO: Extracting new postgres binary files.
12 Apr 2023, 07:29:50 AM:INFO: Installing Repository files...
12 Apr 2023, 07:29:50 AM:INFO: Copying fileset RepositoryLinuxRepositoryInstallPath files...
12 Apr 2023, 07:29:56 AM:INFO: Initializing PostgreSQL
12 Apr 2023, 07:29:57 AM:INFO: Configuring postgresql.conf file.
12 Apr 2023, 07:29:57 AM:FINE: Using port 54302 temporarily for PostgreSQL upgrade.
12 Apr 2023, 07:29:57 AM:INFO: Configuring postgresql.conf file completed
12 Apr 2023, 07:29:57 AM:INFO: Checking postgres upgrade compatibility.
12 Apr 2023, 07:29:58 AM:INFO: Upgrade compatibility check has been passed.
12 Apr 2023, 07:29:58 AM:INFO: Starting PostgreSQL upgrade.
12 Apr 2023, 07:30:04 AM:INFO: PostgreSQL upgrade completed.
12 Apr 2023, 07:30:04 AM:INFO: Restoring old cluster's configuration files.
12 Apr 2023, 07:30:04 AM:INFO: Restoring pg_hba.conf file.
12 Apr 2023, 07:30:04 AM:INFO: Restoring pg_hba.conf file completed.
12 Apr 2023, 07:30:04 AM:INFO: Restoring postgresql.conf file.
12 Apr 2023, 07:30:04 AM:INFO: Restoring postgresql.conf file completed.
12 Apr 2023, 07:30:04 AM:INFO: Checking MicroStrategy Repository status
12 Apr 2023, 07:30:05 AM:INFO: Started PostgreSQL.
12 Apr 2023, 07:30:05 AM:INFO: Cleaning up files under Repository. 

Windows

  1. Start the installer to upgrade your local MicroStrategy version to 2021 Update 10.

  2. If MicroStrategy Repository was selected in a previous installation, it is automatically selected in Select Components. Verify the components you want to upgrade and click Next.

  3. If the connection information to the MicroStrategy Repository has changed since the last installation, you are prompted to provide the database login username, password, and port. Complete the fields and click Next.

  4. You are prompted to backup the repository before proceeding. Review the sections above for information about backing up and restoring your repository. To proceed with the upgrade, you must confirm you have backed up your repository by entering upgrade. Click Next.

  5. The installer checks if there is enough free disk space available on the system. If not, the space requirements are listed and the upgrade is aborted.

  6. If there is enough disk space, the upgrade process for the repository as well as other MicroStrategy components continues. However, if the repository upgrade fails, the entire upgrade aborts and the following screen appears.

    Click the link for detailed steps on how to analyze and troubleshoot the root causes. Once issues are resolved, rerun the installation to complete the upgrade.

Check install.log in the MicroStrategy installation folder (Example: C:\Program Files (x86)\Common Files\MicroStrategy) to see if it includes the PostgreSQL upgrade logs shown below. If they are included, the upgrade is successful

PostgreSQL upgrade log example:

Copy 
Performing Consistency Checks

-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from C:/Program Files (x86)/Common Files/MicroStrategy/Repository/pgsql/PGDATA/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Linking user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.bat

Running this script will delete the old cluster's data files:
    delete_old_cluster.bat

Troubleshooting PostgreSQL Upgrade Failures

If the PostgreSQL upgrade fails, check install.log to find the root cause.

Some typical failure scenarios for the PostgreSQL upgrade include:

Insufficient Disk Space

Upgrading the repository requires additional disk space for the backup, PostgreSQL binaries, and to migrate the databases.

The installer checks if there is enough free disk space available on the system. If not, the upgrade process aborts the following warning appears:

The upgrade cannot proceed as this machine does not have enough disk space.

Password Mismatch

The MicroStrategy installer needs to connect the old PostgreSQL repository using the super user who performed the initial PostgreSQL installation. If the password has changed and is not correctly provided during upgrade, the upgrade process may fail.

You must provide the correct super user password to connect to the old PostgreSQL database.

Access Permission Issues

The user performing the upgrade may not have the necessary permissions to access certain files or directories required for the upgrade.

The correct user to run the MicroStrategy installation and upgrade of the repository is the super user.

Incorrect Configuration Settings

During the upgrade process, the configuration settings for PostgreSQL may need to be updated. If the settings are incorrect, the upgrade process may fail.

An error message is logged to indicate the configuration file is not valid because it contains invalid settings. You must correct the invalid configuration and rerun the installation.

Data Corruption

Data corruption can occur during the upgrade process if there are errors or issues with the data being migrated.

An error message is logged to indicate the data file for the database is corrupt. You must fix the data error by restoring data from the backup and running the installation again.

Failover Scenarios and Restoration

If PostgreSQL data files are interrupted during the upgrade, you may need to restore databases from the backups taken prior to starting the upgrade.

  • If backups were done using the MicroStrategy Repository Administration tool, you must use the same tool to restore them. Follow the steps in Repository Administration to perform the restoration.

  • If backups were done using the native PostgreSQL backup utility, MicroStrategy recommends using the native PostgreSQL restore utility to perform the restoration.

Windows

Default Path: C:\Program Files (x86)\Common Files\MicroStrategy\Repository\pgsql\bin

Example:

Copy 
# To restore platform_analytics_wh from dump file with creating same database name
pg_restore -d postgres -Umstr --clean --create platform_analytics_wh.dump

# To restore mstr_collab to the new created database from dump file
psql -Umstr -d mstr_collab < mstr_collab.sql

# To restore pg_dumpall file all_dbs.out
psql -Umstr -f all_dbs.out postgres

Linux

Default Path: /opt/mstr/MicroStrategy/install/Repository/bin

Before running the restore utility on Linux, you must:

  • Source the setenv.sh file under /opt/mstr/MicroStrategy/install/Repository/bin.

    Failure to do so results in the following error:

    Error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory.

  • Declare the Unix-domain socket to use the /tmp path, since the native PostgreSQL back utility uses the /var/run/postgresql/ default path.

    If the path is not declared, the following error occurs:

    pg_dump: [archiver (db)] connection to database "mstr" failed: could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Example:

Copy 
# To restore database platform_analytics_wh
cd /opt/mstr/MicroStrategy/install/Repository/bin
./mstr_pg_restore -d platform_analytics_wh -Umstr < platform_analytics_wh.dump