Question 90579 in PostgreSQL Question posted in
The official documentation can be found here.

Migrate from H2 to PostgreSQL

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?

Tags:

2

Answers


  1. 0

    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/

    Login or Signup to reply.
  2. 0

    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/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search