detail walkthrough to use postgresql with DHIS2 Live

Support - Assistance technique
1

Dear all,

I am trying to test DHIS2 for monitoring projects in a Humanitarian organization.

I cannot find the way to use postgresql with DHIS2 LIVE, so far i have located the dhis.conf file and also download dhis2-db-sierra-leone.sql.

Is it possible that you can help with a step by step instruction to make the set up? since i have limit knowledge for programing this database.

I have tried to make my test with the H2 version but when i try to use analytic to export i get this error:

2018-02-21 08:06:37 Process failed: StatementCallback; bad SQL grammar [create table _orgunitstructure_temp (organisationunitid integer not null primary key, organisationunituid character(11), level integer, “idlevel1” integer, “uidlevel1” character(11), “idlevel2” integer, “uidlevel2” character(11));]; nested exception is org.h2.jdbc.JdbcSQLException: Tabla “_ORGUNITSTRUCTURE_TEMP” ya existe Table “_ORGUNITSTRUCTURE_TEMP” already exists; SQL statement: create table _orgunitstructure_temp (organisationunitid integer not null primary key, organisationunituid character(11), level integer, “idlevel1” integer, “uidlevel1” character(11), “idlevel2” integer, “uidlevel2” character(11)); [42101-194]
2018-02-21 08:06:37 Updating resource tables
2018-02-21 08:06:37 Analytics table update process started

Then I tried to change the dhis2.conf with the following text:

Hibernate SQL dialect

connection.dialect = org.hibernate.dialect.PostgreSQLDialect

JDBC driver class

connection.driver_class = org.postgresql.Driver

JDBC driver connection URL

connection.url = jdbc:postgresql:dbname

Database username

connection.username = username

Database password

connection.password = password

Database schema behavior, can be validate, update, create, create-drop

connection.schema = update

#Encryption password (sensitive)

#encryption.password = password

didnt work, i got 503 error.

I have also downloaded POSTGRADESQL 10 (I am not sure if this is necessary).

many thanks in advance for your support!

best,

Eduardo.

2

Hi Eduardo,

You seem to be on the right track, I attach some slides I made a long time ago.

Here is what it says in the manual regarding dhis.conf:

https://docs.dhis2.org/2.25/en/implementer/html/install_server_setup.html#install_database_configuration

I would suggest starting with an empty Postgres database. and let DHIS2 generate the tables.

Knut

Installation on a Windows laptop.pptx (63.3 KB)

···

On Sat, Feb 24, 2018 at 1:49 AM, Eduardo Gonzalez egonzalezhn@gmail.com wrote:

Dear all,

I am trying to test DHIS2 for monitoring projects in a Humanitarian organization.

I cannot find the way to use postgresql with DHIS2 LIVE, so far i have located the dhis.conf file and also download dhis2-db-sierra-leone.sql.

Is it possible that you can help with a step by step instruction to make the set up? since i have limit knowledge for programing this database.

I have tried to make my test with the H2 version but when i try to use analytic to export i get this error:

2018-02-21 08:06:37 Process failed: StatementCallback; bad SQL grammar [create table _orgunitstructure_temp (organisationunitid integer not null primary key, organisationunituid character(11), level integer, “idlevel1” integer, “uidlevel1” character(11), “idlevel2” integer, “uidlevel2” character(11));]; nested exception is org.h2.jdbc.JdbcSQLException: Tabla “_ORGUNITSTRUCTURE_TEMP” ya existe Table “_ORGUNITSTRUCTURE_TEMP” already exists; SQL statement: create table _orgunitstructure_temp (organisationunitid integer not null primary key, organisationunituid character(11), level integer, “idlevel1” integer, “uidlevel1” character(11), “idlevel2” integer, “uidlevel2” character(11)); [42101-194]
2018-02-21 08:06:37 Updating resource tables
2018-02-21 08:06:37 Analytics table update process started

Then I tried to change the dhis2.conf with the following text:

Hibernate SQL dialect

connection.dialect = org.hibernate.dialect.PostgreSQLDialect

JDBC driver class

connection.driver_class = org.postgresql.Driver

JDBC driver connection URL

connection.url = jdbc:postgresql:dbname

Database username

connection.username = username

Database password

connection.password = password

Database schema behavior, can be validate, update, create, create-drop

connection.schema = update

#Encryption password (sensitive)

#encryption.password = password

didnt work, i got 503 error.

I have also downloaded POSTGRADESQL 10 (I am not sure if this is necessary).

many thanks in advance for your support!

best,

Eduardo.

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

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

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

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

–

Knut Staring

Department of Information, Evidence and Research
World Health Organization, Geneva, Switzerland
Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522
Skype: knutstar

3

Dear Knut,

many thanks for this, it worked!

I had to install Postgresql v9.5 to make it work. I the start couldn’t get in, then I did some tests and i discover that you have to add control all privileges to the dhis user in the privileges tab.

Many thank again for this detail instructions, I suggest to add the privileges step and add this Tutorial to the main page of the download DHIS2 LIVE section, I have seem many people asking the same thing, setup postgresql with DHIS2 Live.

Again, many thanks for your help!

best,

E.

···

2018-02-24 2:29 GMT-06:00 Knut Staring knutst@gmail.com:

Hi Eduardo,

You seem to be on the right track, I attach some slides I made a long time ago.

Here is what it says in the manual regarding dhis.conf:

https://docs.dhis2.org/2.25/en/implementer/html/install_server_setup.html#install_database_configuration

I would suggest starting with an empty Postgres database. and let DHIS2 generate the tables.

Knut

On Sat, Feb 24, 2018 at 1:49 AM, Eduardo Gonzalez egonzalezhn@gmail.com wrote:

Dear all,

I am trying to test DHIS2 for monitoring projects in a Humanitarian organization.

I cannot find the way to use postgresql with DHIS2 LIVE, so far i have located the dhis.conf file and also download dhis2-db-sierra-leone.sql.

Is it possible that you can help with a step by step instruction to make the set up? since i have limit knowledge for programing this database.

I have tried to make my test with the H2 version but when i try to use analytic to export i get this error:

2018-02-21 08:06:37 Process failed: StatementCallback; bad SQL grammar [create table _orgunitstructure_temp (organisationunitid integer not null primary key, organisationunituid character(11), level integer, “idlevel1” integer, “uidlevel1” character(11), “idlevel2” integer, “uidlevel2” character(11));]; nested exception is org.h2.jdbc.JdbcSQLException: Tabla “_ORGUNITSTRUCTURE_TEMP” ya existe Table “_ORGUNITSTRUCTURE_TEMP” already exists; SQL statement: create table _orgunitstructure_temp (organisationunitid integer not null primary key, organisationunituid character(11), level integer, “idlevel1” integer, “uidlevel1” character(11), “idlevel2” integer, “uidlevel2” character(11)); [42101-194]
2018-02-21 08:06:37 Updating resource tables
2018-02-21 08:06:37 Analytics table update process started

Then I tried to change the dhis2.conf with the following text:

Hibernate SQL dialect

connection.dialect = org.hibernate.dialect.PostgreSQLDialect

JDBC driver class

connection.driver_class = org.postgresql.Driver

JDBC driver connection URL

connection.url = jdbc:postgresql:dbname

Database username

connection.username = username

Database password

connection.password = password

Database schema behavior, can be validate, update, create, create-drop

connection.schema = update

#Encryption password (sensitive)

#encryption.password = password

didnt work, i got 503 error.

I have also downloaded POSTGRADESQL 10 (I am not sure if this is necessary).

many thanks in advance for your support!

best,

Eduardo.

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

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

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

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

Knut Staring

Department of Information, Evidence and Research
World Health Organization, Geneva, Switzerland
Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522
Skype: knutstar

–

4

Hi Eduardo,

Great that you got it working and thanks for the feedback - I will update it and ask the website maintainers to place it on the website.

Cheers,

Knut

···

On Sat, Feb 24, 2018 at 7:16 PM, Eduardo Gonzalez egonzalezhn@gmail.com wrote:

Dear Knut,

many thanks for this, it worked!

I had to install Postgresql v9.5 to make it work. I the start couldn’t get in, then I did some tests and i discover that you have to add control all privileges to the dhis user in the privileges tab.

Many thank again for this detail instructions, I suggest to add the privileges step and add this Tutorial to the main page of the download DHIS2 LIVE section, I have seem many people asking the same thing, setup postgresql with DHIS2 Live.

Again, many thanks for your help!

best,

E.

–

2018-02-24 2:29 GMT-06:00 Knut Staring knutst@gmail.com:

Hi Eduardo,

You seem to be on the right track, I attach some slides I made a long time ago.

Here is what it says in the manual regarding dhis.conf:

https://docs.dhis2.org/2.25/en/implementer/html/install_server_setup.html#install_database_configuration

I would suggest starting with an empty Postgres database. and let DHIS2 generate the tables.

Knut

On Sat, Feb 24, 2018 at 1:49 AM, Eduardo Gonzalez egonzalezhn@gmail.com wrote:

Dear all,

I am trying to test DHIS2 for monitoring projects in a Humanitarian organization.

I cannot find the way to use postgresql with DHIS2 LIVE, so far i have located the dhis.conf file and also download dhis2-db-sierra-leone.sql.

Is it possible that you can help with a step by step instruction to make the set up? since i have limit knowledge for programing this database.

I have tried to make my test with the H2 version but when i try to use analytic to export i get this error:

2018-02-21 08:06:37 Process failed: StatementCallback; bad SQL grammar [create table _orgunitstructure_temp (organisationunitid integer not null primary key, organisationunituid character(11), level integer, “idlevel1” integer, “uidlevel1” character(11), “idlevel2” integer, “uidlevel2” character(11));]; nested exception is org.h2.jdbc.JdbcSQLException: Tabla “_ORGUNITSTRUCTURE_TEMP” ya existe Table “_ORGUNITSTRUCTURE_TEMP” already exists; SQL statement: create table _orgunitstructure_temp (organisationunitid integer not null primary key, organisationunituid character(11), level integer, “idlevel1” integer, “uidlevel1” character(11), “idlevel2” integer, “uidlevel2” character(11)); [42101-194]
2018-02-21 08:06:37 Updating resource tables
2018-02-21 08:06:37 Analytics table update process started

Then I tried to change the dhis2.conf with the following text:

Hibernate SQL dialect

connection.dialect = org.hibernate.dialect.PostgreSQLDialect

JDBC driver class

connection.driver_class = org.postgresql.Driver

JDBC driver connection URL

connection.url = jdbc:postgresql:dbname

Database username

connection.username = username

Database password

connection.password = password

Database schema behavior, can be validate, update, create, create-drop

connection.schema = update

#Encryption password (sensitive)

#encryption.password = password

didnt work, i got 503 error.

I have also downloaded POSTGRADESQL 10 (I am not sure if this is necessary).

many thanks in advance for your support!

best,

Eduardo.

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

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

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

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

Knut Staring

Department of Information, Evidence and Research
World Health Organization, Geneva, Switzerland
Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522
Skype: knutstar

–

Knut Staring

Department of Information, Evidence and Research
World Health Organization, Geneva, Switzerland
Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522
Skype: knutstar