Migrating from MySQL to Postgres and vice versa

Owned by maimoona.kausar
Last updated: Jul 11, 2015
4 min read

OpenSRP inherited the postgres backend but OpenMRS recommends MySQL, hence, to reduce complexity of code and application setup process OpenSRP is ported to MySQL. The pull request entails the changes made to code for making the switching between MySQL and Postgres possible. It is a result of the issue created on github for making it part of OpenSRP V2. The OpenSRP features are still compatible with postgres and could be migrated back to postgres anytime with little effort of just changing few settings. Note that you still needs MySQL for OpenMRS setup i.e. only OpenSRP quartz and reporting backend could be migrated to postgres.

MySQL to Postgres:

1) Open opensrp-server pom file and uncomment the postgres dependency in org.codehaus.mojo:sql-maven-plugin and comment out (or remove) the mysql:mysql-connector-java dependency

 

 

2) Open opensrp.properties file in opensrp-server/assets/config and make following changes in properties listed or image below

  •  jdbc.backend=postgres
  •  jdbc.driverClassName=org.postgresql.Driver
  •  change username and password according to your DBMS credentials from settings
    •    jdbc.username=postgres
    •    jdbc.password=postgrespwd
  •  jdbc.url-wo-db=jdbc:postgresql://localhost:5432 (change server url and port according to your DBMS settings)
  •  hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

 The final database settings would look like image below

 

3) Now open quartz.properties file in opensrp-web/src/main/resources and make changes as follows

  •  org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
  •  org.quartz.dataSource.motechDS.driver = org.postgresql.Driver
  •  org.quartz.dataSource.motechDS.URL = jdbc:postgresql://localhost:5432/motechquartz (change server url and port according to your DBMS settings)
  •  change username and password according to your DBMS credentials from settings
    •    org.quartz.dataSource.motechDS.user = postgres
    •    org.quartz.dataSource.motechDS.password = postgrespwd

 

4) Open maven.properties in opensrp-server/build and change settings as below

  •  db.reporting=opensrp

 

5) Open opensrp-server/pom file and replace the executions between comments with following executions

<!-- Database init scripts for DBMS specific configuration -->
<execution>
    <id>drop-create-relational-db</id>
    <phase>pre-integration-test</phase>
    <goals>
        <goal>execute</goal>
    </goals>
    <configuration>
        <url>${jdbc.url-wo-db}</url>
        <autocommit>true</autocommit>
        <sqlCommand>
        drop database if exists ${db.quartz};
        drop database if exists ${db.reporting};
        create database ${db.quartz};
        create database ${db.reporting};
        </sqlCommand>
        <onError>abort</onError>
   </configuration>
</execution>
<execution>
    <id>create-opensrp-schemas</id>
    <phase>pre-integration-test</phase>
    <goals>
        <goal>execute</goal>
    </goals>
    <configuration>
        <url>${jdbc.url-wo-db}/${db.reporting}</url>
        <autocommit>true</autocommit>
        <sqlCommand>
        CREATE SCHEMA ${db.reporting.report} ;
        CREATE SCHEMA ${db.reporting.anm} ;
        </sqlCommand>
        <onError>abort</onError>
    </configuration>
</execution>
<!-- END: Database init scripts for DBMS specific configuration -->

Postgres to MySQL

1) Open opensrp-server pom file and uncomment the mysql dependency in org.codehaus.mojo:sql-maven-plugin and comment out (or remove) the postgres dependency

 

 

2) Open opensrp.properties file in opensrp-server/assets/config and make following changes in properties listed or image below

  •  jdbc.backend=mysql
  •  jdbc.driverClassName=com.mysql.jdbc.Driver
  •  change username and password according to your DBMS credentials from settings
    •    jdbc.username=root
    •    jdbc.password=mysqlpwd
  •  jdbc.url-wo-db=mysql://localhost:3306 (change server url and port according to your DBMS settings)
  •  hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

 The final database settings would look like image below

 

3) Now open quartz.properties file in opensrp-web/src/main/resources and make changes as follows

  • org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
  • org.quartz.dataSource.motechDS.driver = com.mysql.jdbc.Driver
  • org.quartz.dataSource.motechDS.URL = jdbc:mysql://localhost:3306/motechquartz (change server url and port according to your DBMS settings)
  • org.quartz.dataSource.motechDS.user = root
  • org.quartz.dataSource.motechDS.password = mysqlpwd

 

4) Open maven.properties in opensrp-server/build and change setting below (removing the opensrp value here i.e. setting db name to empty is mandatory otherwise project does not build successfully). NOTE: Donot remove setting.

  •  db.reporting=

 

5) Open opensrp-server/pom file and replace the executions between comments with following executions

<!-- Database init scripts for DBMS specific configuration -->
<execution>
    <id>drop-create-relational-db</id>
    <phase>pre-integration-test</phase>
    <goals>
        <goal>execute</goal>
    </goals>
    <configuration>
        <url>${jdbc.url-wo-db}</url>
        <autocommit>true</autocommit>
        <sqlCommand>
        drop database if exists ${db.quartz};
        drop database if exists ${db.reporting.anm};
        drop database if exists ${db.reporting.report};
        create database ${db.quartz};
        create database ${db.reporting.anm};
        create database ${db.reporting.report};
        </sqlCommand>
        <onError>abort</onError>
    </configuration>
</execution>
<!-- END: Database init scripts for DBMS specific configuration -->