pgagent schedual job not running

Question

pgagent schedual job not running

Opened this issue 7 years ago · 6 comments

I couldn't get the pgagent job running, the log file is attached.
Thanks
Bin
postgresql-2017-03-22_112755.zip

Answer 1 · 2017-03-22T13:23:31.000Z

Looks like this is actually a build failure, not pgagent job related. It seems your build died very early on, I can see on line 14 of the log:

2017-03-22 11:33:17 GMT LOG:  statement: /* add matviews for openclinica tables to cache them locally for reporting */
	SELECT dm_create_ft_openclinica_matviews();
2017-03-22 11:33:17 GMT ERROR:  permission denied for relation archived_dataset_file

This permission denied error indicates to me that you need to do the foreign database preparation, e.g. create a user that has select permissions on all the tables in the openclinica database schema. Specifically, this step:

https://github.com/lindsay-stevens/openclinica_sqldatamart/blob/2016.6/docs/setup/basic.md#steps-to-complete-on-oc-server

Answer 2 · 2017-03-22T15:39:35.000Z

I did those permissions on the oc server, and once stage it was running somehow (not via the agent though) I have downloaded all the views from the oc server, but never got the pgagent running, I reinstalled postgres (9.6 and pgagent), but never got them working again. I didn't change anything on the oc server, assumed all the permission is working.
Does the postgres password has to be the same on oc and ocdm server?

Answer 3 · 2017-03-22T17:35:38.000Z

I don't follow what you mean. In particular, what do you mean by "downloaded all the views"? The log you sent looks like a failed run of the build script. Either the permissions on the foreign server were not set, or the username and password entered in the batch script are incorrect, or the job is being run by an unmapped user. To give some context: ocdm tries to copy data from OC via foreign tables. A local user is mapped to a foreign user via the user mapping in the postgres_fdw server. Check that the user you're running the script as is the same as that role. For example, maybe you have a mapping for local user 'dm_admin' to foreign user 'oc_fdw'. If you run a maintenance job just as postgres it won't work, since postgres isn't mapped to 'oc_fdw'. You can add a mapping, or set the session role to 'dm_admin'. Im pretty sure I covered these intricacies in the instructions so maybe try to start over from the beginning.

Answer 4 · 2017-03-24T09:11:33.000Z

I have started the whole process from the beginning, and still stuck with "the permission denied for relation archived_dataset_file", where does this permission problem occur, on the oc server or on ocdm server?

I managed get the oc data into the ocdm db, but they are in the openclinica_fdw views, is that correct?

Here is one screenshots of my ocdm db.

Answer 5 · 2017-03-27T09:29:33.000Z

I found the problem! It was at the stage to run the setup_sqldatamart, I didn't run it under super user account ( or under windows run the script as administrator).
Many thanks for your help.

Answer 6 · 2017-03-27T10:41:17.000Z

Hi I spoke too soon, it seems the previous problem was about the setup wasn't running properly. I used the maintains script you provided, only changed my_fqdn AS (SELECT 'svr-ocdm-pSQL9.ad.nchecr.unsw.edu.au' AS fqdn), to my_fqdn AS (SELECT '127.0.0.1' AS fqdn), but seems the pgagent is not running, I didn't get any jobs successful in the statistics tab. actually nothing at all.
postgresql-2017-03-27_010000.zip