openmrs should support postgresql

Tags: #<Tag:0x00007f01bb5ba6f8> #<Tag:0x00007f01bb5ba630> #<Tag:0x00007f01bb5ba428> #<Tag:0x00007f01bb5ba360>

No, I haven’t uptill now. Liquibase is new for me so I am going through it but I will as early as possible and share my findings.

I am trying to run openmrs on postgresql but am getting error.

I have done the following:

  1. Added the maven dependency for postgresql.

  2. Changed the hibernate configurations for postgresql( driver_class, url, dialect ) in /api/src/main/resources/hibernate.default.properties of openmrs-core.

3.I am deploying openmrs on jetty server using mvn jetty:run

4.I have given the url for database connection as >

jdbc:postgresql://localhost:5432/@DBNAME@?autoReconnect=true&sessionVariables=default_storage_engine=PostgreSQL&useUnicode=true&characterEncoding=UTF-8

After filling out the various forms I am getting this error at user creation stage. It appears that there is some syntax difference in create user queries in mysql and postgresql. I have already changed hibernate configs but still this error is coming :

Can you share the server side log via pastebin.com?

I could not find any openmrs.log in .OpenMRS directory. Directory has been created but its empty.

But going through the code base I figured out what the problem is.

  1. There are if else conditions for create databases query for postgres( https://github.com/openmrs/openmrs-core/blob/924526ea581ab613e709e3c078d06c166aacd124/web/src/main/java/org/openmrs/web/filter/initialization/InitializationFilter.java#L1386 ) and mysql but no conditions for create username or granting privileges in InitializationFilter.java. Is there any particular reason why this is the case ?

  2. I am planning to add the conditions for other queries accordingly to move further.

I printed the out the queries that are getting executed uptill now:

create database “openmrs” encoding 'utf8’

drop user "openmrs_user"

create user “openmrs_user” with password 'v#PHAH91XECW’

GRANT ALL PRIVILEGES ON DATABASE “openmrs” TO "openmrs_user"

Currently, I am facing the above problem. Here is a link to the logs from console: https://pastebin.com/ZHDRVxPQ

The previous issue is resolved. Then there were problems related to granting privileges to created user in postgresql. It appears that the query GRANT ALL PRIVILEGES ON DATABASE “openmrs” TO “openmrs_user” does not actually grant all privileges. For instance privileges related to Triggers that are required for changesets in liqibase-core.xml file were not granted. So I had to make created user superuser. Also, I am making use of PostgreSQL 11 so I had to change the maven dependency for latest drivers to

  	<dependency>
  	    <groupId>org.postgresql</groupId>
  	    <artifactId>postgresql</artifactId>
  	    <version>42.2.10</version>
  	    <scope>runtime</scope>
  	</dependency>

Now I am struck at execution of changesets in liquibase-update-to-latest.xml. Here is the link to the logs: https://pastebin.com/NapvVGv3

Any help is welcomed

Did you get a chance of looking at this https://wiki.openmrs.org/display/docs/Database+Update+Conventions?

Thanks for the response. I will go through it now.

I have tried by changing driver versions and also by downgrading from PostgreSQL 11 to 9.6 and went through the liquibase docs but still this error persists.

I am not able to figure out what I am doing wrong. Any suggestion is welcomed :neutral_face:

Sorry about it. Kindly share the error log using pastebin

Here are the logs: https://pastebin.com/NapvVGv3 . I have pasted the screenshot and explained the problem I am facing in my previous comment openmrs should support postgresql.

Thanks again.

I am preparing a proposal for GSOC 2020 on the project “OpenMRS should run on PostgreSQL” .

Objectives as mentioned in Project Description include:

  • OpenMRS Platform successfully runs on PostgreSQL

  • Create a CI build which breaks when liquibase changesets fail to run on PostgreSQL

  • Make changes as necessary, to the references application modules to ensure that they run on top of PostgreSQL.

I am having a doubt in 2nd task. The current CI build executes the script

mvn clean install --batch-mode

So if there will be any failure during the build process that might include failures in running liquibase change sets as well, the CI build will break whether it be MySQL or PostgreSQL. So I am not able to understand clearly what the second task is supposed to mean.

Pardon me If I am saying something wrong. I am completely new to CI.

cc. @dkayiwa @jwnasambu

Am glad you tried out the installation process where in the first time setup wizard you pointed it to a postgresql install instead of mysql and as expected it failed because the liquibase changesets don’t support postgresql.You should have identified some of the errors up to this point and that what is expected to happen on task 2 mainly on PostgreSQL but not MySQL because we are transitting from MySQL to PostgreSQL.

cc @dkayiwa

1 Like

Does this mean that we don’t need to change the current CI build https://github.com/openmrs/openmrs-core/blob/master/.travis.yml and in task 2 also we need to focus on making liquibase changeSets work for PostgreSQL ?

In the talk thread given in issue description, Support For PostgreSQL , I found that we need to add CI plans so that the changes we made are constantly tested and if this breaks, we know quickly.

cc. @dkayiwa @jwnasambu

1 Like

In terms of CI, we only use Travis to run “unit” tests rather than tests against a live MySQL database. What we would need is a new CI plan on the OpenMRS Bamboo server.

1 Like

Thanks @ibacher

So what we are planning is to create a CI plan for Bamboo server ( shifting from Travis ) and run the unit tests against a live PostgreSQL database in this case rather then using H2 database that is currently being used ?

cc. @jwnasambu @dkayiwa @ibacher

Yes! This is because the propietary SQL extensions used the H2 database mainly used for running unit tests may fail. Though I would like you to differentiate between the CI plan for Bamboo server and Travis CI.

1 Like

Travis is easily integtated with github but for making use of Bamboo Server for continuous integration would involve :

  • running the tests at the Bamboo Server

  • generating the reports at Bamboo Server

  • Writing a script that continuously checks for status of build and informs github whether the build is pending or not.

  • Sending the build reports back to github as a final step.

This is the distinction I have found between Bamboo and Travis CI. Thanks @jwnasambu and @ibacher . Now I am able to understand what needs to be done. :smiley:

Am glad to hear that. I wish you the best in your application process.

1 Like