Support For PostgreSQL

Development
21

these are the current changes needed on top of the latest 1.11.x to install the openmrs-core on Postgresql 9.3:

diff --git a/api/src/main/resources/liquibase-update-to-latest.xml b/api/src/main/resources/liquibase-update-to-latest.xml
index 2e5aab2..62f5998 100644
--- a/api/src/main/resources/liquibase-update-to-latest.xml
+++ b/api/src/main/resources/liquibase-update-to-latest.xml
@@ -7575,7 +7575,7 @@
             <column name="java_class_name" value="org.openmrs.TestOrder" />
             <column name="creator" valueNumeric="1" />
             <column name="date_created" valueDate="2014-03-09" />
-            <column name="retired" valueNumeric="0" />
+            <column name="retired" valueBoolean="false" />
             <column name="uuid" value="52a447d3-a64a-11e3-9aeb-50e549534c5e" />
         </insert>
     </changeSet>
@@ -8313,6 +8313,10 @@
             <column name="date_created" valueDate="2014-03-06"/>
             <column name="retired" valueBoolean="false"/>
         </insert>
+               <!-- Update postgres sequence correctly as it is incorrect after previous inserts with explicit concept_class_id's -->
+               <modifySql dbms="postgresql">
+                       <prepend value="SELECT setval('concept_class_concept_class_id_seq', (SELECT MAX(concept_class_id) FROM concept_class)+1);"/>
+               </modifySql>
     </changeSet>
 
     <changeSet id="20140304816-TRUNK-4139" author="Akshika">

I just briefly tested in the UI. Was able to login, create a patient :slight_smile:

I have installed the OpenMRS without demo data. This is the next place I would look for issues.

How should we proceed? Is anybody else also going to test as well? Should I create a PR, if I get it to install also without the demo data?

2 Likes
22

Definitely a good idea

23

Hi all, I am in the process of postgresql support for openmrs-core.

I am tweaking few files like openmrs-core/api/src/main/resources/hibernate.default.properties openmrs-core/web/src/main/java/org/openmrs/web/filter/initialization/InitializationFilter.java openmrs-core/web/src/main/java/org/openmrs/web/filter/initialization/InitializationWizardModel.java

I am able to do following steps (which involves syntax changes for postgres)

  • Able to create Databse
  • Create User
  • Grant All permissions

Now I am trying to fix liquibase related errors. If anyone tried already, please let me know the changes ( I am newbie to liquibase)

2 Likes
24

Hi Everyone. I know this is an old post and I hope it’s fine that I kind of refloat it.

The thing is that I’ve been trying to run openmrs reference application with a postgres db hosted in the cloud, but this cloud service doesn’t allow to create extensions in the public schema, but in another one, so these two changesets in liquibase-update-to-latest-2.4.x.xml (openmrs-core) fail.

<changeSet id="20200604-soundex_extension" author="aman" dbms="postgresql">
        <comment> Soundex extension for PostgreSQL</comment>
        <sql> CREATE EXTENSION IF NOT EXISTS fuzzystrmatch SCHEMA public;</sql>
    </changeSet>
    
    <changeSet id="20200715-uuid_ossp_extension" author="aman" dbms="postgresql">
        <comment> Extension to use UUID functions with PostgreSQL and creating an alias similar to MySQL</comment>
        <sql>
        	CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA public;
        	CREATE FUNCTION UUID() RETURNS UUID LANGUAGE SQL AS $$ SELECT uuid_generate_v1() $$;
        </sql>
    </changeSet>

By removing the SCHEMA public bit doesn’t seem to help, because even though then the functions are created in the proper schema, then the CREATE FUNCTION UUID() is not executed, I have no idea why. The search path for the database admin user already contains both public and the extensions schemas, but I don’t know why, I can’t make this work only with the liquibase scripts.

The only way I found to make this work is by manually adding the function in the database prior to starting an empty reference application, and even then, the UUID() alias is not working because some module wasn’t able to use the underlying uuid_generate_v1() function, which is also in the extensions schema.

This is a mistery to me, because as I said, the user has both schemas in the search path, but I can see in the openmrs’ logs that the search path is being set to only public somewhere, I don’t know where. My clumsy solution was executing this manually before starting the reference application:

CREATE FUNCTION public.uuid_generate_v1() RETURNS UUID LANGUAGE SQL AS $$ SELECT extension_schema.uuid_generate_v1() $$;
CREATE FUNCTION public.UUID() RETURNS UUID LANGUAGE SQL AS $$ SELECT public.uuid_generate_v1() $$;

In conclusion, I think openmrs is not prepared to use separate schemas in postgres, or I can’t make it work properly. Does anyone see a better solution to this?

Thanks a lot!