Blob's migration data from Informix to Postgres

Question

I am migrating Informix (11.7) DB to PostgreSQL(9.2). There is only one problem: how to move blob(image) from Informix to PostgreSQL(9.2)? Many thanks in advance.

Answer 1

I did some such conversions between various databases using Jython and JDBC.

All you need is Informix and PostgreSQL JDBC drivers in CLASSPATH.

I have Informix table with BYTE column, and PostgreSQL table with BYTEA column:

-- Informix
CREATE TABLE _blob_test (
  id integer PRIMARY KEY,
  image BYTE
)

-- PostgreSQL
CREATE TABLE _blob_test (
  id integer PRIMARY KEY,
  image BYTEA
)

Then you can use JDBC getObject()/setObject() methods to copy data:

#!/usr/bin/env jython
# -*- coding: utf8 -*-

from java.sql import DriverManager
from java.lang import Class

Class.forName("com.informix.jdbc.IfxDriver")
Class.forName('org.postgresql.Driver')

def copy_table(db_from, db_to):
    col_count = 2
    insert_stmt = db_to.prepareStatement('INSERT INTO _blob_test (id, image) VALUES (?, ?)')
    pstm2 = db_from.createStatement()
    pstm2.setFetchSize(10000)
    rs_in = pstm2.executeQuery('SELECT * FROM _blob_test')
    try:
        batch_buffer = 0
        batch_size = 100
        while (rs_in.next()):
            for i in range(1, col_count + 1):
                insert_stmt.setObject(i, rs_in.getObject(i))
            insert_stmt.addBatch()
            batch_buffer += 1
            if (batch_buffer % batch_size == 0):
                insert_stmt.executeBatch()
                batch_buffer = 0
        if (batch_buffer > 0):
            insert_stmt.executeBatch()
    finally:
        rs_in.close()
        pstm2.close()


db_from = DriverManager.getConnection('jdbc:informix-sqli://informix-test:9088/infdb:informixserver=ol_testifx;DB_LOCALE=pl_PL.CP1250;CLIENT_LOCALE=pl_PL.CP1250;charSet=CP1250', 'informix', '12345')
db_to   = DriverManager.getConnection('jdbc:postgresql://pg-test:5490/pg_test?stringtype=unspecified', 'postgres', '12345')

copy_table(db_from, db_to)

Answer 2

I know the answer is late but this could help other persons looking for a solution.
We have done a similar migration in our company.
To migrate binaries we used a tool called SQL-workbench, we used the data pumper (menu tools). Data pumper allows to copy data from a source DB to a target DB. The DBMSs could be different (in this case Informix and Postgres).
You have to register both drivers in "File > Manage Drivers" and create 2 profiles: A source profile targeting your Informix DB and a target profile targeting your Postgres DB. The profiles will then be selected from Data pumper. If you have several tables to copy at once, please read the documentation about using the function Wbcopy (very useful)