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?
CodePudding user response：
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/
CodePudding user response：
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.