Data migration tool - Part I: from Postgres to IRIS

Sometimes it is necessary to transfer or migrate data and data schema from Postgres to IRIS. There are currently a few options for doing this, but the two most popular options are using DBeaver (https://openexchange.intersystems.com/package/DBeaver) or SQLGateway. The first will be demonstrated in this article and the second is presented in an excellent article by Robert Cemper, DB Migration using SQLgateway (https://community.intersystems.com/post/db-migration-using-sqlgateway), see in this article how to perform this migration using DBeaver:

Get the sample data to the migration process

In the Github is possible download a docker compose project to build and run 2 databases:

• Source Database: PostgreSQL database Docker instance with a sample database.
• Target Database: InterSystems IRIS data platform Docker instance with a ready schema to receive the source database.

To get the sample and run it, follow these steps:

1. Go to the https://github.com/yurimarx/migration-pg-iris and click Download to go to the git repository.
2. Clone the project: git clone https://github.com/yurimarx/migration-pg-iris.git.  
3. Go to the project folder migration-pg-iris.
4. Do the build: docker-compose build.
5. Execute the containers: docker-compose up -d.
6. See in your docker desktop with the instances are ok:

About the data to be migrated

The data to be migrated is represented here:

So, the migration process from PostgreSQL to IRIS will migrate:

• 08 tables.
• 1000000 rows of sale.
• 250000 rows of users.
• 300 rows of product.
• 500 rows of store.
• 100 rows of country.
• 30 rows of city.
• 5 rows of status_name.

The migration destination will be dc_test schema inside USER namespace in the InterSystems IRIS database.

Open-source tool to migrate from PostgreSQL to IRIS: DBeaver

DBeaver is a database tool to connect, create, drop, select, update and delete data objects to the main database products in the market. Download it from: https://openexchange.intersystems.com/package/DBeaver. Now follow the installation instructions to get this fantastic product into your laptop or desktop.
DBeaver can be used to migrate data between database connections, even if they are from different manufacturers and versions.

Connecting the Source and Target Databases using the DBeaver

Now we will set the database connections to be migrated.
To set PostgreSQL connection to the DBeaver:

1. In the DBeaver Go to File > New.

2. Select Database Connection and click Next:

3. Choose SQL tab > PostgreSQL and click next:

4. Fill the PostgreSQL connection fields like this picture:

Host: localhost Port: 5438 Database: postgres Username: postgres Password: postgres Click Finish.

To set InterSystems IRIS connection to the DBeaver:

1.    In the DBeaver Go to File > New.
2.    Select Database Connection and click Next:

3.    Choose SQL tab > InterSystems IRIS and click next:

4.    If DBeaver requests to download the InterSystems IRIS driver, press Yes or Ok.
5.    Set the InterSystems IRIS connection fields like this picture:

Host: localhost Database/Schema: user Username: _SYSTEM Password: SYS Click Text Connection and Finish.

6. The connections (postgres and user) are available in the Database Navigator:

Do the migration

To do the migration, follow these steps:
1.    Expand the postgres connection > public and select all tables. Click with the mouse right button with the tables selected and choose Export Data, like this picture:

2.    Select Database, like in this picture and click Next

3.    Click the Choose button:

4.    Select dc_test and click Ok.

5.    Now It is necessary change some data type configurations for the target database, because the IRIS and PostgreSQL use different data types for integer and decimal values.
6.    Expand public.country table, select the first field (country_id) and click Columns…

7.    Change the Target Type from int4 to integer and click Ok.

8.    Repeat the process to the tables

a.    public.product.
b.    public.status_name.
c.    public.users.
d.    public.city (change the type to integer for city_id and country_id).
e.    public.store (change the type to integer for store_id and city_id).
f.    public.sale (change the type to double for amount and integer for product_id, user_id and store_id)

g.    public.order_status (change status_name_id to integer).

9.    Now with the Target Data Types changed, click Next.

10.    Set Fetch size to 1000000 and click Next.

11.    Accept the default values to the Data load settings and click Next.

12.    In the Confirm click Proceed.

13.    Now see in the Database Navigator all PostgreSQL Tables inside InterSystems IRIS dc_test schema.

The migration process was very simple for tables, but for views, functions, triggers and stored procedures, you need to rewrite the SQL source code using ObjectScript or SQL.

What do you get by migrating to IRIS?

This list summarizes what you get in IRIS:

• API Management.
• Visual Reports (IRIS Reports).
• AutoML (IntegratedML).
• Multilanguage application/data development (Python, Java, .NET, JavaScript).
• ESB.
• BI/Analytics.
• NLP.
• Microservices development.
• Multimodel database (SQL, JSON, Analytical Cubes, Object Oriented).
• Sharding.

In summary, when migrating to IRIS you get a data platform, when before you only had a database.