How to migrate existing MS SQL database to PostgreSQL database (from ATLAS version 2.7.0 to 2.11.1)

GameDeveloper · August 30, 2022, 9:36am

Hello,

We are currently using ATLAS version 2.7.0 with Microsoft SQL Server databases, and are trying to migrate it to the latest version 2.11.1.

Our questions are:

How do we migrate the existing tables from Microsoft SQL Server to PostgreSQL server?
What do we do about the differing tables between old ATLAS version 2.7.0 and the latest version 2.11.1?

Thank you!

Chris_Knoll · August 30, 2022, 2:33pm

I would do the following:

Migrate your MSSql database from sql server to postgreSQL using the tools suggested in this search. I read through a few of the suggestions, and it seems that there’s a process of exporting the schema from MSSQL using sql mangment studio and then running it through a tool to create the DDL for postgresql. This should be a straight-forward task because we do not leverage any special MS-SQL specific functions like stored procedures or triggers. You will need to migrate the webAPI schema, including tables, views and sequences.
Re-build WebAPI but configure it to use PostgreSQL (described in the installation docs) and point it to your 2.7.0 database. The goal is to start up a new instance of WebAPI that’s pointing to the 2.7.0 version of your current data but using the postgresql configuration.
Once you verify that 2.7.0 is working on postgresql, you can then install 2.11.1 and all the webapi tables will be automatically migrated. So the concern you have in your second point is addressed via migration scripts that will automatically be applied with the 2.11.1 version starts.

I suggest making backups of everything you have so that you don’t lose anything, but since we just us simple DB constructs (tables, views and sequences) so the only thing you want to double check in your migration of schema and data is that the sequences have the correct ‘next’ value (ie: if you have cohort definitions with max id = 375, then your cohort_definition_sequence should show 376 (at least) as the next value. We migrated away from MSSQL auto-number fields in version 2.7, so that should simplify some of the data/schema migration tasks.

GameDeveloper · August 31, 2022, 6:12am

Thank you very much for the detailed information! We’ll try your approach.

GameDeveloper · October 19, 2022, 9:33am

Hello Chris,

I tried your approach and encountered the following error which I don’t know how to solve.
Any help is appreciated!

Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException:
Migration V2.7.2.20190528153600__fix-ir-report-perms.sql failed

SQL State : 23505
Error Code : 0
Message : ERROR: duplicate key value violates unique constraint “permission_unique”
Detail: Key (value)=(ir:DM-2015_to_2018:info::delete) already exists.
Location : db/migration/postgresql/V2.7.2.20190528153600__fix-ir-report-perms.sql (/opt/tomcat/webapps/WebAPI/WEB-INF/classes/db/migration/postgresql/V2.7.2.20190528153600__fix-ir-report-perms.sql)
Line : 3
Statement : INSERT INTO webapi.sec_permission (id, value, for_role_id)
SELECT nextval(‘webapi.sec_permission_id_seq’), REPLACE(CAST(new_perms.val AS VARCHAR(255)), ‘%s’, REPLACE(REPLACE(value, ‘source:’, ‘’), ‘:access’, ‘’)), role_id
FROM webapi.sec_permission sp
JOIN webapi.sec_role_permission srp on sp.id = srp.permission_id
CROSS JOIN (
SELECT 'ir:%s:info::delete’ val
) new_perms
WHERE sp.value LIKE ‘source:%:access’

Chris_Knoll · October 20, 2022, 1:51am

Yes, I’ll look into an update for this migration and let you know.

Chris_Knoll · October 20, 2022, 6:03pm

Would you mind running the below on your WebAPI database and show me the results? For some reason, you’re getting multiple rows for insert into the sec_permission table, maybe because you have multiple roles attaching to the same datasource for ‘access’…

If it is because of multiple roles assigned to access a single datasource, that may lead to the duplicate inserts, but I just wanted to make sure:

REPLACE(CAST(new_perms.val AS VARCHAR(255)), ‘%s’, REPLACE(REPLACE(value, ‘source:’, ‘’), ‘:access’, ‘’)), role_id
FROM webapi.sec_permission sp
JOIN webapi.sec_role_permission srp on sp.id = srp.permission_id
CROSS JOIN (
SELECT 'ir:%s:info::delete’ val
) new_perms
WHERE sp.value LIKE ‘source:%:access’

GameDeveloper · October 21, 2022, 1:55am

Sure! The query returned the following results:

replace	role_id
ir:DM-2015_to_2018:info::delete	1002
ir:discovery-osdhi:info::delete	1003
ir:DM-2015_to_2018:info::delete	1062
ir:discovery-osdhi:info::delete	1062

Chris_Knoll · October 22, 2022, 5:05am

Right, so as you can see, you get role_id 1002 adn 1062 for discovery-ohdsi and dm-2015… So, it will duplicate the inserts. I found a few cases where the migration is using an invalid approach to create the new permissions, so we need to fix those and push a hotfix. I need to reach out to the core devs for some support on this, but we should have some instructions for you to fix your build next week.

GameDeveloper · October 25, 2022, 1:36am

Thank you, looking forward to the instructions!

Chris_Knoll · October 25, 2022, 2:24pm

Ok, can you please find the file: resources\db\migration\postgresql\V2.7.2.20190528153600__fix-ir-report-perms.sql

Replace the contents of that file with the below:

CREATE TEMP TABLE temp_migration (
  from_perm_id int,
  new_value character varying(255)
);

INSERT INTO temp_migration (from_perm_id, new_value)
SELECT sp.id as from_id,
	REPLACE(CAST(new_perms.val AS VARCHAR(255)), '%s', REPLACE(REPLACE(value, 'source:', ''), ':access', '')) as new_value
FROM ${ohdsiSchema}.sec_permission sp
CROSS JOIN (
	SELECT 'ir:%s:info:*:delete' val
) new_perms
WHERE sp.value LIKE 'source:%:access';

INSERT INTO ${ohdsiSchema}.sec_permission (id, value)
SELECT nextval('${ohdsiSchema}.sec_permission_id_seq'), new_value
FROM temp_migration;

INSERT INTO ${ohdsiSchema}.sec_role_permission (id,role_id, permission_id)
SELECT nextval('${ohdsiSchema}.sec_role_permission_sequence'),
  srp.role_id,
  sp.id as permission_id
FROM temp_migration m
JOIN ${ohdsiSchema}.sec_permission sp on m.new_value = sp.value
JOIN ${ohdsiSchema}.sec_role_permission srp on m.from_perm_id = srp.permission_id;

drop table temp_migration;

What this does is: finds the data sources that have been granted access permission, and stores the permission_id for each of those sources. Then inserts a new permission for deleting IR results. Then, it needs to assign roles to this new permission, based on the permission that has access to the data source. This last part is what broke the old implementation: with multiple roles having this permission, the logic of the original query led to duplicate permissions.

I can push this change up to a branch if you prefer to build off of a branch instead of making a change to the file. I’m not sure if you are building off of a master branch or if you are using a release tag…

GameDeveloper · October 26, 2022, 5:44am

I’m using a release tag (2.11.0). Thank you! That error is gone but I’m getting another error:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘CDMResultsService’ defined in file
[/opt/tomcat/webapps/WebAPI/WEB-INF/classes/org/ohdsi/webapi/service/CDMResultsService.class]:
Invocation of init method failed; nested exception is javax.ws.rs.WebApplicationException: HTTP 500 Internal Server Error
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1630)
… 49 more
Caused by: javax.ws.rs.WebApplicationException: HTTP 500 Internal Server Error
at org.ohdsi.webapi.job.JobTemplate.launch(JobTemplate.java:60)
… 68 more
Caused by: org.springframework.dao.DataAccessResourceFailureException: Could not obtain sequence value; nested exception is org.postgresql.util.PSQLException: ERROR: “batch_job_seq” is not a sequence
at org.springframework.jdbc.support.incrementer.AbstractSequenceMaxValueIncrementer.getNextKey(AbstractSequenceMaxValueIncrementer.java:78)
… 80 more
Caused by: org.postgresql.util.PSQLException: ERROR: “batch_job_seq” is not a sequence
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
at org.springframework.jdbc.support.incrementer.AbstractSequenceMaxValueIncrementer.getNextKey(AbstractSequenceMaxValueIncrementer.java:69)
… 109 more

I tried creating the sequence “batch_job_seq” manually using the following SQL:

CREATE SEQUENCE IF NOT EXISTS webapi.batch_job_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE webapi.batch_job_seq
    OWNER TO ohdsi_admin_user;

GRANT ALL ON SEQUENCE webapi.batch_job_seq TO ohdsi_admin_user;

GRANT SELECT, USAGE ON SEQUENCE webapi.batch_job_seq TO ohdsi_app;

But I got the following error message:

NOTICE:  relation "batch_job_seq" already exists, skipping
ERROR:  "batch_job_seq" is not a sequence
SQL state: 42809

Any idea how to fix this?

Chris_Knoll · October 26, 2022, 1:49pm

I’ll look into this, but please avoid making any updates to the db by hand. We want the database migration function to do it’s work, and so if you do anything for it, there will be errors.

In addition, can you look into your database ans see if there is a table, sequence or view called ‘batch_job_seq’. Thanks.

Chris_Knoll · October 26, 2022, 1:58pm

I checked the sourcecode, and in this migration: resources\db\migration\postgresql\V1.0.0.1__schema-create_spring_batch.sql it creates the sequence BATCH_JOB_SEQ:

CREATE SEQUENCE ${ohdsiSchema}.BATCH_JOB_SEQ MAXVALUE 9223372036854775807 NO CYCLE;

In your error message, it says it’s not a sequence, so that makes me believe that there’s something already in your db called ‘batch_job_seq’, but it’s not a sequence.

You can use this information to list objects in your Postgres instance:

GameDeveloper · October 27, 2022, 2:36am

Understood. I will avoid making any DB updates by hand.
Here’s the list of sequences:

"achilles_cache_seq"
"analysis_execution_sequence"
"batch_job_execution_seq_id_seq"
"batch_job_seq_id_seq"
"batch_step_execution_seq_id_seq"
"cdm_cache_seq"
"cohort_sample_sequence"
"cohort_study_cohort_study_id_seq"
"concept_of_interest_id_seq"
"exampleapp_widget_id_seq"
"fe_aggregate_sequence"
"fe_analysis_criteria_sequence"
"fe_analysis_sequence"
"fe_conceptset_sequence"
"generation_cache_sequence"
"heracles_heel_results_id_seq"
"heracles_results_dist_id_seq"
"heracles_results_id_seq"
"ir_analysis_dist_id_seq"
"ir_analysis_result_id_seq"
"ir_analysis_strata_stats_id_seq"
"ir_strata_id_seq"
"penelope_laertes_uni_pivot_id_seq"
"reusable_seq"
"sec_permission_id_seq"
"sec_role_permission_sequence"
"tag_seq"

Here’s the first 20 tables (under webapi schema):

achilles_cache
analysis_generation_info
batch_job_execution
batch_job_execution_context
batch_job_execution_params
batch_job_execution_seq
batch_job_instance
batch_job_seq
batch_step_execution
batch_step_execution_context
batch_step_execution_seq
cc_analysis
cc_cohort
cc_param
cc_strata
cc_strata_conceptset
cca
cca_execution
cca_execution_ext
cdm_cache

Thank you!

GameDeveloper · October 27, 2022, 7:19am

I managed to fix the error “batch_job_seq” is not a sequence. In the old MSSQL database, there’s a table named “batch_job_seq” and after migration (using sqlserver2pgsql), that table was created in PostgreSQL database as “webapi.batch_job_seq” and the sequence for that was created as “webapi.batch_job_seq_id_seq”.

However, I’m now getting the following error:

2022-10-27 15:03:43.154 ERROR taskExecutor-1 org.springframework.batch.core.job.AbstractJob - [] - Encountered fatal error executing job
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is java.lang.IllegalStateException: EntityManagerFactory is closed
	at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431) ~[spring-orm-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:377) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:463) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at com.sun.proxy.$Proxy268.getLastStepExecution(Unknown Source) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
	at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at com.sun.proxy.$Proxy268.getLastStepExecution(Unknown Source) ~[?:?]
	at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:115) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE]
	at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:392) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE]
	at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:135) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE]
	at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306) [spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE]
	at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
	at java.lang.Thread.run(Thread.java:829) [?:?]
Caused by: java.lang.IllegalStateException: EntityManagerFactory is closed
	at org.hibernate.internal.SessionFactoryImpl.validateNotClosed(SessionFactoryImpl.java:530) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
	at org.hibernate.internal.SessionFactoryImpl.createEntityManager(SessionFactoryImpl.java:609) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
	at org.hibernate.internal.SessionFactoryImpl.createEntityManager(SessionFactoryImpl.java:154) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
	at org.springframework.orm.jpa.JpaTransactionManager.createEntityManagerForTransaction(JpaTransactionManager.java:449) ~[spring-orm-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:369) ~[spring-orm-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	... 26 more
2022-10-27 15:03:43.186 INFO taskExecutor-1 org.springframework.batch.core.launch.support.SimpleJobLauncher$1 - [] - Job: [SimpleJob: [name=warming cache: DM-2015_to_2018]] failed unexpectedly and fatally with the following parameters: [{jobName=warming cache: DM-2015_to_2018, source_key=DM-2015_to_2018, source_id=-1, time=1666854215461, jobAuthor=anonymous}]
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is java.lang.IllegalStateException: EntityManagerFactory is closed
	at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431) ~[spring-orm-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:377) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:463) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at com.sun.proxy.$Proxy268.update(Unknown Source) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
	at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at com.sun.proxy.$Proxy268.update(Unknown Source) ~[?:?]
	at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:357) ~[spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE]
	at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.10.RELEASE.jar:3.0.10.RELEASE]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
	at java.lang.Thread.run(Thread.java:829) [?:?]
Caused by: java.lang.IllegalStateException: EntityManagerFactory is closed
	at org.hibernate.internal.SessionFactoryImpl.validateNotClosed(SessionFactoryImpl.java:530) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
	at org.hibernate.internal.SessionFactoryImpl.createEntityManager(SessionFactoryImpl.java:609) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
	at org.hibernate.internal.SessionFactoryImpl.createEntityManager(SessionFactoryImpl.java:154) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
	at org.springframework.orm.jpa.JpaTransactionManager.createEntityManagerForTransaction(JpaTransactionManager.java:449) ~[spring-orm-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:369) ~[spring-orm-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	... 23 more
Exception in thread "taskExecutor-1" org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is java.lang.IllegalStateException: EntityManagerFactory is closed
	at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:377)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:463)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy268.update(Unknown Source)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy268.update(Unknown Source)
	at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:357)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.lang.IllegalStateException: EntityManagerFactory is closed
	at org.hibernate.internal.SessionFactoryImpl.validateNotClosed(SessionFactoryImpl.java:530)
	at org.hibernate.internal.SessionFactoryImpl.createEntityManager(SessionFactoryImpl.java:609)
	at org.hibernate.internal.SessionFactoryImpl.createEntityManager(SessionFactoryImpl.java:154)
	at org.springframework.orm.jpa.JpaTransactionManager.createEntityManagerForTransaction(JpaTransactionManager.java:449)
	at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:369)
	... 23 more

Thank you for your time!

Chris_Knoll · October 27, 2022, 10:10pm

Entity Manager being closed is a consequence of some other error. Something like a connection failed or some permission denied. Can you look around these errors and try to find the specific error that may have information about a specific problem?

Chris_Knoll · October 28, 2022, 3:17pm

I think the better approach for migrating from mssql to postgres would be to install webapi into a blank postgres database, and then transfer the data from mssql to postgres and update the sequences. This would be a great community contribution to script out the data transfer between servers, because it is likely people will be needing to migrate from sqlserver to postgres.

GameDeveloper · October 29, 2022, 2:37pm

Thank you for your suggestion. Unfortunately, I tried this approach and I was only able to transfer mostly permission related tables (e.g. sec_permission, sec_role, …). Maybe I was doing it wrong.

system · June 4, 2024, 5:57pm

This topic is temporarily closed for at least 4 hours due to a large number of community flags.

system · June 9, 2024, 2:10pm

This topic was automatically opened after 4 days.

How to migrate existing MS SQL database to PostgreSQL database (from ATLAS version 2.7.0 to 2.11.1)

Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException: Migration V2.7.2.20190528153600__fix-ir-report-perms.sql failed

Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException:
Migration V2.7.2.20190528153600__fix-ir-report-perms.sql failed