Portal database migration configuration

SuperMap iPortal 10i(2020) SP1+ provides a portal database migration tool (migration.bat/sh) to help you realize the rapid migration of portal data (maps, services, scenes, data, projects, accounts, and other basic information). The portal database migration tool is located in the %SuperMap iPortal_HOME%\support\migration directory. With this portal database migration tool, you can perform any migration operation of portal data among the four different databases of SQLite, MySQL, Oracle, and PostgreSQL. And you can also migrate the portal databases between different versions of iPortal.

Note: The migration tool supports migrating from SuperMap iPortal 9D(2019) SP2+ to a later version iPortal. Before the migration, if the source iPortal doesn't provide the migration tool, you need to copy the migration tool(in the %SuperMap iPortal_HOME%\support\migration directory) and the corresponding jar(***-migration-***.jar)(in the %SuperMap iPortal_HOME%\webapps\iportal\WEB-INF\lib directory) from a newer version iPortal to the source iPortal.

Migrate portal data

Before migration, you need to complete the database migration configuration. The corresponding configuration file "config.properties" is located in the %SuperMap iPortal_HOME%\support\migration directory. In the file, the configuration examples for migrating from SQLite database to MySQL database, Oracle database, and PostgreSQL database have been written well. You only need to comment out the target configuration example and fill in the actual database connection info. After completing, stop the iPortal service, click to run the portal data migration script (migration.bat/sh) to migrate the portal data.

SQLite database migration configuration

In the config.properties configuration file, a configuration example with the SQLite database as the source database is provided. If you want to use the SQLite database as the target database for migration, you only need to modify the prefix name of the SQLite database configuration variable to "target".

Configuration example:

source.dbType=SQLITE

source.username=username

source.password=password

source.jdbcUrl=jdbc:sqlite:../../webapps/iportal/WEB-INF/iportaldata/iportal.db

source.maxPoolSize=1

source.minPoolSize=1

source.maxIdleTime=3000

source.maxWait=300000

source.initialPoolSize=1

source.skipTables=proxyserviceaccessrecords

source.step=1000

dbType: Database type, characters need to be uppercase.
username: The user name of the SQLite database can be empty.
password: The password of the SQLite database can be empty.
jdbcUrl: The url used to connect the driver, i.e., the file path of the sqlite database. The built-in SQLite database is filled in the example, you can change according to the actual situation.
maxPoolSize: The maximum number of active connections the connection pool provides at the same time.
minPoolSize: The minimum number of active connections the connection pool provides at the same time.
maxIdleTime: Maximum idle time, the connection is discarded if not used within maxIdleTime. The default value is 0 and if maxIdleTime is 0, it will never be discarded.
maxWait: The maximum number of milliseconds the database waits for, when an exception occurs (when no connection is available).
initialPoolSize: The initial size of the connection pool.
skipTables: Specify tables that do not need to be migrated. For example, if the amount of data in some tables is too large but not important, you can choose to skip during the migration. The table names must be separated by commas.
step: Specify the maximum number of rows for each migration, the default value: 1000.

MySQL database migration configuration

In the config.properties configuration file, a configuration example with the MySQL database as the target database is provided. If you want to use the MySQL database as the source database for migration, you only need to modify the prefix name of the MySQL database configuration variable to "source".

Configuration example:

target.dbType=MYSQL

target.username=supermap

target.password=supermap

target.jdbcUrl=jdbc:mysql://127.0.0.1:3306/iportal?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true

target.maxPoolSize=30

target.minPoolSize=5

target.maxIdleTime=3000

target.maxWait=300000

target.initialPoolSize=1

dbType: Database type, characters need to be uppercase.
username: Configure the actual MySQL account name.
password: Configure the actual MySQL account password.
jdbcUrl: The url used to connect the driver. If SuperMap iPortal and MySQL are on the same machine, the IP is localhost. If they are not on the same machine, the IP here should be the IP of the machine where MySQL is located. The default port for MySQL is: 3306, which you can configure based on the actual situation of the MySQL you installed. Database is the database name and the default is iportal. Ensure the name of existing database and the database you created should be matched.
maxPoolSize: The maximum number of active connections the connection pool provides at the same time.
minPoolSize: The minimum number of active connections the connection pool provides at the same time.
maxIdleTime: Maximum idle time, the connection is discarded if not used within maxIdleTime. The default value is 0 and if maxIdleTime is 0, it will never be discarded.
maxWait: The maximum number of milliseconds the database waits for, when an exception occurs (when no connection is available).
initialPoolSize: The initial size of the connection pool.

Oracle database migration configuration

In the config.properties configuration file, a configuration example of the Oracle database as the target database is provided. If you want to use the Oracle database as the source database for migration, you only need to modify the prefix name of the Oracle database configuration variable to "source".

Configuration example:

target.dbType=ORACLE

target.username=supermap

target.password=supermap

target.jdbcUrl=jdbc:oracle:thin:@127.0.0.1:1521:orcl

target.maxPoolSize=30

target.minPoolSize=5

target.maxIdleTime=3000

target.maxWait=300000

target.initialPoolSize=1

dbType: Database type, characters need to be uppercase.
username: Username of the user, who has granted the database permission.
password: Password of the user, who has granted the database permission.
jdbcUrl: The url used to connect the driver. If SuperMap iPortal and Oracle are on the same machine, the IP is localhost. If it is not on the same machine, the IP here should write the IP of the machine where Oracle is located. Oracle's default port is: 1521, which you can configure based on your actual installed Oracle. iportal is the database instance name, just the same as the database instance name you created.
maxPoolSize: The maximum number of active connections the connection pool provides at the same time.
minPoolSize: The minimum number of active connections the connection pool provides at the same time.
maxIdleTime: Maximum idle time, the connection is discarded if not used within maxIdleTime. The default value is 0 and if maxIdleTime is 0, it will never be discarded.
maxWait: The maximum number of milliseconds the database waits for, when an exception occurs (when no connection is available).
initialPoolSize: The initial size of the connection pool.

Note: If the error "Missing Oracle driver package" is reported during Oracle database migration, you need to add Oracle's JDBC driver package to iPortal. For details, please refer to: Add the JDBC driver package

PostgreSQL database migration configuration

In the config.properties configuration file, a configuration example of the PostgreSQL database as the target database is provided. If you want to use the PostgreSQL database as the source database for migration, you only need to modify the prefix name of the PostgreSQL database configuration variable to "source".

Configuration example:

target.dbType=POSTGRESQL

target.username=supermap

target.password=supermap

target.jdbcUrl=jdbc:postgresql://127.0.0.1:5432/iportal?useUnicode=true&characterEncoding=UTF-8

target.maxPoolSize=30

target.minPoolSize=5

target.maxIdleTime=3000

target.maxWait=300000

target.initialPoolSize=1

dbType: Database type, characters need to be uppercase.
username: Configure the actual PostgreSQL account name.
password: Configure the actual PostgreSQL account password.
jdbcUrl: The url used to connect the driver. If SuperMap iPortal and PostgreSQL are on the same machine, the IP is localhost. If they are not on the same machine, the IP here should be the IP of the machine where PostgreSQL is located. The default port for PostgreSQL is: 5432, which you can configure based on the actual PostgreSQL you installed. The iportal is the database name, just the same as the database name you created.
maxPoolSize: The maximum number of active connections the connection pool provides at the same time.
minPoolSize: The minimum number of active connections the connection pool provides at the same time.
maxIdleTime: Maximum idle time, the connection is discarded if not used within maxIdleTime. The default value is 0 and if maxIdleTime is 0, it will never be discarded.
maxWait: The maximum number of milliseconds the database waits for, when an exception occurs (when no connection is available).
initialPoolSize: The initial size of the connection pool.

Switch the portal database

After completing the migration, you also need to configure to switch the portal database of the target iPortal to the migrated database. The portal database configuration information stores in the iportal.xml configuration file under the installation directory (%SuperMap iPortal_HOME%\webapps\iportal\WEB-INF). It configures the built-in SQLite database as the storage database by default. You need to configure it using the actual migrated database. Please refer to configure the MySQL database, Oracle database, or PostgreSQL database.

Besides, you also need to set the value of the node under the node to true in the iPortal.xml file to rebuild the global search index. The specific configuration is as follows:

...

...

</modulesConfig>

<reindexAllResources>: Whether to re-index existing iPortal resources. After the index is rebuilt, this value will be automatically set to false.

After completing the above configuration, restart iPortal to take effect.