In order to restore a DHIS database I usually do the followings:
Implement a new DHIS instance with the exact version running on the DB to be imported/restored.
I test the that the newly instance is functional by login into it before restoring the DB.
I delete the clean dhis2 DB and re-create it
Install the PostGiS extension
Restore the DB
Run a set of queries to assign views, tables, etc to the dhis user.
I log back in the Postgres dhis2 DB from the shell using the dhis user credentials to make sure all the rights are set correctly
Launch my web browser and connect to the newly restored site and smile.
I have done this process a couple of dozens of times and never had an issue. Two weeks ago, I did the same to restore a 2.30 DHIS instance and it would not work. A fresh install of 2.30 works fine, but not the DB restored. It complains about PostGIS not installed which is (a fresh installed of 2.30 would not work with without PostGIS extension) . I would appreciate any help to resolve this so that I can smile again. Below are my systems info
System info:
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION=“Ubuntu 16.04.6 LTS”
Packages installed:
ii libpostproc-ffmpeg53:amd64 7:2.8.15-0ubuntu0.16.04.1 amd64 FFmpeg library for post processing - runtime files
ii libxine2-misc-plugins 1.2.6-1build5 amd64 Input, audio output and post plugins for libxine2
ii pgdg-keyring 2018.2 all keyring for apt.postgresql.org
ii postgresql-9.5 9.5.16-1.pgdg16.04+1 amd64 object-relational SQL database, version 9.5 server
ii postgresql-9.5-postgis-2.2 2.2.2+dfsg-7~137.gite11228b.pgdg16.04+1 amd64 Geographic objects support for PostgreSQL 9.5
ii postgresql-9.5-postgis-2.2-scripts 2.2.2+dfsg-7~137.gite11228b.pgdg16.04+1 all Geographic objects support for PostgreSQL 9.5 – scripts
ii postgresql-client-9.5 9.5.16-1.pgdg16.04+1 amd64 front-end programs for PostgreSQL 9.5
ii postgresql-client-common 199.pgdg16.04+1 all manager for multiple PostgreSQL client versions
ii postgresql-common 199.pgdg16.04+1 all PostgreSQL database-cluster manager
ii postgresql-contrib-9.5 9.5.16-1.pgdg16.04+1 amd64 additional facilities for PostgreSQL
I upgraded to Postgresql 9.6 to no satisfaction.
Below Catalina output
–
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘databaseInfoProvider’ defined in URL [jar:file:/home/dhis/tomcat-dhis/webapps/ROOT/WEB-INF/lib/dhis-support-system-2.30.jar!/META-INF/dhis/beans.xml]: Invocation of init method failed; nested exception is java.lang.IllegalStateException: Postgis extension is not installed. Execute “CREATE EXTENSION postgis;” as a superuser and start the application again.
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1631)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:297)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:208)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1136)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1064)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:583)
… 55 more
Caused by: java.lang.IllegalStateException: Postgis extension is not installed. Execute “CREATE EXTENSION postgis;” as a superuser and start the application again.
at org.hisp.dhis.system.database.HibernateDatabaseInfoProvider.init(HibernateDatabaseInfoProvider.java:74)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1760)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1697)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1627)
@sagbodjan Dhis 2.30 on startup executes SELECT POSTGIS_FULL_VERSION() to verify if the extension is installed on the connecting database. If that fails, dhis throws this error and fails to startup.
Can you make sure the extension is installed by executing the above script on the db?
Thanks to @jomutsani and @Ameen. The issue was with the DB dump obtained which had non standard geometry type. I had to change the PostGIS path initially to get it restored which I guess affected DHIS2 application which did not see PostGIS extension installed.