topic Re: How to change the custom H2 DB in alresco maven project into Postgres? in Alfresco Archive

How to change the custom H2 DB in alresco maven project into Postgres?

mbel — Tue, 27 Dec 2016 09:59:52 GMT

Hello,I have installed Alfresco and I have running Postgres DB.I have also generated new maven alfresco-amp-archetype project.By default it uses H2 DB, however I am trying to connect to the Postgres DB which is created after the installation of Alfresco on my localmachine.I added the following data

Re: How to change the custom H2 DB in alresco maven project into Postgres?

afaust — Tue, 27 Dec 2016 11:00:59 GMT

It appears you have already successfully switched from H2 to PostgreSQL, but now have run into a secondary issue. The PostgreSQL database seems to have already been partially initialised by one startup / test attempt but stopped somewhere before completion. Now Alfresco detects this and stops, since it can't determine what is already there and what is missing. The best way to proceed would be to drop + recreate the database in PostgreSQL and start your project again.

These kind of "DB management" issues are why Alfresco SDK uses H2 by default. It is easier to just run a "mvn clean -P purge" and then the tests again with a clean slate, than to require the user to manage an external database server.

Re: How to change the custom H2 DB in alresco maven project into Postgres?

mbel — Tue, 27 Dec 2016 16:19:15 GMT

I did so, however there are some other conflicts

2016-12-27 17:47:37,387 ERROR [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema auto-update failed
org.postgresql.util.PSQLException: ERROR: column "was_executed" is of type boolean but expression is of type integer
Hint: You will need to rewrite or cast the expression.
Position: 363

I think I changed the type in the sql script , but again the above exception appears

is there any chance not to run these scripts?

Im not sure how all that is working, but is there another way of solving this issue?

Re: How to change the custom H2 DB in alresco maven project into Postgres?

afaust — Tue, 27 Dec 2016 16:38:31 GMT

Why do you want to connect your Alfresco AMP project to the PostgreSQL database in the first place? I can think of no real reason to do so and the H2 database works just fine for local builds + tests. The Alfresco AMP project also is separate from your local Alfresco installation in that the former is only used for the build of customisations. Once you install the resulting AMP file into your Alfresco installation it will automatically use your PostgreSQL instance.

Also, you should normally not change anything in any SQL script. Alfresco already provides PostgreSQL compatible SQL scripts out-of-the-box. If there is an error like this it likely means that there is either an incompatibility with the version of the database or the wrong scripts are being used in the first place.

Re: How to change the custom H2 DB in alresco maven project into Postgres?

mbel — Wed, 28 Dec 2016 08:26:24 GMT

The one reason is because I want to debug the data which comes from the Alfresco installation( I have installed some additional things there), in order to test it I have to install my amp there, yes, but I will not be in debugging mode.

The second reason is that since my project is simple amp, I don't have share ui and cannot create sites for testing.

In general, I have changed the port for the Alfresco installation to 8081 in order not to have any conflicts when I run my project on 8080.

Re: How to change the custom H2 DB in alresco maven project into Postgres?

afaust — Wed, 28 Dec 2016 08:47:58 GMT

You can configure your Tomcat so that you can debug it: Tomcat Wiki - Developing

Re: How to change the custom H2 DB in alresco maven project into Postgres?

yogeshpj — Wed, 28 Dec 2016 15:31:31 GMT

If debugging is the only reason for changing DB from H2 to PostgreSQL, then I believe you don't have to do it.

Use mvndebug command to debug your alfresco-amp-archetype project.

I am not sure which version of alfresco maven sdk are you using but you can refer this blog to debug IT Tips: How to remote debug Java code using Alfresco SDK 2.1 .

Re: How to change the custom H2 DB in alresco maven project into Postgres?

mbel — Thu, 29 Dec 2016 13:39:21 GMT

Well, In general I know how to debug my project..

The problem is that I want to have some data (sites) in order to test.

1st I have them in Postgres DB which is used by my alfresco installation.

2nd If I cannot connect to it then I will need to connect to Share UI somehow when Im running the project though my IDE in order to be able to create Sites properly(through the webscript).

Re: How to change the custom H2 DB in alresco maven project into Postgres?

afaust — Thu, 29 Dec 2016 18:02:35 GMT

Having base data in order to test is a matter of bootstrapping the content either by using a module-based startup component or by importing a prepared content bundle (ACP) via an action. There is no need to try some kind of hacky access to an external database for this, especially since such a hack may disrupt your existing database.

Re: How to change the custom H2 DB in alresco maven project into Postgres?

mohit — Fri, 17 Mar 2017 00:29:49 GMT

Well, this blog post explains about how to get activiti working with postgres DB. See, if this helps.