openmrs should support postgresql

Hello everyone. I am a third year student going through my undergraduate computer engineering programme. I am planning to take the following task for gsoc2020.

I have already gone through the talk thread discussing this issue.Can anyone please provide some resources or a starting point so that I can get started and get more insight into this problem?

Any help is welcomed :slightly_smiling_face:

@dkayiwa @jwnasambu , can you guys give your views?

I am glad you have interest in this project. Did you get a chance of looking at the timeline on this link GSoC 2020: Warm up practices for students? between now and 17th May, Kindly be working on the introduction tickets to increase on your chances to be selected to work on this project at the same time get familiar with how the community operates.

Thanks for your response. I have been working on a few tickets for the past month and it is a great learning experience for me under the guidance of experienced members of OPENMRS. :slightly_smiling_face:

Currently I am going through my University exams, so can’t code till 6 March. So if there were some resources or docs I should go through , that could help in gaining more insight into the problem and preparing the proposal.

Am glad to hear of your contribution just keep doing it. Concerning the the Docs and resources, feel free to google around from different sources. I for one I don’t have a specific one to share right now may be @dkayiwa but with time I will . Success in your exams.

Have you even tried to run OpenMRS on PostgreSQL?

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: INFO 8/3/20 10:56 PM:liquibase: Reading from liquibasechangelogINFO 8/3/20 10: - Pastebin.com

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: INFO 10/3/20 9:48 PM:liquibase: ChangeSet liquibase-schema-only.xml::20120529-22 - Pastebin.com

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