pgloader - How to import a longblob as oid?

Question

In a nutshell

How do you migrate a longblob from MySQL to Postgres using pgloader s.t. Hibernate is happy if the column is annotated @Lob and @Basic(fetch= FetchType.LAZY)?

Full story

So I'm migrating (or trying to, at least) a MySQL DB to postgres. And I'm now trying to move this table correctly:

My current pgloader script is fairly simple:

LOAD DATABASE
    FROM      mysql://foo:bar@localhost:3306/foobar
    INTO postgresql://foo:bar@localhost:5432/foobar

    CAST
        type int to integer drop typemod,
        type bigint with extra auto_increment to bigserial drop typemod,
        type bigint to bigint drop typemod

    ALTER TABLE NAMES MATCHING 'User' RENAME TO 'users'
    ALTER TABLE NAMES MATCHING ~/./ SET SCHEMA 'public'
;

This is sufficient to load the data and have the foreign keys working.

The postgres table looks like this:

The File, however, is a java entity and its content is annotated @Lob:

@Entity
@Inheritance(strategy= InheritanceType.JOINED)
public class File extends BaseEntity {

    @NotNull
    private String name;

    @Column
    @Size(max = 4096)
    private String description;

    @NotNull
    private String mimeType;

    @Lob
    @Basic(fetch= FetchType.LAZY)
    private transient byte[] content;

    ...

}

which is why the application fails to connect to the migrated database with error:

Schema-validation: wrong column type encountered in column [content] in table [File];
                   found [bytea (Types#BINARY)], but expecting [oid (Types#BLOB)]

How do I get this migration to work?

I did try setting

spring.jpa.properties.hibernate.jdbc.use_streams_for_binary=false

as suggested in proper hibernate annotation for byte[] but that didn't do anything.

score 2 · Answer 1 · answered Aug 11 '20 at 14:35

Hm ... I guess I can just create blobs after the fact, as suggested by Migrate PostgreSQL text/bytea column to large object?

Meaning the migration script will get an extension:

LOAD DATABASE
    FROM      mysql://foo:bar@localhost:3306/foobar
    INTO postgresql://foo:bar@localhost:5432/foobar

    CAST
        type int to integer drop typemod,
        type bigint with extra auto_increment to bigserial drop typemod,
        type bigint to bigint drop typemod

    ALTER TABLE NAMES MATCHING 'User' RENAME TO 'users'
    ALTER TABLE NAMES MATCHING ~/./ SET SCHEMA 'public'

    AFTER LOAD DO
        $$
            ALTER TABLE file RENAME COLUMN content TO content_bytes;
        $$,
        $$
            ALTER TABLE file ADD COLUMN content OID;
        $$,
        $$
            UPDATE file SET
                content = lo_from_bytea(0, content_bytes::bytea),
                content_bytes = NULL
            ;
        $$,
        $$
            ALTER TABLE file DROP COLUMN content_bytes
        $$
;

pgloader - How to import a longblob as oid?

1 Answers1

Linked