MySQL / PostgreSQL Migration Tool

Convert MySQL to PostgreSQL

A common application for MapForce is migrating data between MySQL and PostgreSQL databases. MapForce makes this easy with graphical, drag-and-drop data mapping tools including a rich library of filters and data processing functions to transform source data.

The Database Connection Wizard in MapForce makes it easy to connect. SQLite connections are supported as native, direct connections to the SQLite database file. No separate drivers are required. PostgreSQL connections are supported both as native connections and driver-based connections through interfaces (drivers) such as ODBC or JDBC. Native connections do not require any drivers.

When you load the MySQL and PostgreSQL database structures in the mapping pane, MapForce automatically interprets the database schemas, lets you pick available database tables and views, and recognizes table relationships.

Once you have loaded all of the content models required for your database mapping, complete the mapping by simply dragging connecting lines between the source and target structures.

Using the no-code MapForce approach, it's easy to meet common data transformation requirements, for example:

Many database mappings require tran sformation of data between the source and target based on Boolean conditions or SQL and SQL/XML statements. You may need to perform logical comparisons, mathematical computations, or string operations, check for database data of a particular value, and make other modifications to the data. In screenshot above, data processing functions appear as the boxes between the lines joining the source and the target data model.

Data processing functions enable you perform advanced database mappings on-the-fly for a multitude of real-world transformation requirements.

Once your mapping is defined, the built-in MapForce Engine allows you to view and save the results with one click.

Your MySQL to PostgreSQL mapping will produce output in the form of SQL scripts (e.g., SELECT, INSERT, UPDATE, and DELETE statements) that are run against your target database directly from within MapForce.

After previewing the output, you'll have the option to automate the data transformation process via MapForce Server.

Database transaction error handling

MapForce also offers database transaction handling to roll back the affected part of the database conversion when an error is thrown and optionally proceed with the rest of the mapping.

Sometimes an error occurs that doesn’t prevent the rest of a database mapping from continuing, such as when certain database constraints prevent the mapping from inserting or updating invalid data.

Enabling database transaction handling will enclose all changes done by the database component inside a transaction that will be rolled back in case of error, and you can opt to either proceed with rest of the mapping or stop further execution of the mapping. You can enable transactions at database component level and at table action level.

This is very useful when the mapping designer wants to capture valid incoming data and simply ignore invalid data.

Automate Database Mapping Execution with MapForce Server

MapForce pre-processes and optimizes data mappings, stores them in MapForce Server Execution files for command-line execution by MapForce Server, and uploads them for use in FlowForce Server jobs.

When MapForce Server operates under the management of FlowForce Server, data mappings are executed as FlowForce Server job steps, based on triggers defined as part of the FlowForce Server job. For example, a new XBRL instance document lands in a directory, which triggers a multi-step FlowForce Server job to first validate the file using RaptorXML+XBRL Server, then execute MapForce Server to extract certain data from the XBRL and insert it into a database.