Migrate from H2 to PostgreSQL

Question

I need to replace H2 with PostgreSQL at the WSO2 API Manager. Since there is currently data saved on H2, I need to pass it to PostgreSQL.

I found the command

SCRIPT TO 'dump.sql'

to export the data to .sql files, but I could not use it because I was not given the credentials to access the database, so I had to retrieve the data from the .mv.db files that H2 generates. On those files the data is not encrypted, but the password obviously is. To export the data to .sql files I used the command

java -cp h2-*.jar org.h2.tools.Recover -dir file_path -db file_name.

The .sql files are generated correctly, but when I try to import them into PostgreSQL with the command

psql -U db_user db_name < dump_name.sql

numerous syntax errors come up, probably due to the imcompatibility of H2 and PostgreSQL dialects. Is there a way to export the data so that it can then be imported into PostgreSQL? Alternatively, would there be an alternative way to migrate the data?

Take a look at this answer stackoverflow.com/a/74717068/2627018 — ycr
– ycr, Commented Dec 12, 2022 at 12:23

Joy Rathnayake · Accepted Answer · 2022-12-12 12:03:08Z

This is changing the database vendor and we don't support such use cases. There are different scripts in the /[PRODUCT_HOME]/dbscripts folder and you need to setup the target database (in your case PostgreSQL) using the correct scripts. This is due to the nature of differences between different database vendors. The datatypes and schema are different from one database vendor to another.

The correct approach is to go through the migration. You can setup a new environment with PostgreSQL and use a 3rd-party tool or a tool provided by the database vendor to migrate data from H2 to ProstgreSQL. There is no straightforward method to change the database from H2 to PostgreSQL.

For more information on the product migration - https://apim.docs.wso2.com/en/latest/install-and-setup/upgrading-wso2-api-manager/upgrading-guidelines/

Lakshitha · Accepted Answer · 2022-12-12 12:05:18Z

1

WSO2 does not have any scripts or tools for cross-db migrations. However, you can use the API controller[1] to migrate APIs, Applications from the previous environment with H2 DB to a new one with PostgreSQL.

[1] - https://apim.docs.wso2.com/en/latest/install-and-setup/setup/api-controller/getting-started-with-wso2-api-controller/

answered Dec 12, 2022 at 12:05

Lakshitha

1,0311 gold badge7 silver badges16 bronze badges

Collectives™ on Stack Overflow

Migrate from H2 to PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related