Can I use the APM Config Import Tool to migrate from Oracle to Postgres?

Products

CA Application Performance Management Agent (APM / Wily / Introscope) INTROSCOPE

Issue/Introduction

A customer is upgrading his EM from a lower version of APM to the latest version. In the lower version, he is using Oracle database. Now in the latest version, he wants to use the Postgres database instead of Oracle. So, he exports data from Oracle using configexport command. This will generate export_cem_oracle.xml file. Using this file, he will now try to import the data into Postgres database using configimport command.

Environment

All supported APM and Oracle/Postgres releases. However, the utility was only tested with upgrading from 9.5.3 to 10.5.1 release. 9.5.3 EM had Oracle database 10.1.0.0 and 10.5.1 EM had Postgres 9.5.

Cause

Importing data into Postgres will fail in this scenario. As export_cem_oracle.xml file contains exported data from Oracle DB, trying to import the file into Postgres DB will throw many errors due to the difference in formats. For instance, in Oracle, the column TS_SOFT_DELETE in the table TANG_CONFIG is of type numeric which accepts the values which are only 0s and 1s. But in Postgres, the same column is of type Boolean which accepts the values true or false.

Some typical Errors:

When migrating APM 9.5.3 (Oracle) to APM 10.5 on Postgres, they are seeing the error:

Error: [ERROR] [main] [root] [ConfigImport] - Error parsing export file at line: 31. Cause: SQL insert error: ERROR: column "ts_soft_delete" is of type boolean but expression is of type numeric

Hint: You will need to rewrite or cast the expression.

Position: 97.

...

[ERROR] [main] [com.wily.apm.dbutil.SqlExecutor] Sql error in file: dbupgrade-apm-postgres-9.5.6.0.sql

[ERROR] [main] [com.wily.apm.dbutil.SqlExecutor] org.postgresql.util.PSQLException: ERROR: column "ts_web_view_port_secure" of relation "ts_introscope_ems" already exists

Customer already added this column in createtables-postgres-9.5.0.0.sql during the db upgrade to 9.5.6,

Resolution

For the last error above only:

Open the file dbupgrade-apm-postgres-9.5.6.0.sql in 10.5.1 environment. Then remove or comment out the line number 9 which has:

alter table ts_introscope_ems add column ts_web_view_port_secure bool not null default false;

Overall:

Using the jar ExportOracleToPostgres.jar, we can change the format of export_cem_oracle.xml from Oracle to Postgres. This jar will generate export_cem_postgres.xml file. Using this file, the users can import the data into Postgres DB.

Important Note:

This tool is provided AS-IS without official support. It is highly recommended to backup the Oracle and Postgres systems before deploying the Export Oracle utility.

Users need to execute the following command using the jar.

java -jar ExportOracleToPostgres.jar <location of export_cem_oracle.xml>

This jar takes the input as absolute path of export_cem_oracle.xml file. And after execution, it generates export_cem_postgres.xml file in the jar location, as output. If the argument <location of export_cem_oracle.xml> is not provided in the above command, the jar will consider the default location(where the jar is placed).

Additional Information

The jar file and Readme can be found at the APM Community.

https://communities.ca.com/docs/DOC-231177041-exportoracletopostgrestxt -- Read Me

https://communities.ca.com/docs/DOC-231177040-exportoracletopostgresjar - Jar File