英文:
pgloader - How to import a longblob as oid?
问题
"In a nutshell"部分：
如何使用pgloader将MySQL中的longblob迁移到Postgres，以便在列被注释为@Lob和@Basic(fetch= FetchType.LAZY)的情况下，使Hibernate正常工作？
"Full story"部分：
所以我正在尝试将MySQL数据库迁移到Postgres。现在我正在尝试正确地迁移这张表：
我的当前pgloader脚本相当简单：
LOAD DATABASEFROM mysql://foo:bar@localhost:3306/foobarINTO postgresql://foo:bar@localhost:5432/foobarCASTtype int to integer drop typemod,type bigint with extra auto_increment to bigserial drop typemod,type bigint to bigint drop typemodALTER TABLE NAMES MATCHING 'User' RENAME TO 'users'ALTER TABLE NAMES MATCHING ~/./ SET SCHEMA 'public';
这足以加载数据并使外键正常工作。
Postgres表如下所示：
但是，File是一个Java实体，其内容被注释为@Lob：
@Entity@Inheritance(strategy= InheritanceType.JOINED)public class File extends BaseEntity {@NotNullprivate String name;@Column@Size(max = 4096)private String description;@NotNullprivate String mimeType;@Lob@Basic(fetch= FetchType.LAZY)private transient byte[] content;...}
这就是为什么应用程序在连接到迁移后的数据库时出错的原因：
Schema-validation: wrong column type encountered in column [content] in table [File];found [bytea (Types#BINARY)], but expecting [oid (Types#BLOB)]
我如何使这个迁移工作？
我尝试设置
spring.jpa.properties.hibernate.jdbc.use_streams_for_binary=false
如https://stackoverflow.com/questions/3677380/proper-hibernate-annotation-for-byte中建议的那样，但没有任何作用。
英文:
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 DATABASEFROM mysql://foo:bar@localhost:3306/foobarINTO postgresql://foo:bar@localhost:5432/foobarCASTtype int to integer drop typemod,type bigint with extra auto_increment to bigserial drop typemod,type bigint to bigint drop typemodALTER 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 {@NotNullprivate String name;@Column@Size(max = 4096)private String description;@NotNullprivate 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 https://stackoverflow.com/questions/3677380/proper-hibernate-annotation-for-byte but that didn't do anything.
答案1
得分: 2
Here is the translated content:
嗯...我猜我可以按照https://stackoverflow.com/questions/55006781/migrate-postgresql-text-bytea-column-to-large-object中建议的方法，在事后创建数据块。
这意味着迁移脚本将得到扩展：
从mysql://foo:bar@localhost:3306/foobar加载数据库到postgresql://foo:bar@localhost:5432/foobar转换将类型int转换为integer并删除typemod，将带有额外自动增量的类型bigint转换为bigserial并删除typemod，将类型bigint转换为bigint并删除typemod将匹配名称'User'的表重命名为'users'将匹配正则表达式~/./的表设置为模式'public'加载后执行$$将表file的列content重命名为content_bytes;$$，$$向表file添加列content OID;$$，$$更新file表，设置content = lo_from_bytea(0，content_bytes::bytea)，content_bytes = NULL;$$，$$删除表file的列content_bytes$$;
英文:
Hm ... I guess I can just create blobs after the fact, as suggested by https://stackoverflow.com/questions/55006781/migrate-postgresql-text-bytea-column-to-large-object
Meaning the migration script will get an extension:
LOAD DATABASEFROM mysql://foo:bar@localhost:3306/foobarINTO postgresql://foo:bar@localhost:5432/foobarCASTtype int to integer drop typemod,type bigint with extra auto_increment to bigserial drop typemod,type bigint to bigint drop typemodALTER 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 SETcontent = lo_from_bytea(0, content_bytes::bytea),content_bytes = NULL;$$,$$ALTER TABLE file DROP COLUMN content_bytes$$;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论