db-migration-using-SQLgateway

Downloads13
Subscribe
0
Bookmark
1
This application is not supported by InterSystems Corporation. Please be notified that you use it at your own responsibility.
Details
Releases
Reviews
Screenshots
Issues

What's new in this version

  • added Demo Server
  • added WebTerminal
  • added Code Quality (empty)

db-migration-using-SQLgateway

Sample repository to show how to migrate from PostgreSQL to InterSystems IRIS
using SQLgateway in differnce to using an external tool as DBeaver or similar.
I especially try to demonstrate the comfort in fitting underlaying naming limitations.

Credits

  1. OEX package migration-pg-iris-dataset provided by YURI MARX PEREIRA GOMES

    • Special thanks as this was an excellent base to start off.
  2. Article about PostgreSQL into Docker:

  3. Git project created from:

Prerequisites

Make sure you have git and Docker desktop installed.

Installation

Clone/git pull the repo into any local directory

git https://github.com/isc-at/db-migration-using-SQLgateway.git
  1. Build
docker-compose build
  1. Run it in foreground. Sometimes container start is slower than estimated.
docker-compose up
  • Wait for confirmation from postgres container: ready to accept connections
postgres_1  | 2022-01-02 15:37:27.654 UTC [1] LOG:  database system is ready to accept connections
  1. Use DBeaver to connect to the databases

    • Connection to PostgreSQL:
      • host: localhost
      • database: postgres
      • port: 5438
      • username: postgres
      • password: postgres
    • Connection to IRIS:
      • host: localhost
      • database: user
      • port: 1972
      • username: _SYSTEM
      • password: SYS
  2. SQLgateway is installed during Docker build and the required
    jdbcdriver for Linux is included in this repo
    In order to make this demo faster, size of tables to migrate have been shrinked a bit.

How to test

All migration actions can be executed directly from SMP.

  1. Verify the gateway connection in
    SMP> Administration> Configuraation >Connectivity >SqlGateway_Configuration

    • To test Connection click edit
    • and Test Connection
    • verify Connection successful
    • Be patient at this point. Postgres Containers sometimes take quite some time to talk to you.
      wait a little bit, reload the page in browser and try the test again.
  2. Identifying the source tables. In SMP > Change to Namespace USER
    then step to SMP >Explorers >SQL >Wizards > Data Migration

  3. Set required import parameters

  • Destination Namespace
  • Type = TABLE
  • Gateway = postgres ; now the first connection is established and you select
  • Schema = public
  • Tables to migrate = all
  1. Identify target but change schema to be OEX compatible from public to dc_public
  • don't forget to click change all
  • we migrate Definitions and Data so both sides are selected
  1. Skipping special setting we use defaults we start the task in background

  2. Now we check the results and see everything was working without Errors
    You might see errors if tables depend on content not yet migrated.
    And wait for completions until the status shows Done

  3. We terminate the Migration Wizzard and return to normal table view filtered by dc*
    All 8 tables are visible and show meaningful columns

  4. Selecting a table and clicking on OpenTable shows resonable contents

  5. A look into the related generated Class Defnitions confirms the result and successful completion.

Article on DC

Demo Server SMP
Demo Server WebTerminal

Code Quality this is all setup no code involved

Rating
5 (1)
Category
Technology Example
Works with
InterSystems IRIS
Tags
Info
Version
0.2.0
Last updated
2022-05-15
Repository
Open
Documentation
Open
License
Link