Migration from MySQL to Postgre

(IM Officer) #1

Hello everyone,
we were working on a system usign php, MYSQL and angular 7. We took a decision to convert to dhis2 for multiple reasons.

I installed everything, and even converted our huge database from MySQL syntax into PostgreSQL. But it appears that the database should be implemented, while we’re creating data elements and organization units and groups.

Can we connect data elements to database already exists on PostgreSQL server in someway?

1 Like
(James Omutsani) #2

Hi @im_officer_leb,

Glad to have you in the community! Seems like I haven’t understood your question clearly - also kindly let us know your version of DHIS2, the PostgreSQL version you are running
@bobj - kindly help out here…

Best,
James.

(IM Officer) #3

Hello I am using the newest dhis version, pgAdmin for PostgreSQL. I already have a database with its postgreSQL script. I need to connect data elements to its existing tables.

So if I have a table called individuals, I connect a form to gather info of persons and data will be added to this table.

1 Like
(James Omutsani) #4

Hi @mykbitz - I know you did your Migration of database to new server setup - kindly support our brother @im_officer_leb in configuring it right.

Best,
James.

(IM Officer) #5

Yeah but in my case from MySQL to PostgreSQL.

1 Like
(Calle Hedberg) #6

@im_officer_leb

Your system based on mySQL, PHP, and Angular 7 - is that a system based on the DHIS2 data model (and if yes, which version?) or a different model?

Because if it’s a different system/data model altogether, and since technically converting the database from MySQL to PostgreSQL do not change the data model (tables, indexes, constraints), you won’t be able to use the data in DHIS2.

Trying to design some kind of back-end “conversion layer” between DHIS2 and another data model makes little sense to me - it would be a nightmare to maintain even if you managed to design it in the first place.

So if your legacy data model is different, you need to

  • design the equivalent configuration (meta-data, reports, etc) in DHIS2
  • map the legacy tables & fields to the equivalent meta-data in DHIS2
  • transfer the data

I’m not sure what you mean by “huge” database, but if it is a few hundred GB, I would not recommend using the DHIS2 API for the transfer of data. I would look at ways of inserting the data directly into the DHIS2 database. Which is straightforward in terms of aggregated data and event data, but more difficult with tracker data (you don’t indicate what type of data it is).

As a general approach, I would add the relevant DHIS2 uid values etc to the tables in your original db, and then dump the data out in a format that can be directly imported into the DHIS2 data model.

My 2c worth
Calle

1 Like
(Mike Bitok) #7

Sorry @jomutsani seeing this now. @im_officer_leb were you able to figure it out?

1 Like
(IM Officer) #8

Nope. Nothing at all.

1 Like
(Mike Bitok) #9

Hey @im_officer_leb, If I got this convo right, the source is a DHIS2 instance with a MySQL database?What version of DHIS2 is the source?

1 Like
(IM Officer) #10

Correct. But how can I tell the dhis version I am using ?

1 Like
(James Omutsani) #11

Hi @im_officer_leb,

Click on the active User profile on the top right corner of your screen, then click on the About DHIS2.

about

Then the Version, build and other DHIS2 info will load

Best,
James.

(IM Officer) #12

@mykbitz:

Web API:

Browse it here

Current user:

admin

Version:

2.28

Build revision:

af15a18

Build date:

2017-10-11 06:22

Jasper reports version:

6.3.1

User agent:

Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36

Server date:

2019-04-23 10:26

Last analytics table generation:

Time since last analytics table generation:

Last analytics table runtime:

Environment variable:

DHIS2_HOME

External configuration directory:

C:\Users\Medair-it\dhis2-live2.28\dhis-live\conf

File store provider:

filesystem

Node ID:

Cache provider:

ehcache

Database type:

H2

Database name:

dhis2;AUTO_SERVER=TRUE;DB_CLOSE_ON_EXIT=FALSE

Database user:

sa

Read replica count:

0

Java opts:

Java version:

1.8.0_202

Java vendor:

Oracle Corporation

OS name:

Windows 7

OS architecture:

amd64

OS version:

6.1

Server memory:

Mem Total in JVM: 690 Free in JVM: 453 Max Limit: 1788

CPU cores:

4

1 Like