Permission denied for relation i18nlocale

Hello DHIS2 Developer’s List

I’m attempting to spin up a couple test instances of DHIS2 inside Ubuntu 14.04 and with both 2.14 and 2.16 and am encountering some problems.

If create the postgres user (dhis) and the db (dhis2) and do nothing else, I get the demo version (admin:district) for both 2.14 and 2.16. So tomcat and postgresql are talking and the server is capable of starting and serving pages. I then delete the DB, recreate a blank one and import a database dump from our production server (version 2.14). Things go well until I execute ./tomcat-dhis/bin/startup.sh this time, the system will fail to start either 2.14 or 2.16. Interestingly enough, I can do the exact same process in Windows 7 (Postgres and Tomcat again) and 2.14 starts up without issue, but I have not tested 2.16 in Windows yet. I’m encountering the following error after startup routine 11 of 11 is complete. I’ve included a snippet below from 2.14, and have attached the entire log to this email.

System: Ubuntu 14.04

PostgreSQL 9.3.5

Tomcat7

  • ERROR 2014-10-06 16:06:51,792 ERROR: permission denied for relation i18nlocale (SqlExceptionHelper.java [localhost-startStop-1])

  • ERROR 2014-10-06 16:06:51,812 org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

(…)

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation i18nlocale

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

(…)

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Error listenerStart

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Context [] startup failed due to previous errors

Any ideas or suggestions are appreciated. I’m both trying to learn how to use Ubuntu with DHIS2 (used the following: https://www.dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html) and attempting to test our migration from 2.14 to 2.16. Thanks!

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

catalina.out.txt (14.2 KB)

Hi TIm,
Usually, this happens when a local database is restored from a remote dump which belongs to another user not present in your local system. Be sure your database is owned by the user which is present in your hibernate.properties files, and that all tables belong to that user. Otherwise, you may need to alter the ownership of the database and tables to the user which is used to access the database.

Best regards,

Jason

···

On Mon, Oct 6, 2014 at 7:38 AM, Timothy Harding hardingt@gmail.com wrote:

Hello DHIS2 Developer’s List

I’m attempting to spin up a couple test instances of DHIS2 inside Ubuntu 14.04 and with both 2.14 and 2.16 and am encountering some problems.

If create the postgres user (dhis) and the db (dhis2) and do nothing else, I get the demo version (admin:district) for both 2.14 and 2.16. So tomcat and postgresql are talking and the server is capable of starting and serving pages. I then delete the DB, recreate a blank one and import a database dump from our production server (version 2.14). Things go well until I execute ./tomcat-dhis/bin/startup.sh this time, the system will fail to start either 2.14 or 2.16. Interestingly enough, I can do the exact same process in Windows 7 (Postgres and Tomcat again) and 2.14 starts up without issue, but I have not tested 2.16 in Windows yet. I’m encountering the following error after startup routine 11 of 11 is complete. I’ve included a snippet below from 2.14, and have attached the entire log to this email.

System: Ubuntu 14.04

PostgreSQL 9.3.5

Tomcat7

  • ERROR 2014-10-06 16:06:51,792 ERROR: permission denied for relation i18nlocale (SqlExceptionHelper.java [localhost-startStop-1])
  • ERROR 2014-10-06 16:06:51,812 org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

(…)

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation i18nlocale

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

(…)

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Error listenerStart

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Context [] startup failed due to previous errors

Any ideas or suggestions are appreciated. I’m both trying to learn how to use Ubuntu with DHIS2 (used the following: https://www.dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html) and attempting to test our migration from 2.14 to 2.16. Thanks!

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137


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


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

I suspect Jason is right.

People often backup the database with the ownership information intact, which is useful if you want to restore the database on the same system, eg recovering after a crash of some sort, where you want to get everything back to where it was.

Having the ownership in the backup is more problematic where the intention is to restore it somewhere else where the users may be different (and might not even exist)

suggest you do the backup again, but this time ensure that you don’t include the ownership information. If using pg_dump the -O option ensures this. Then when you restore into a new database, all objects will be owned by the (new) database owner. I’m pretty sure there will be a similar option on pgadmin if you are using this.

Mind you, if the dump is in the postgres custom binary format , then you can use the -O option on restore and it will ignore ownership information. If it is a text based sql format then that option doesn’t work. You can massage the file (you can see an example using sed at the bottom of the script here https://github.com/dhis2/dhis2tools/blob/master/pkg/usr/bin/dhis2-restoredb))), but maybe its easier to just do the backup again as described at the start. Particularly if you are not using the command line tools.

The final alternative is to do a REASSIGN OWNED BY xxx TO nnn. This might also be straightforward if you can easily see who it is that is owning the database objects.

On a more general note, as an alternative to working through chapter 8 (which is useful for understanding some of the concepts) you might consider basing your installation on the dhis2-tools : https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20.html. Addressing this postgres permissions issue is one of the many gotchas which are dealt with by using these standard scripts.

Regards

Bob

···

On 6 October 2014 12:16, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi TIm,
Usually, this happens when a local database is restored from a remote dump which belongs to another user not present in your local system. Be sure your database is owned by the user which is present in your hibernate.properties files, and that all tables belong to that user. Otherwise, you may need to alter the ownership of the database and tables to the user which is used to access the database.

Best regards,

Jason


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

On Mon, Oct 6, 2014 at 7:38 AM, Timothy Harding hardingt@gmail.com wrote:

Hello DHIS2 Developer’s List

I’m attempting to spin up a couple test instances of DHIS2 inside Ubuntu 14.04 and with both 2.14 and 2.16 and am encountering some problems.

If create the postgres user (dhis) and the db (dhis2) and do nothing else, I get the demo version (admin:district) for both 2.14 and 2.16. So tomcat and postgresql are talking and the server is capable of starting and serving pages. I then delete the DB, recreate a blank one and import a database dump from our production server (version 2.14). Things go well until I execute ./tomcat-dhis/bin/startup.sh this time, the system will fail to start either 2.14 or 2.16. Interestingly enough, I can do the exact same process in Windows 7 (Postgres and Tomcat again) and 2.14 starts up without issue, but I have not tested 2.16 in Windows yet. I’m encountering the following error after startup routine 11 of 11 is complete. I’ve included a snippet below from 2.14, and have attached the entire log to this email.

System: Ubuntu 14.04

PostgreSQL 9.3.5

Tomcat7

  • ERROR 2014-10-06 16:06:51,792 ERROR: permission denied for relation i18nlocale (SqlExceptionHelper.java [localhost-startStop-1])
  • ERROR 2014-10-06 16:06:51,812 org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

(…)

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation i18nlocale

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

(…)

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Error listenerStart

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Context [] startup failed due to previous errors

Any ideas or suggestions are appreciated. I’m both trying to learn how to use Ubuntu with DHIS2 (used the following: https://www.dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html) and attempting to test our migration from 2.14 to 2.16. Thanks!

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137


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

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Thank you both Bob and Jason, so much! I will give the suggestions a shot. That last link looks really promising Bob, but it seems to be very much a work in progress:

“Once your base system is properly installed and secured you can proceed to install the dhis2-tools package from the apt repository at http://apt.dhis2.org. The easiest way to do so is to run the install.sh script available at …”

https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20s03.html

Does anyone on the list have a link to this install.sh file? If not, I can manage, this isn’t my first apt-get repository rodeo :). Thanks a million both of you, getting to work on this now, and will report back.

···

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Mon, Oct 6, 2014 at 11:22 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

I suspect Jason is right.

People often backup the database with the ownership information intact, which is useful if you want to restore the database on the same system, eg recovering after a crash of some sort, where you want to get everything back to where it was.

Having the ownership in the backup is more problematic where the intention is to restore it somewhere else where the users may be different (and might not even exist)

| suggest you do the backup again, but this time ensure that you don’t include the ownership information. If using pg_dump the -O option ensures this. Then when you restore into a new database, all objects will be owned by the (new) database owner. I’m pretty sure there will be a similar option on pgadmin if you are using this.

Mind you, if the dump is in the postgres custom binary format , then you can use the -O option on restore and it will ignore ownership information. If it is a text based sql format then that option doesn’t work. You can massage the file (you can see an example using sed at the bottom of the script here https://github.com/dhis2/dhis2tools/blob/master/pkg/usr/bin/dhis2-restoredb))), but maybe its easier to just do the backup again as described at the start. Particularly if you are not using the command line tools.

The final alternative is to do a REASSIGN OWNED BY xxx TO nnn. This might also be straightforward if you can easily see who it is that is owning the database objects.

On a more general note, as an alternative to working through chapter 8 (which is useful for understanding some of the concepts) you might consider basing your installation on the dhis2-tools : https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20.html. Addressing this postgres permissions issue is one of the many gotchas which are dealt with by using these standard scripts.

Regards

Bob

On 6 October 2014 12:16, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi TIm,
Usually, this happens when a local database is restored from a remote dump which belongs to another user not present in your local system. Be sure your database is owned by the user which is present in your hibernate.properties files, and that all tables belong to that user. Otherwise, you may need to alter the ownership of the database and tables to the user which is used to access the database.

Best regards,

Jason


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

On Mon, Oct 6, 2014 at 7:38 AM, Timothy Harding hardingt@gmail.com wrote:

Hello DHIS2 Developer’s List

I’m attempting to spin up a couple test instances of DHIS2 inside Ubuntu 14.04 and with both 2.14 and 2.16 and am encountering some problems.

If create the postgres user (dhis) and the db (dhis2) and do nothing else, I get the demo version (admin:district) for both 2.14 and 2.16. So tomcat and postgresql are talking and the server is capable of starting and serving pages. I then delete the DB, recreate a blank one and import a database dump from our production server (version 2.14). Things go well until I execute ./tomcat-dhis/bin/startup.sh this time, the system will fail to start either 2.14 or 2.16. Interestingly enough, I can do the exact same process in Windows 7 (Postgres and Tomcat again) and 2.14 starts up without issue, but I have not tested 2.16 in Windows yet. I’m encountering the following error after startup routine 11 of 11 is complete. I’ve included a snippet below from 2.14, and have attached the entire log to this email.

System: Ubuntu 14.04

PostgreSQL 9.3.5

Tomcat7

  • ERROR 2014-10-06 16:06:51,792 ERROR: permission denied for relation i18nlocale (SqlExceptionHelper.java [localhost-startStop-1])
  • ERROR 2014-10-06 16:06:51,812 org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

(…)

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation i18nlocale

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

(…)

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Error listenerStart

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Context [] startup failed due to previous errors

Any ideas or suggestions are appreciated. I’m both trying to learn how to use Ubuntu with DHIS2 (used the following: https://www.dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html) and attempting to test our migration from 2.14 to 2.16. Thanks!

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137


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

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

You’ll find it here https://github.com/dhis2/dhis2tools.

···

On Mon, Oct 6, 2014 at 11:24 PM, Timothy Harding hardingt@gmail.com wrote:

Thank you both Bob and Jason, so much! I will give the suggestions a shot. That last link looks really promising Bob, but it seems to be very much a work in progress:

“Once your base system is properly installed and secured you can proceed to install the dhis2-tools package from the apt repository at http://apt.dhis2.org. The easiest way to do so is to run the install.sh script available at …”

https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20s03.html

Does anyone on the list have a link to this install.sh file? If not, I can manage, this isn’t my first apt-get repository rodeo :). Thanks a million both of you, getting to work on this now, and will report back.


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

Dept. of Informatics, University of Oslo

+4791880522

Skype: knutstar

http://dhis2.org

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Mon, Oct 6, 2014 at 11:22 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

I suspect Jason is right.

People often backup the database with the ownership information intact, which is useful if you want to restore the database on the same system, eg recovering after a crash of some sort, where you want to get everything back to where it was.

Having the ownership in the backup is more problematic where the intention is to restore it somewhere else where the users may be different (and might not even exist)

| suggest you do the backup again, but this time ensure that you don’t include the ownership information. If using pg_dump the -O option ensures this. Then when you restore into a new database, all objects will be owned by the (new) database owner. I’m pretty sure there will be a similar option on pgadmin if you are using this.

Mind you, if the dump is in the postgres custom binary format , then you can use the -O option on restore and it will ignore ownership information. If it is a text based sql format then that option doesn’t work. You can massage the file (you can see an example using sed at the bottom of the script here https://github.com/dhis2/dhis2tools/blob/master/pkg/usr/bin/dhis2-restoredb))), but maybe its easier to just do the backup again as described at the start. Particularly if you are not using the command line tools.

The final alternative is to do a REASSIGN OWNED BY xxx TO nnn. This might also be straightforward if you can easily see who it is that is owning the database objects.

On a more general note, as an alternative to working through chapter 8 (which is useful for understanding some of the concepts) you might consider basing your installation on the dhis2-tools : https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20.html. Addressing this postgres permissions issue is one of the many gotchas which are dealt with by using these standard scripts.

Regards

Bob

On 6 October 2014 12:16, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi TIm,
Usually, this happens when a local database is restored from a remote dump which belongs to another user not present in your local system. Be sure your database is owned by the user which is present in your hibernate.properties files, and that all tables belong to that user. Otherwise, you may need to alter the ownership of the database and tables to the user which is used to access the database.

Best regards,

Jason


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

On Mon, Oct 6, 2014 at 7:38 AM, Timothy Harding hardingt@gmail.com wrote:

Hello DHIS2 Developer’s List

I’m attempting to spin up a couple test instances of DHIS2 inside Ubuntu 14.04 and with both 2.14 and 2.16 and am encountering some problems.

If create the postgres user (dhis) and the db (dhis2) and do nothing else, I get the demo version (admin:district) for both 2.14 and 2.16. So tomcat and postgresql are talking and the server is capable of starting and serving pages. I then delete the DB, recreate a blank one and import a database dump from our production server (version 2.14). Things go well until I execute ./tomcat-dhis/bin/startup.sh this time, the system will fail to start either 2.14 or 2.16. Interestingly enough, I can do the exact same process in Windows 7 (Postgres and Tomcat again) and 2.14 starts up without issue, but I have not tested 2.16 in Windows yet. I’m encountering the following error after startup routine 11 of 11 is complete. I’ve included a snippet below from 2.14, and have attached the entire log to this email.

System: Ubuntu 14.04

PostgreSQL 9.3.5

Tomcat7

  • ERROR 2014-10-06 16:06:51,792 ERROR: permission denied for relation i18nlocale (SqlExceptionHelper.java [localhost-startStop-1])
  • ERROR 2014-10-06 16:06:51,812 org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

(…)

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation i18nlocale

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

(…)

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Error listenerStart

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Context [] startup failed due to previous errors

Any ideas or suggestions are appreciated. I’m both trying to learn how to use Ubuntu with DHIS2 (used the following: https://www.dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html) and attempting to test our migration from 2.14 to 2.16. Thanks!

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137


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

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Some other pieces

DHIS2 Ubuntu server installation (1) (2).txt (908 Bytes)

DHIS2 Ubuntu server installation (1) (2).txt (908 Bytes)

···

On Mon, Oct 6, 2014 at 11:29 PM, Knut Staring knutst@gmail.com wrote:

You’ll find it here https://github.com/dhis2/dhis2tools.


Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

Skype: knutstar

http://dhis2.org

On Mon, Oct 6, 2014 at 11:24 PM, Timothy Harding hardingt@gmail.com wrote:

Thank you both Bob and Jason, so much! I will give the suggestions a shot. That last link looks really promising Bob, but it seems to be very much a work in progress:

“Once your base system is properly installed and secured you can proceed to install the dhis2-tools package from the apt repository at http://apt.dhis2.org. The easiest way to do so is to run the install.sh script available at …”

https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20s03.html

Does anyone on the list have a link to this install.sh file? If not, I can manage, this isn’t my first apt-get repository rodeo :). Thanks a million both of you, getting to work on this now, and will report back.


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

Dept. of Informatics, University of Oslo

+4791880522

Skype: knutstar

http://dhis2.org

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Mon, Oct 6, 2014 at 11:22 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

I suspect Jason is right.

People often backup the database with the ownership information intact, which is useful if you want to restore the database on the same system, eg recovering after a crash of some sort, where you want to get everything back to where it was.

Having the ownership in the backup is more problematic where the intention is to restore it somewhere else where the users may be different (and might not even exist)

| suggest you do the backup again, but this time ensure that you don’t include the ownership information. If using pg_dump the -O option ensures this. Then when you restore into a new database, all objects will be owned by the (new) database owner. I’m pretty sure there will be a similar option on pgadmin if you are using this.

Mind you, if the dump is in the postgres custom binary format , then you can use the -O option on restore and it will ignore ownership information. If it is a text based sql format then that option doesn’t work. You can massage the file (you can see an example using sed at the bottom of the script here https://github.com/dhis2/dhis2tools/blob/master/pkg/usr/bin/dhis2-restoredb))), but maybe its easier to just do the backup again as described at the start. Particularly if you are not using the command line tools.

The final alternative is to do a REASSIGN OWNED BY xxx TO nnn. This might also be straightforward if you can easily see who it is that is owning the database objects.

On a more general note, as an alternative to working through chapter 8 (which is useful for understanding some of the concepts) you might consider basing your installation on the dhis2-tools : https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20.html. Addressing this postgres permissions issue is one of the many gotchas which are dealt with by using these standard scripts.

Regards

Bob

On 6 October 2014 12:16, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi TIm,
Usually, this happens when a local database is restored from a remote dump which belongs to another user not present in your local system. Be sure your database is owned by the user which is present in your hibernate.properties files, and that all tables belong to that user. Otherwise, you may need to alter the ownership of the database and tables to the user which is used to access the database.

Best regards,

Jason


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

On Mon, Oct 6, 2014 at 7:38 AM, Timothy Harding hardingt@gmail.com wrote:

Hello DHIS2 Developer’s List

I’m attempting to spin up a couple test instances of DHIS2 inside Ubuntu 14.04 and with both 2.14 and 2.16 and am encountering some problems.

If create the postgres user (dhis) and the db (dhis2) and do nothing else, I get the demo version (admin:district) for both 2.14 and 2.16. So tomcat and postgresql are talking and the server is capable of starting and serving pages. I then delete the DB, recreate a blank one and import a database dump from our production server (version 2.14). Things go well until I execute ./tomcat-dhis/bin/startup.sh this time, the system will fail to start either 2.14 or 2.16. Interestingly enough, I can do the exact same process in Windows 7 (Postgres and Tomcat again) and 2.14 starts up without issue, but I have not tested 2.16 in Windows yet. I’m encountering the following error after startup routine 11 of 11 is complete. I’ve included a snippet below from 2.14, and have attached the entire log to this email.

System: Ubuntu 14.04

PostgreSQL 9.3.5

Tomcat7

  • ERROR 2014-10-06 16:06:51,792 ERROR: permission denied for relation i18nlocale (SqlExceptionHelper.java [localhost-startStop-1])
  • ERROR 2014-10-06 16:06:51,812 org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

(…)

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation i18nlocale

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

(…)

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Error listenerStart

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Context [] startup failed due to previous errors

Any ideas or suggestions are appreciated. I’m both trying to learn how to use Ubuntu with DHIS2 (used the following: https://www.dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html) and attempting to test our migration from 2.14 to 2.16. Thanks!

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137


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

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Thanks Knut, much appreciated :)!

···

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Tue, Oct 7, 2014 at 8:31 AM, Knut Staring knutst@gmail.com wrote:

Some other pieces

On Mon, Oct 6, 2014 at 11:29 PM, Knut Staring knutst@gmail.com wrote:

You’ll find it here https://github.com/dhis2/dhis2tools.


Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

Skype: knutstar

http://dhis2.org

On Mon, Oct 6, 2014 at 11:24 PM, Timothy Harding hardingt@gmail.com wrote:

Thank you both Bob and Jason, so much! I will give the suggestions a shot. That last link looks really promising Bob, but it seems to be very much a work in progress:

“Once your base system is properly installed and secured you can proceed to install the dhis2-tools package from the apt repository at http://apt.dhis2.org. The easiest way to do so is to run the install.sh script available at …”

https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20s03.html

Does anyone on the list have a link to this install.sh file? If not, I can manage, this isn’t my first apt-get repository rodeo :). Thanks a million both of you, getting to work on this now, and will report back.


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

Dept. of Informatics, University of Oslo

+4791880522

Skype: knutstar

http://dhis2.org

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Mon, Oct 6, 2014 at 11:22 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

I suspect Jason is right.

People often backup the database with the ownership information intact, which is useful if you want to restore the database on the same system, eg recovering after a crash of some sort, where you want to get everything back to where it was.

Having the ownership in the backup is more problematic where the intention is to restore it somewhere else where the users may be different (and might not even exist)

| suggest you do the backup again, but this time ensure that you don’t include the ownership information. If using pg_dump the -O option ensures this. Then when you restore into a new database, all objects will be owned by the (new) database owner. I’m pretty sure there will be a similar option on pgadmin if you are using this.

Mind you, if the dump is in the postgres custom binary format , then you can use the -O option on restore and it will ignore ownership information. If it is a text based sql format then that option doesn’t work. You can massage the file (you can see an example using sed at the bottom of the script here https://github.com/dhis2/dhis2tools/blob/master/pkg/usr/bin/dhis2-restoredb))), but maybe its easier to just do the backup again as described at the start. Particularly if you are not using the command line tools.

The final alternative is to do a REASSIGN OWNED BY xxx TO nnn. This might also be straightforward if you can easily see who it is that is owning the database objects.

On a more general note, as an alternative to working through chapter 8 (which is useful for understanding some of the concepts) you might consider basing your installation on the dhis2-tools : https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20.html. Addressing this postgres permissions issue is one of the many gotchas which are dealt with by using these standard scripts.

Regards

Bob

On 6 October 2014 12:16, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi TIm,
Usually, this happens when a local database is restored from a remote dump which belongs to another user not present in your local system. Be sure your database is owned by the user which is present in your hibernate.properties files, and that all tables belong to that user. Otherwise, you may need to alter the ownership of the database and tables to the user which is used to access the database.

Best regards,

Jason


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

On Mon, Oct 6, 2014 at 7:38 AM, Timothy Harding hardingt@gmail.com wrote:

Hello DHIS2 Developer’s List

I’m attempting to spin up a couple test instances of DHIS2 inside Ubuntu 14.04 and with both 2.14 and 2.16 and am encountering some problems.

If create the postgres user (dhis) and the db (dhis2) and do nothing else, I get the demo version (admin:district) for both 2.14 and 2.16. So tomcat and postgresql are talking and the server is capable of starting and serving pages. I then delete the DB, recreate a blank one and import a database dump from our production server (version 2.14). Things go well until I execute ./tomcat-dhis/bin/startup.sh this time, the system will fail to start either 2.14 or 2.16. Interestingly enough, I can do the exact same process in Windows 7 (Postgres and Tomcat again) and 2.14 starts up without issue, but I have not tested 2.16 in Windows yet. I’m encountering the following error after startup routine 11 of 11 is complete. I’ve included a snippet below from 2.14, and have attached the entire log to this email.

System: Ubuntu 14.04

PostgreSQL 9.3.5

Tomcat7

  • ERROR 2014-10-06 16:06:51,792 ERROR: permission denied for relation i18nlocale (SqlExceptionHelper.java [localhost-startStop-1])
  • ERROR 2014-10-06 16:06:51,812 org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

(…)

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation i18nlocale

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

(…)

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Error listenerStart

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Context [] startup failed due to previous errors

Any ideas or suggestions are appreciated. I’m both trying to learn how to use Ubuntu with DHIS2 (used the following: https://www.dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html) and attempting to test our migration from 2.14 to 2.16. Thanks!

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137


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

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Hi Tim

Sorry I’ve been aware of that hanging sentence for a while :slight_smile: I guess the problem at the time I was writing that we didn’t really have a stable place where this file would be. Since then we moved to github as Knut indicated, so I should just put the link in the documentation.

Anyway if you are using ubuntu 14.04, the only relevant lines in install.sh are:

add dhis2 apt repository to sources

echo ‘deb http://apt.dhis2.org/amd64 /’ > /etc/apt/sources.list.d/dhis2.list

wget -O - http://apt.dhis2.org/keyFile |apt-key add -

apt-get update -y

install the dhis2-tools deb

apt-get install dhis2-tools

Uncomment below to install postgres and nginx servers on this machine

apt-get -y install nginx postgresql

Besides the odd missing bit here and there in the docs, the tools themselves are getting reasonably well established and are being used in probably a dozen or so places.

Cheers

Bob

···

On 6 October 2014 22:24, Timothy Harding hardingt@gmail.com wrote:

Thank you both Bob and Jason, so much! I will give the suggestions a shot. That last link looks really promising Bob, but it seems to be very much a work in progress:

“Once your base system is properly installed and secured you can proceed to install the dhis2-tools package from the apt repository at http://apt.dhis2.org. The easiest way to do so is to run the install.sh script available at …”

https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20s03.html

Does anyone on the list have a link to this install.sh file? If not, I can manage, this isn’t my first apt-get repository rodeo :). Thanks a million both of you, getting to work on this now, and will report back.

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Mon, Oct 6, 2014 at 11:22 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

I suspect Jason is right.

People often backup the database with the ownership information intact, which is useful if you want to restore the database on the same system, eg recovering after a crash of some sort, where you want to get everything back to where it was.

Having the ownership in the backup is more problematic where the intention is to restore it somewhere else where the users may be different (and might not even exist)

| suggest you do the backup again, but this time ensure that you don’t include the ownership information. If using pg_dump the -O option ensures this. Then when you restore into a new database, all objects will be owned by the (new) database owner. I’m pretty sure there will be a similar option on pgadmin if you are using this.

Mind you, if the dump is in the postgres custom binary format , then you can use the -O option on restore and it will ignore ownership information. If it is a text based sql format then that option doesn’t work. You can massage the file (you can see an example using sed at the bottom of the script here https://github.com/dhis2/dhis2tools/blob/master/pkg/usr/bin/dhis2-restoredb))), but maybe its easier to just do the backup again as described at the start. Particularly if you are not using the command line tools.

The final alternative is to do a REASSIGN OWNED BY xxx TO nnn. This might also be straightforward if you can easily see who it is that is owning the database objects.

On a more general note, as an alternative to working through chapter 8 (which is useful for understanding some of the concepts) you might consider basing your installation on the dhis2-tools : https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20.html. Addressing this postgres permissions issue is one of the many gotchas which are dealt with by using these standard scripts.

Regards

Bob

On 6 October 2014 12:16, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi TIm,
Usually, this happens when a local database is restored from a remote dump which belongs to another user not present in your local system. Be sure your database is owned by the user which is present in your hibernate.properties files, and that all tables belong to that user. Otherwise, you may need to alter the ownership of the database and tables to the user which is used to access the database.

Best regards,

Jason


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

On Mon, Oct 6, 2014 at 7:38 AM, Timothy Harding hardingt@gmail.com wrote:

Hello DHIS2 Developer’s List

I’m attempting to spin up a couple test instances of DHIS2 inside Ubuntu 14.04 and with both 2.14 and 2.16 and am encountering some problems.

If create the postgres user (dhis) and the db (dhis2) and do nothing else, I get the demo version (admin:district) for both 2.14 and 2.16. So tomcat and postgresql are talking and the server is capable of starting and serving pages. I then delete the DB, recreate a blank one and import a database dump from our production server (version 2.14). Things go well until I execute ./tomcat-dhis/bin/startup.sh this time, the system will fail to start either 2.14 or 2.16. Interestingly enough, I can do the exact same process in Windows 7 (Postgres and Tomcat again) and 2.14 starts up without issue, but I have not tested 2.16 in Windows yet. I’m encountering the following error after startup routine 11 of 11 is complete. I’ve included a snippet below from 2.14, and have attached the entire log to this email.

System: Ubuntu 14.04

PostgreSQL 9.3.5

Tomcat7

  • ERROR 2014-10-06 16:06:51,792 ERROR: permission denied for relation i18nlocale (SqlExceptionHelper.java [localhost-startStop-1])
  • ERROR 2014-10-06 16:06:51,812 org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

(…)

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation i18nlocale

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

(…)

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Error listenerStart

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Context [] startup failed due to previous errors

Any ideas or suggestions are appreciated. I’m both trying to learn how to use Ubuntu with DHIS2 (used the following: https://www.dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html) and attempting to test our migration from 2.14 to 2.16. Thanks!

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137


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

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Bob, Jason,

You are exactly right! It was an ownership issue. The dump probably did not have ownership information, so when I imported it came in as ‘postgres’:

postgres@dhis2-2:~$ pg_dump -s dhis2 | grep -i ‘owner to’ | head

ALTER SCHEMA private OWNER TO postgres;

ALTER TABLE private.facilities OWNER TO postgres;

ALTER TABLE private.islands_provinces OWNER TO postgres;

ALTER TABLE public._categoryoptioncomboname OWNER TO postgres;

(…)

I’m guessing this is because I didn’t alter the user I was importing from:

postgres@dhis2-2:~$ psql dhis2 < /media/sf_DHIS2-2.16/vanphis_post_import.sql

(ignore the 2.16 here, it is the name of the vm)

I found the sed statement you referenced, replaced the bash vars with the appropriate values and ran it (highlighted below):

postgres@dhis2-2:~$ pg_dump -s dhis2 | grep -i ‘owner to’ | sed -e “s/OWNER TO .*;/OWNER TO dhis;/i”|psql dhis2

ALTER SCHEMA

ALTER TABLE

ALTER TABLE

ALTER TABLE

(…)

After that it loads up just fine!

image

Thanks for your help guys! Much appreciated. Now to spin up a 2.16 instance.

···

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Mon, Oct 6, 2014 at 11:22 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

I suspect Jason is right.

People often backup the database with the ownership information intact, which is useful if you want to restore the database on the same system, eg recovering after a crash of some sort, where you want to get everything back to where it was.

Having the ownership in the backup is more problematic where the intention is to restore it somewhere else where the users may be different (and might not even exist)

| suggest you do the backup again, but this time ensure that you don’t include the ownership information. If using pg_dump the -O option ensures this. Then when you restore into a new database, all objects will be owned by the (new) database owner. I’m pretty sure there will be a similar option on pgadmin if you are using this.

Mind you, if the dump is in the postgres custom binary format , then you can use the -O option on restore and it will ignore ownership information. If it is a text based sql format then that option doesn’t work. You can massage the file (you can see an example using sed at the bottom of the script here https://github.com/dhis2/dhis2tools/blob/master/pkg/usr/bin/dhis2-restoredb))), but maybe its easier to just do the backup again as described at the start. Particularly if you are not using the command line tools.

The final alternative is to do a REASSIGN OWNED BY xxx TO nnn. This might also be straightforward if you can easily see who it is that is owning the database objects.

On a more general note, as an alternative to working through chapter 8 (which is useful for understanding some of the concepts) you might consider basing your installation on the dhis2-tools : https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20.html. Addressing this postgres permissions issue is one of the many gotchas which are dealt with by using these standard scripts.

Regards

Bob

On 6 October 2014 12:16, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi TIm,
Usually, this happens when a local database is restored from a remote dump which belongs to another user not present in your local system. Be sure your database is owned by the user which is present in your hibernate.properties files, and that all tables belong to that user. Otherwise, you may need to alter the ownership of the database and tables to the user which is used to access the database.

Best regards,

Jason


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

On Mon, Oct 6, 2014 at 7:38 AM, Timothy Harding hardingt@gmail.com wrote:

Hello DHIS2 Developer’s List

I’m attempting to spin up a couple test instances of DHIS2 inside Ubuntu 14.04 and with both 2.14 and 2.16 and am encountering some problems.

If create the postgres user (dhis) and the db (dhis2) and do nothing else, I get the demo version (admin:district) for both 2.14 and 2.16. So tomcat and postgresql are talking and the server is capable of starting and serving pages. I then delete the DB, recreate a blank one and import a database dump from our production server (version 2.14). Things go well until I execute ./tomcat-dhis/bin/startup.sh this time, the system will fail to start either 2.14 or 2.16. Interestingly enough, I can do the exact same process in Windows 7 (Postgres and Tomcat again) and 2.14 starts up without issue, but I have not tested 2.16 in Windows yet. I’m encountering the following error after startup routine 11 of 11 is complete. I’ve included a snippet below from 2.14, and have attached the entire log to this email.

System: Ubuntu 14.04

PostgreSQL 9.3.5

Tomcat7

  • ERROR 2014-10-06 16:06:51,792 ERROR: permission denied for relation i18nlocale (SqlExceptionHelper.java [localhost-startStop-1])
  • ERROR 2014-10-06 16:06:51,812 org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

(…)

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation i18nlocale

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

(…)

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Error listenerStart

Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext startInternal

SEVERE: Context [] startup failed due to previous errors

Any ideas or suggestions are appreciated. I’m both trying to learn how to use Ubuntu with DHIS2 (used the following: https://www.dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html) and attempting to test our migration from 2.14 to 2.16. Thanks!

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137


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

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

The joys of sed ...

Hopefully you will get a chance to try out the dhis2-tools some other
time. I have timed my typical installation from a scratch ubuntu with just
ssh running, down to 3 minutes - of course without the who web filter :slight_smile:

One of the important things it deals with is running multiple instances on
the same server. For example often we find people may run a production,
staging and training instance. In which case the method described for
serving static files as described in the manual doesn't work very well,

Anyway good to see you are up and running. Sorry it was such an effort ..
postgres is great but takes some getting used to.

Cheers
Bob

image

···

On 7 October 2014 00:34, Timothy Harding <hardingt@gmail.com> wrote:

Bob, Jason,

You are exactly right! It was an ownership issue. The dump probably did
not have ownership information, so when I imported it came in as 'postgres':

postgres@dhis2-2:~$ pg_dump -s dhis2 | grep -i 'owner to' | head
ALTER SCHEMA private OWNER TO postgres;
ALTER TABLE private.facilities OWNER TO postgres;
ALTER TABLE private.islands_provinces OWNER TO postgres;
ALTER TABLE public._categoryoptioncomboname OWNER TO postgres;
(...)

I'm guessing this is because I didn't alter the user I was importing from:
postgres@dhis2-2:~$ psql dhis2 <
/media/sf_DHIS2-2.16/vanphis_post_import.sql
*(ignore the 2.16 here, it is the name of the vm)*

I found the sed statement you referenced, replaced the bash vars with the
appropriate values and ran it (highlighted below):
postgres@dhis2-2:~$ pg_dump -s dhis2 | grep -i 'owner to' | sed -e
"s/OWNER TO .*;/OWNER TO dhis;/i"|psql dhis2
ALTER SCHEMA
ALTER TABLE
ALTER TABLE
ALTER TABLE
(...)

After that it loads up just fine!
[image: Inline image 1]

Thanks for your help guys! Much appreciated. Now to spin up a 2.16
instance.

Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@gmail.com
+678 5955137

On Mon, Oct 6, 2014 at 11:22 PM, Bob Jolliffe <bobjolliffe@gmail.com> > wrote:

I suspect Jason is right.

People often backup the database with the ownership information intact,
which is useful if you want to restore the database on the same system, eg
recovering after a crash of some sort, where you want to get everything
back to where it was.

Having the ownership in the backup is more problematic where the
intention is to restore it somewhere else where the users may be different
(and might not even exist)

> suggest you do the backup again, but this time ensure that you don't
include the ownership information. If using pg_dump the -O option ensures
this. Then when you restore into a new database, all objects will be owned
by the (new) database owner. I'm pretty sure there will be a similar
option on pgadmin if you are using this.

Mind you, if the dump is in the postgres custom binary format , then you
can use the -O option on restore and it will ignore ownership information.
If it is a text based sql format then that option doesn't work. You can
massage the file (you can see an example using sed at the bottom of the
script here
https://github.com/dhis2/dhis2tools/blob/master/pkg/usr/bin/dhis2-restoredb\)),
but maybe its easier to just do the backup again as described at the
start. Particularly if you are not using the command line tools.

The final alternative is to do a REASSIGN OWNED BY xxx TO nnn. This
might also be straightforward if you can easily see who it is that is
owning the database objects.

On a more general note, as an alternative to working through chapter 8
(which is useful for understanding some of the concepts) you might consider
basing your installation on the dhis2-tools :
https://www.dhis2.org/doc/snapshot/en/implementer/html/ch20.html\.
Addressing this postgres permissions issue is one of the many gotchas which
are dealt with by using these standard scripts.

Regards
Bob

On 6 October 2014 12:16, Jason Pickering <jason.p.pickering@gmail.com> >> wrote:

Hi TIm,
Usually, this happens when a local database is restored from a remote
dump which belongs to another user not present in your local system. Be
sure your database is owned by the user which is present in your
hibernate.properties files, and that all tables belong to that user.
Otherwise, you may need to alter the ownership of the database and tables
to the user which is used to access the database.

Best regards,
Jason

On Mon, Oct 6, 2014 at 7:38 AM, Timothy Harding <hardingt@gmail.com> >>> wrote:

Hello DHIS2 Developer's List

I'm attempting to spin up a couple test instances of DHIS2 inside
Ubuntu 14.04 and with both 2.14 and 2.16 and am encountering some problems.

If create the postgres user (dhis) and the db (dhis2) and do nothing
else, I get the demo version (admin:district) for both 2.14 and 2.16. So
tomcat and postgresql are talking and the server is capable of starting and
serving pages. I then delete the DB, recreate a blank one and import a
database dump from our production server (version 2.14). Things go well
until I execute ./tomcat-dhis/bin/startup.sh this time, the system
will fail to start either 2.14 or 2.16. Interestingly enough, I can do the
exact same process in Windows 7 (Postgres and Tomcat again) and 2.14 starts
up without issue, but I have not tested 2.16 in Windows yet. I'm
encountering the following error after startup routine 11 of 11 is
complete. I've included a snippet below from 2.14, and have attached the entire
log to this email.

System: Ubuntu 14.04
PostgreSQL 9.3.5
Tomcat7

* ERROR 2014-10-06 16:06:51,792 ERROR: permission denied for relation
i18nlocale (SqlExceptionHelper.java [localhost-startStop-1])
* ERROR 2014-10-06 16:06:51,812
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
        at
org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
        at
org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
(...)
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied
for relation i18nlocale
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
(...)
Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext
startInternal
SEVERE: Error listenerStart
Oct 06, 2014 4:06:51 PM org.apache.catalina.core.StandardContext
startInternal
SEVERE: Context [] startup failed due to previous errors

Any ideas or suggestions are appreciated. I'm both trying to learn how
to use Ubuntu with DHIS2 (used the following:
https://www.dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html\)
and attempting to test our migration from 2.14 to 2.16. Thanks!

Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@gmail.com
+678 5955137

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

--
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

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