Transition H2 data to PostgreSQL

Question

I am doing a transition from H2 database to PostgreSQL. As I found out - the SQL dump which is created when doing SCRIPT TO command on H2 contains several inappropriate constructions:

Unicode data is wrapped with function STRINGDECODE('unicode-data')
Binary & Blob data is escaped as X'binary-data' - probably hex format (Used for storing images and files directly in a database table

I managed to change my exported SQL so it is compatible with Postgres as follows:

Unicode data is wrapped with E'unicode-data'
Binary & Blob data is escaped as E'\\xbinary-data' - should be imported in 'bytea' column in Postgres

Import of data passes as it should.

I configured Hibernate to use PostgreSQL dialect, where default binary & blob type of Postgres is changed to 'bytea':

public class PostgreDialectFix extends PostgreSQLDialect {

    /**
     * Creates a new instance.
     */
    public PostgreDialectFix() {
        super();

        registerColumnType(Types.BLOB, "bytea");                    
        registerColumnType(Types.LONGVARBINARY, "bytea");           
    }
}

I also added the following to my persistense.xml:

<!-- PostgreSQL binary data usage ('false' for oid, 'true' for bytea) -->
<property name="hibernate.jdbc.use_streams_for_binary" value="true"/>

So everything compiles and starts, but when I try to open a page, containing an image and it should be loaded from new PostgreSQL database, the following error occurs:

Caused by: org.postgresql.util.PSQLException: Bad value of type long : \xffd8ffe000104a46494600010200000100010000ffdb004300...
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2971)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2163)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(AbstractJdbc2ResultSet.java:378)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(AbstractJdbc2ResultSet.java:366)
at org.jboss.resource.adapter.jdbc.WrappedResultSet.getBlob(WrappedResultSet.java:386)
at org.hibernate.type.BlobType.get(BlobType.java:80)
at org.hibernate.type.BlobType.nullSafeGet(BlobType.java:134)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2124)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1404)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1332)
at org.hibernate.loader.Loader.getRow(Loader.java:1230)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:603)
at org.hibernate.loader.Loader.doQuery(Loader.java:724)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.loadCollectionBatch(Loader.java:2053)
... 99 more

So my question is: What is going wrong in this transition? I think it is something related to import of data or configuring of Hibernate to read correctly Postgres data columns ...

Thanks in advance!

ADDITIONAL INFO:

Here is Postgres database table definition for FILEDATA:

CREATE TABLE filedata
(
  id bigint NOT NULL,
  version integer,
  created timestamp with time zone,
  createdbymemberid bigint,
  modified timestamp with time zone,
  modifiedbymemberid bigint,
  filedata bytea,
  file_id bigint,
)
WITH (
  OIDS=FALSE
);

FINALLY FOUND THE SOLUTION: In order to import filedata from H2 database into PostgreSQL database you need to use PostgreSQL function decode(filedata-in-hex, 'hex'). Here is a sample import:

INSERT INTO PUBLIC.FILEDATA(ID, VERSION, CREATED, CREATEDBYMEMBERID, MODIFIED, MODIFIEDBYMEMBERID, FILEDATA, FILE_ID) VALUES
(174849, 0, TIMESTAMP '2013-02-11 14:47:57.743', 174833, NULL, NULL, decode('89504e470d0a1a0a0000000d494844520000007b00000050080600000039ac0a8a00000c1a4944415478daed5d095494d7151e136bb3b659baa5494c62dbc4d85a4dba9ee6a49bd6b4f5246dd29ec6a5ae803126261aad5b5ca24489bb6ca2800a0ac822288280ca22b8a251510165515059040541d661bbbddf9b19986146f9c1f90707df77ce3bc30c33ef9f79dfbbefdefbdebdf7d790c47d038d1c0249b684245b42922d21c99690644b48b22524d912926c0949b684245be2be203bb9289b3cd213e9d3c3c1f49ffd...', 'hex'), 174848);

After that import is correctly insert into database as bytea and you can load it correctly!

Could you search for this value in the script, and then post the table definition and the insert statement that fails? That way it's much easier to see what's wrong. — Thomas Mueller
– Thomas Mueller, Commented Apr 9, 2013 at 5:14
By the way, there are a few tools that could help migrating data, for example the SQuirreL DB Copy Plugin - there are more such tools actually. — Thomas Mueller
– Thomas Mueller, Commented Apr 9, 2013 at 5:16
Thanks Thomas! I updated the post with sample of requested info.. Btw I tried using SQuirrel DB Copy Plugin, but no succeed due to big amount of data to copy.. So the only one show-stopper are those binary columns and their reading.. :) — MykoB
– MykoB, Commented Apr 9, 2013 at 7:45
Are you certain you have configured Hibernate to use your special dialect? — Andrew Lazarus
– Andrew Lazarus, Commented Apr 9, 2013 at 16:11

MykoB · Accepted Answer · 2013-04-26 13:15:50Z

4

Resolved using PostgreSQL's function decode(). Answer and sample code can be found in question above :)

answered Apr 26, 2013 at 13:15

MykoB

1841 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transition H2 data to PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related