Help Please! Migration from MySQL to PostgreSQL

Support - Assistance technique
1

Hi,

I am presently working on a new HMIS project in Ethiopia and require some advice. We recently (2 months ago)installed a DHIS online system using MySQL and wish to migrate to PostgreSQL,
without losing information on the table structure or data which have just been entered. How can this be done?

2

Hi Felix,
You should be able to migrate most data with the export format (DXF)
and import this into a seperate instance which uses Postgres. Most
(but not all) of the metadata will be migrated. Some things (users,
custom data entry forms) and some other metadata elements will either
need to be recreated manually. There are possibly some tools which can
convert MySQL to Postgresql format (many here
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL\)
. In general, I would try first with DXF, see what is missing, and
then think about manually recreating things. Other ETL tools like
Pentaho may make things a bit easier, but one must be exceedingly
careful about direct injection into the DHIS2 database.

Others may have some more specific reccomendations and experience.

Best regards,
Jason

···

On Thu, Jan 19, 2012 at 4:20 PM, Felix Kayode <felixkayode@hotmail.com> wrote:

Hi,

I am presently working on a new HMIS project in Ethiopia and require some
advice. We recently (2 months ago)installed a DHIS online system using MySQL
and wish to migrate to PostgreSQL,
without losing information on the table structure or data which have just
been entered. How can this be done?

_______________________________________________
Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help

3

Thank you Jason, will I be able to migrate the org units including all facilities from the MySQL version? This is the section we are concerned with and are afraid we may lose a lot of the information created for the facilities. We may not be too concerned about the data elements and forms. The data has been collected for only two months and can easily be re-entered.

···

Date: Thu, 19 Jan 2012 17:44:38 +0200
Subject: Re: [Dhis2-users] Help Please! Migration from MySQL to PostgreSQL
From: jason.p.pickering@gmail.com
To: felixkayode@hotmail.com
CC: dhis2-users@lists.launchpad.net

Hi Felix,
You should be able to migrate most data with the export format (DXF)
and import this into a seperate instance which uses Postgres. Most
(but not all) of the metadata will be migrated. Some things (users,
custom data entry forms) and some other metadata elements will either
need to be recreated manually. There are possibly some tools which can
convert MySQL to Postgresql format (many here
Converting from other Databases to PostgreSQL - PostgreSQL wiki)
. In general, I would try first with DXF, see what is missing, and
then think about manually recreating things. Other ETL tools like
Pentaho may make things a bit easier, but one must be exceedingly
careful about direct injection into the DHIS2 database.

Others may have some more specific reccomendations and experience.

Best regards,
Jason

On Thu, Jan 19, 2012 at 4:20 PM, Felix Kayode felixkayode@hotmail.com wrote:

Hi,

I am presently working on a new HMIS project in Ethiopia and require some
advice. We recently (2 months ago)installed a DHIS online system using MySQL
and wish to migrate to PostgreSQL,
without losing information on the table structure or data which have just
been entered. How can this be done?

Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help

4

You should have no trouble with migrating all of the organisation
units and data, including all data elements and indicators data sets,
etc.

Migration of the custom data entry forms can be done, but it is quite
a lot of trouble. It is probably easier to recreate this, at least in
my opinion.

The export/Import process is described here

http://dhis2.org/doc/snapshot/en/user/html/ch18.html

Just export everything from your database and save the DXF file. If
you are going to use the same machine, then change your
hibernate.properties file to point to the new Postgresql database, and
start DHIS. You should have an empty DHIS database at this point.
Next, import the file which you exported in the previous step, and you
should be good to go.

If you encounter any errors, let the list know.

Regards,
Jason

···

On Thu, Jan 19, 2012 at 6:03 PM, Felix Kayode <felixkayode@hotmail.com> wrote:

Thank you Jason, will I be able to migrate the org units including all
facilities from the MySQL version? This is the section we are concerned with
and are afraid we may lose a lot of the information created for the
facilities. We may not be too concerned about the data elements and forms.
The data has been collected for only two months and can easily be
re-entered.

Date: Thu, 19 Jan 2012 17:44:38 +0200
Subject: Re: [Dhis2-users] Help Please! Migration from MySQL to PostgreSQL
From: jason.p.pickering@gmail.com
To: felixkayode@hotmail.com
CC: dhis2-users@lists.launchpad.net

Hi Felix,
You should be able to migrate most data with the export format (DXF)
and import this into a seperate instance which uses Postgres. Most
(but not all) of the metadata will be migrated. Some things (users,
custom data entry forms) and some other metadata elements will either
need to be recreated manually. There are possibly some tools which can
convert MySQL to Postgresql format (many here

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL\)
. In general, I would try first with DXF, see what is missing, and
then think about manually recreating things. Other ETL tools like
Pentaho may make things a bit easier, but one must be exceedingly
careful about direct injection into the DHIS2 database.

Others may have some more specific reccomendations and experience.

Best regards,
Jason

On Thu, Jan 19, 2012 at 4:20 PM, Felix Kayode <felixkayode@hotmail.com> >> wrote:
> Hi,
>
> I am presently working on a new HMIS project in Ethiopia and require
> some
> advice. We recently (2 months ago)installed a DHIS online system using
> MySQL
> and wish to migrate to PostgreSQL,
> without losing information on the table structure or data which have
> just
> been entered. How can this be done?
>
> _______________________________________________
> Mailing list: DHIS 2 Users in Launchpad
> Post to : dhis2-users@lists.launchpad.net
> Unsubscribe : DHIS 2 Users in Launchpad
> More help : ListHelp - Launchpad Help
>

5

Thanks Jason. I will try this out and provide a feedback to the list

···

Date: Thu, 19 Jan 2012 18:47:50 +0200
Subject: Re: [Dhis2-users] Help Please! Migration from MySQL to PostgreSQL
From: jason.p.pickering@gmail.com
To: felixkayode@hotmail.com
CC: dhis2-users@lists.launchpad.net

You should have no trouble with migrating all of the organisation
units and data, including all data elements and indicators data sets,
etc.

Migration of the custom data entry forms can be done, but it is quite
a lot of trouble. It is probably easier to recreate this, at least in
my opinion.

The export/Import process is described here

http://dhis2.org/doc/snapshot/en/user/html/ch18.html

Just export everything from your database and save the DXF file. If
you are going to use the same machine, then change your
hibernate.properties file to point to the new Postgresql database, and
start DHIS. You should have an empty DHIS database at this point.
Next, import the file which you exported in the previous step, and you
should be good to go.

If you encounter any errors, let the list know.

Regards,
Jason

On Thu, Jan 19, 2012 at 6:03 PM, Felix Kayode felixkayode@hotmail.com wrote:

Thank you Jason, will I be able to migrate the org units including all
facilities from the MySQL version? This is the section we are concerned with
and are afraid we may lose a lot of the information created for the
facilities. We may not be too concerned about the data elements and forms.
The data has been collected for only two months and can easily be
re-entered.

Date: Thu, 19 Jan 2012 17:44:38 +0200
Subject: Re: [Dhis2-users] Help Please! Migration from MySQL to PostgreSQL
From: jason.p.pickering@gmail.com
To: felixkayode@hotmail.com
CC: dhis2-users@lists.launchpad.net

Hi Felix,
You should be able to migrate most data with the export format (DXF)
and import this into a seperate instance which uses Postgres. Most
(but not all) of the metadata will be migrated. Some things (users,
custom data entry forms) and some other metadata elements will either
need to be recreated manually. There are possibly some tools which can
convert MySQL to Postgresql format (many here

Converting from other Databases to PostgreSQL - PostgreSQL wiki)
. In general, I would try first with DXF, see what is missing, and
then think about manually recreating things. Other ETL tools like
Pentaho may make things a bit easier, but one must be exceedingly
careful about direct injection into the DHIS2 database.

Others may have some more specific reccomendations and experience.

Best regards,
Jason

On Thu, Jan 19, 2012 at 4:20 PM, Felix Kayode felixkayode@hotmail.com > >> wrote:

Hi,

I am presently working on a new HMIS project in Ethiopia and require
some
advice. We recently (2 months ago)installed a DHIS online system using
MySQL
and wish to migrate to PostgreSQL,
without losing information on the table structure or data which have
just
been entered. How can this be done?

Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help