dhis2-devs team mailing list archive

[Timothy Harding <hardingt@xxxxxxxxx>]

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@xxxxxxxxx
+678 5955137

On Mon, Oct 6, 2014 at 11:22 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx> 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@xxxxxxxxx>
> 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@xxxxxxxxx>
>> 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@xxxxxxxxx
>>> +678 5955137
>>>
>>> _______________________________________________
>>> Mailing list: https://launchpad.net/~dhis2-devs
>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>>>
>>
>>
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+46764147049
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>