dhis2-devs team mailing list archive

[Timothy Harding <hardingt@xxxxxxxxx>]

Thanks Jason for the direction, let me give that a shot.

So I've just enabled logging inside the postgres.conf and looks like I may
have found it here:
2014-10-09 11:28:31 VUT ERROR:  cannot drop table patientidentifiertype
because other objects depend on it
2014-10-09 11:28:31 VUT DETAIL:  constraint
fk_program_patientidentifiertypes_patientidentifiertypeid on table
program_patientidentifiertypes depends on table patientidentifiertype
2014-10-09 11:28:31 VUT HINT:  Use DROP ... CASCADE to drop the dependent
objects too.
2014-10-09 11:28:31 VUT STATEMENT:  DROP TABLE patientidentifiertype

As an aside, stackoverflow mentioned that logging was not on by default for
postgres, but there* is* a 1.1MB file in my /var/log/postgres/ directory,
so something was taking STDERR and putting into a file)
dhis@dhis216:/var/log/postgresql$ ls -la
total 1124
drwxrwxr-t  2 root     postgres    4096 Oct  9 11:13 .
drwxrwxr-x 10 root     syslog      4096 Oct  9 11:13 ..
-rw-------  1 postgres postgres     278 Oct  9 11:13
postgresql-2014-10-09_111335.log
-rw-r-----  1 postgres adm      1122520 Oct  9 11:13 postgresql-9.3-main.log
-rw-r-----  1 postgres adm         6537 Oct  5 06:38
postgresql-9.3-main.log.1

So I'm guessing the table is: patientidentifiertype, which just needs a
CASCADE appending to the DROP query. Curious, I finally took a look at the
pastebin link and lo and behold:
DROP TABLE patientidentifiertype CASCADE;

Thanks for that Jason, we are getting closer now :).

Sadly, it seems to be hanging on something else now (still Step 7), the
last lines from the log file (attached) are:
2014-10-09 11:50:36 VUT ERROR:  column "groupby" of relation
"trackedentityattribute" does not exist
2014-10-09 11:50:36 VUT STATEMENT:  ALTER TABLE trackedentityattribute DROP
COLUMN groupBy
but that may not be what is hanging, as entire log file is filled with
attempts to ALTER followed by 'does not exist', but trackedentity does
coorespond with the step:
* INFO  2014-10-09 11:50:35,345 Executing startup routine [7 of 14,
runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java
[localhost-startStop-1])

Any help is appreciate, thanks again.


Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@xxxxxxxxx
+678 5955137

On Wed, Oct 8, 2014 at 10:32 PM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

> Hi Tim,
> Ah, the haze of travel. I did not see that you had already applied the
> upgrade script. I had a problem with your database, which others may also
> experience when upgrading from 2.14 to 2.16.. This error is visible if you
> carefully examine the postgresql logs. So, it is a good exercise to figure
> out where they are. I am pretty sure they show up at the default logging
> level, but you may also need to increase the logging level to see each
> query which is executed. After that, you can trawl through the logs and see
> what is causing the error. I will give you a hint....a table which needs to
> be dropped cannot due to a foreign key constraint.
>
> If you want to skip to the spoiler, I have posted some SQL here (
> http://pastebin.com/0UCGVhx7) which should solve your problem. :)
>
> Best regards,
> Jason
>
>
> On Wed, Oct 8, 2014 at 2:27 AM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
> wrote:
>
>> Hi Jason
>>
>> That looks like the same script Tim says he has applied.  Must be
>> something else.
>>
>> @Tim all those postgres processes represent the pool of connections
>> between dhis and postgres.  The pool parameters are fixed somewhere deep in
>> the webapp but I think you can expect the default is going between
>> something like 10(min) and 40(max).  Postgres is traditionally
>> multi-process rather than multi-threaded - a bit slower in terms of context
>> switching but very stable.  One crazy thread can't bring down the server
>> process.
>>
>> Bob
>>
>>
>> On 8 October 2014 01:08, Jason Pickering <jason.p.pickering@xxxxxxxxx>
>> wrote:
>>
>>> Hi Tim,
>>> You may want to look here for the necessary upgrade script.
>>>
>>>
>>> http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql
>>>
>>> Regards,
>>> Jason
>>>  On Oct 7, 2014 7:41 PM, "Timothy Harding" <hardingt@xxxxxxxxx> wrote:
>>>
>>>> Hello Developer's Group
>>>>
>>>> I'm now working on migrating from 2.14 to 2.16 in our test environment
>>>> and the startup for tomcat is hanging on step 7. I've included some
>>>> relevant snippets of logs below and have attached the full logs:
>>>>
>>>>    - stdout.txt = Standard out during importing the DHIS2 2.14
>>>>    database, running the upgrade script, and starting tomcat)
>>>>    - catalina.out = The tomcat log for the latest attempt to start up
>>>>    2.16
>>>>
>>>> My Notes:
>>>> I can boot up 2.14 with the database dump without problem
>>>> I can boot up 2.16 with a *blank* database without problem (it creates
>>>> the live environment i.e. admin:district)
>>>> The problem occurs when I try to boot up 2.16 with the 2.14 database
>>>> (after I've run the 2.15 upgrade script found here:
>>>> http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql
>>>> )
>>>> It hangs at step 7 of 14 * INFO  2014-10-08 09:45:03,291 Executing
>>>> startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror
>>>> (DefaultStartupRoutineExecutor.java [localhost-startStop-1])
>>>> The system, for having such meager hardware, is really snappy with both
>>>> the live version of 2.16, and our full production mirror of 2.14.
>>>> The com.hazelcast.util.HealthMonitor continues to function even after
>>>> the initialization process hangs at step 7 of 14 (as you can see in
>>>> catalina.out) Oct 08, 2014 10:36:58 AM com.hazelcast.util.HealthMonitor
>>>>
>>>> Both Java and Postgres are using no cpu cycles after the hang, so it
>>>> doesn't *feel* like it is churning away at something:
>>>>
>>>>   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+
>>>> COMMAND
>>>>  1413 dhis      20   0 1895572 570840  16532 S   1.0 44.7   1:48.39 java
>>>>   906 postgres  20   0  523944  30260  28868 S   0.0  2.4   0:00.47
>>>> postgres
>>>>   908 postgres  20   0  524704  77648  75724 S   0.0  6.1   0:01.04
>>>> postgres
>>>>   909 postgres  20   0  524140   4304   2904 S   0.0  0.3   0:00.16
>>>> postgres
>>>>   910 postgres  20   0  524140  14056  12656 S   0.0  1.1   0:00.50
>>>> postgres
>>>>   911 postgres  20   0  524980   3068   1096 S   0.0  0.2   0:00.11
>>>> postgres
>>>>   912 postgres  20   0  103712   1984    352 S   0.0  0.2   0:00.71
>>>> postgres
>>>>  1432 postgres  20   0  526508   7656   4524 S   0.0  0.6   0:00.00
>>>> postgres
>>>>  1433 postgres  20   0  529308  14604   8948 S   0.0  1.1   0:00.22
>>>> postgres
>>>>  1434 postgres  20   0  536664  46672  32832 S   0.0  3.7   0:08.00
>>>> postgres
>>>>  1904 postgres  20   0  525372   5652   3204 S   0.0  0.4   0:00.00
>>>> postgres
>>>>  1906 postgres  20   0  525256   4860   2548 S   0.0  0.4   0:00.00
>>>> postgres
>>>>  1907 postgres  20   0  525256   4856   2540 S   0.0  0.4   0:00.00
>>>> postgres
>>>>  2114 postgres  20   0  525372   5600   3192 S   0.0  0.4   0:00.00
>>>> postgres
>>>> (there are a lot of postgres processes it seems like)
>>>>
>>>> I saw some errors on startup but the notes for 2.16 says to expect a
>>>> few the first time. I also saw some errors when I ran the import and 2.15
>>>> upgrade script.
>>>> Database import (grep for errors)
>>>> ERROR:  must be owner of extension plpgsql
>>>>
>>>> Upgrade script (grep for errors)
>>>> ERROR:  table "patientaggregatereportmembers" does not exist
>>>> ERROR:  table "patienttabularreportmembers" does not exist
>>>> ERROR:  table "patientregistrationform_attributes" does not exist
>>>> ERROR:  table "patientregistrationform_fixedattributes" does not exist
>>>> ERROR:  table "patientregistrationform_identifiertypes" does not exist
>>>> ERROR:  table "patientregistrationform_attributes" does not exist
>>>> ERROR:  table "patientmobilesetting" does not exist
>>>>
>>>> Any ideas? Is there an upgrade script I've forgotten to run or maybe a
>>>> setting I've missed?
>>>>
>>>> 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
>>>>
>>>>
>>> _______________________________________________
>>> 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
>

Attachment: postgresql-2014-10-09_114547.log
Description: Binary data