PostgreSQL -> H2 data migrations

[john...@gmail.com]

Hello.
I like H2 and want to switch my tests database from postgresql to H2.
Q: How I may make data migration from PostgreSQL to H2 ?
Thanks in advance.

[Kerry Sainsbury]

sql-workbench has a reasonable data migration tool that might help: http://www.sql-workbench.net/manual/data-pumper.html

Cheers

Kerry

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

> I like H2 and want to switch my tests database from postgresql to H2.
> Q: How I may make data migration from PostgreSQL to H2 ?

If it's a "one time" operation, then you could use a tool such as
"SQuirreL DB Copy Plugin". I haven't used the tool myself but from
what I read it should work. There are other tools, see also
http://h2database.com/html/links.html

Another option is to create "linked tables" within H2 and copy the
data from there (using INSERT INTO ... SELECT * FROM LINKED_... or
CREATE TABLE ... AS SELECT * FROM LINKED_...). See also
http://h2database.com/html/grammar.html#create_linked_table

Regards,
Thomas

[Flaz Fourteen]

I've tried several free and trial versions of tools (I would like to convert the whole database into binary files of H2 database or into H2 SQL dump). That tools could only do partial job (for example, using RazorSQL I converted only queries for creating tables, but this tool hangs up when I tried to convert multiple tables).

I my particular case database contains about 5 tables. So I obtained queries for creating tables using RazorSQL. Then I dumped Postgree database using standard pg_dump tool (with --column-inserts options) and deleted from this dump everything except INSERT statements. And I combined CREATE queries (produced by RazorSQL) and INSERT statements into one script. This script performs correctly, so I can read my H2 database using JPA persistence layer.