Problem regarding DHIS2 metadata migration from MySQL to PostgreSQL

Dear Experts

I writ you guys earlier that we are facing problem running DHIS2 version 2.11 with MySQL. So we are trying to migrate to PostgreSQL. With 26000 orgunits and 35 million data value it is really difficult for us to do that. When I am trying to export metadata from MySQL based dhis2 to PostgreSQL based dhis2 (both are version 2.11) I found the following problem:

The orgunit table in mysql have following

But the postgresql have the following

Please see carefully the organisationunitid of ‘Bangladesh’ become 46 after import metadata into postgresql where as the original source in mysql was 1.

Similarly in mysql dataelement table was

it become following in postgres

dataelement 1 become 26424. Similarly in dataelementcategory table 1 become 13, 2 become 26387. And most dangerous is in dataelementcategoryoption where the values are

Also the period table is same.

Why this is happening? Can you please explain.

Regards

Hannan

Hi Hannan,

What you pasted in the email seems like a base64 string of an image. Unless gmail screwed something from the email that you sent, I request that you send logs or long text as attachments, instead of inline email message.

Sequences in postgreSQL are similar to auto-increment ids in MySQL. Their values are generated during the insert statements and will depend on the initial value of the sequence. You can manually set the sequence value (i.e. setval) before you start importing, to ensure that the values start from 1. Either ways, the metadata xml does not have the internal Ids (i.e. the organisationunitid and similar). Those ids are generated and if other organisationunits exist in the system into which you are importing, then you would not like to overwrite these internal ids.

If you are starting with a blank database, why not let DHIS2 2.11 create the blank database schema. Then on empty tables set the sequences to 1 and run the metadata import after that to ensure that they started from 1.

···

Regards,
Saptarshi PURKAYASTHA

My Tech Blog: http://sunnytalkstech.blogspot.com
You Live by CHOICE, Not by CHANCE

On 14 May 2013 08:15, Hannan Khan hannank@gmail.com wrote:

Dear Experts

I writ you guys earlier that we are facing problem running DHIS2 version 2.11 with MySQL. So we are trying to migrate to PostgreSQL. With 26000 orgunits and 35 million data value it is really difficult for us to do that. When I am trying to export metadata from MySQL based dhis2 to PostgreSQL based dhis2 (both are version 2.11) I found the following problem:

The orgunit table in mysql have following

But the postgresql have the following

Please see carefully the organisationunitid of ‘Bangladesh’ become 46 after import metadata into postgresql where as the original source in mysql was 1.

Similarly in mysql dataelement table was

it become following in postgres

dataelement 1 become 26424. Similarly in dataelementcategory table 1 become 13, 2 become 26387. And most dangerous is in dataelementcategoryoption where the values are

Also the period table is same.

Why this is happening? Can you please explain.

Regards

Hannan


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Thanks Saptarshi for solution.

But manually create sequence and alter all table manually is difficult and time consuming job. Is there any other quick solution?

Regards

Hannan

···

On Tue, May 14, 2013 at 2:41 PM, Saptarshi Purkayastha sunbiz@gmail.com wrote:

Hi Hannan,

What you pasted in the email seems like a base64 string of an image. Unless gmail screwed something from the email that you sent, I request that you send logs or long text as attachments, instead of inline email message.

Sequences in postgreSQL are similar to auto-increment ids in MySQL. Their values are generated during the insert statements and will depend on the initial value of the sequence. You can manually set the sequence value (i.e. setval) before you start importing, to ensure that the values start from 1. Either ways, the metadata xml does not have the internal Ids (i.e. the organisationunitid and similar). Those ids are generated and if other organisationunits exist in the system into which you are importing, then you would not like to overwrite these internal ids.

If you are starting with a blank database, why not let DHIS2 2.11 create the blank database schema. Then on empty tables set the sequences to 1 and run the metadata import after that to ensure that they started from 1.


Regards,
Saptarshi PURKAYASTHA

My Tech Blog: http://sunnytalkstech.blogspot.com
You Live by CHOICE, Not by CHANCE

On 14 May 2013 08:15, Hannan Khan hannank@gmail.com wrote:

Dear Experts

I writ you guys earlier that we are facing problem running DHIS2 version 2.11 with MySQL. So we are trying to migrate to PostgreSQL. With 26000 orgunits and 35 million data value it is really difficult for us to do that. When I am trying to export metadata from MySQL based dhis2 to PostgreSQL based dhis2 (both are version 2.11) I found the following problem:

The orgunit table in mysql have following

But the postgresql have the following

Please see carefully the organisationunitid of ‘Bangladesh’ become 46 after import metadata into postgresql where as the original source in mysql was 1.

Similarly in mysql dataelement table was

it become following in postgres

dataelement 1 become 26424. Similarly in dataelementcategory table 1 become 13, 2 become 26387. And most dangerous is in dataelementcategoryoption where the values are

Also the period table is same.

Why this is happening? Can you please explain.

Regards

Hannan


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

This page lists some ideas - http://wiki.postgresql.org/wiki/Fixing_Sequences
Probably with a JDBC program could also do it

···

On Tue, May 14, 2013 at 2:41 PM, Saptarshi Purkayastha sunbiz@gmail.com wrote:

Hi Hannan,

What you pasted in the email seems like a base64 string of an image. Unless gmail screwed something from the email that you sent, I request that you send logs or long text as attachments, instead of inline email message.

Sequences in postgreSQL are similar to auto-increment ids in MySQL. Their values are generated during the insert statements and will depend on the initial value of the sequence. You can manually set the sequence value (i.e. setval) before you start importing, to ensure that the values start from 1. Either ways, the metadata xml does not have the internal Ids (i.e. the organisationunitid and similar). Those ids are generated and if other organisationunits exist in the system into which you are importing, then you would not like to overwrite these internal ids.

If you are starting with a blank database, why not let DHIS2 2.11 create the blank database schema. Then on empty tables set the sequences to 1 and run the metadata import after that to ensure that they started from 1.


Regards,
Saptarshi PURKAYASTHA

My Tech Blog: http://sunnytalkstech.blogspot.com
You Live by CHOICE, Not by CHANCE

On 14 May 2013 08:15, Hannan Khan hannank@gmail.com wrote:

Dear Experts

I writ you guys earlier that we are facing problem running DHIS2 version 2.11 with MySQL. So we are trying to migrate to PostgreSQL. With 26000 orgunits and 35 million data value it is really difficult for us to do that. When I am trying to export metadata from MySQL based dhis2 to PostgreSQL based dhis2 (both are version 2.11) I found the following problem:

The orgunit table in mysql have following

But the postgresql have the following

Please see carefully the organisationunitid of ‘Bangladesh’ become 46 after import metadata into postgresql where as the original source in mysql was 1.

Similarly in mysql dataelement table was

it become following in postgres

dataelement 1 become 26424. Similarly in dataelementcategory table 1 become 13, 2 become 26387. And most dangerous is in dataelementcategoryoption where the values are

Also the period table is same.

Why this is happening? Can you please explain.

Regards

Hannan


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Hi Hannan,

my approach to this would have been to

  • upgrade both versions to 2.11

  • do a full dxf2 meta-data exchange between the systems

  • for the data values use the CSV data value import-export and made sure that you use the start-end dates when exporting to split the files into manageable sizes, e.g. approximately 500 000 records per file.

regards,

Lars

···

On Wed, May 15, 2013 at 11:33 AM, Saptarshi Purkayastha sunbiz@gmail.com wrote:

This page lists some ideas - http://wiki.postgresql.org/wiki/Fixing_Sequences

Probably with a JDBC program could also do it


Regards,

Saptarshi PURKAYASTHA

My Tech Blog: http://sunnytalkstech.blogspot.com
You Live by CHOICE, Not by CHANCE

On 15 May 2013 10:24, Hannan Khan hannank@gmail.com wrote:

Thanks Saptarshi for solution.

But manually create sequence and alter all table manually is difficult and time consuming job. Is there any other quick solution?

Regards

Hannan


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

On Tue, May 14, 2013 at 2:41 PM, Saptarshi Purkayastha sunbiz@gmail.com wrote:

Hi Hannan,

What you pasted in the email seems like a base64 string of an image. Unless gmail screwed something from the email that you sent, I request that you send logs or long text as attachments, instead of inline email message.

Sequences in postgreSQL are similar to auto-increment ids in MySQL. Their values are generated during the insert statements and will depend on the initial value of the sequence. You can manually set the sequence value (i.e. setval) before you start importing, to ensure that the values start from 1. Either ways, the metadata xml does not have the internal Ids (i.e. the organisationunitid and similar). Those ids are generated and if other organisationunits exist in the system into which you are importing, then you would not like to overwrite these internal ids.

If you are starting with a blank database, why not let DHIS2 2.11 create the blank database schema. Then on empty tables set the sequences to 1 and run the metadata import after that to ensure that they started from 1.


Regards,
Saptarshi PURKAYASTHA

My Tech Blog: http://sunnytalkstech.blogspot.com
You Live by CHOICE, Not by CHANCE

On 14 May 2013 08:15, Hannan Khan hannank@gmail.com wrote:

Dear Experts

I writ you guys earlier that we are facing problem running DHIS2 version 2.11 with MySQL. So we are trying to migrate to PostgreSQL. With 26000 orgunits and 35 million data value it is really difficult for us to do that. When I am trying to export metadata from MySQL based dhis2 to PostgreSQL based dhis2 (both are version 2.11) I found the following problem:

The orgunit table in mysql have following

But the postgresql have the following

Please see carefully the organisationunitid of ‘Bangladesh’ become 46 after import metadata into postgresql where as the original source in mysql was 1.

Similarly in mysql dataelement table was

it become following in postgres

dataelement 1 become 26424. Similarly in dataelementcategory table 1 become 13, 2 become 26387. And most dangerous is in dataelementcategoryoption where the values are

Also the period table is same.

Why this is happening? Can you please explain.

Regards

Hannan


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp