PostgreSQL to MogDB Evaluation

MogDB Environment Preparation

You need to prepare your own MogDB/openGauss database environment for the repository and the target database, and the same environment is used by default for the repository and the target database.

Object Structure Migration

Structure Migration: You need to create the target database in the prepared MogDB/openGauss environment and migrate the data structures from the source database to the newly created target database.
Data Migration: If you want to perform SQL performance comparison evaluation, then in addition to migrating the database structure, you also need to migrate the table data to ensure that the source PostgreSQL and the target MogDB/openGauss database data size is the same for the performance comparison to be meaningful.

Related Tool:

MTK: Structure and data migration can be done by MTK, for details please refer to https://mogdb.io/mtk

PostgreSQL Data Collection

Currently, the PostgreSQL source SQL collection relies on the plug-in pg_stat_statements plugin, which collects all SQL recorded in the plug-in table at once.

To avoid SQL misses, it is recommended to adjust the maximum number of SQL retained in the plugin and ensure that the entire business cycle is tested and executed or covered.

Required Permissions

The permissions required for the collection user are as follows.

superuser (for querying the data dictionary and pg_stat_statements plug-in table)

./sca_linux_x86_64 -T PC -h <host> -p <port> -n <source-pg-db> -u <pg-user> -e <pg-password>

# Command options:
# -h/p/t/u/e specify the connection method of the source PostgreSQL database

采集结果

When the collection is complete, a zip packet is generated which can be copied to the target database and unpacked into a data directory.

Subsequent compatibility analysis relies on the data in this packet and does not require another connection to the source PostgreSQL database.

Repository Initialization

# Use the MogDB/openGauss user with administrator privileges for repository initialization
# Repository initialization will create a database with the name sca_db by default
# You can specify the repository name by -N, the repository user name by -U, and the repository user password by -E

./sca_linux_x86_64 -T i -H <host> -P <port> -N sca_db -U sca_repo -E 'SCA@password' --user <super-user> --password <super-password>

Perform Analysis Tasks

Compatibility Analysis

PostgreSQL database currently only supports SQL compatibility analysis with the following command.

# If the repository name, username, password are not the default, you need to use the -N, -U, -E options to specify
# If the target database information is different from the repository, use the -h, -p, -n, -u, -e options to specify

./sca_linux_x86_64 -T PI -H <host> -P <port> -n <target-db> -d <unzipped data directory>

When the analysis is complete, a report will be generated in the directory specified by -d, which can be downloaded offline for viewing.