Postgres error - relation does not exist

dbannikov
Participant I

I've added a new environment prod using apigee-adminapi script. After that I ran apigee-provision enable-ax.

When I switch to the prod environment in Apigee UI, I see "Error fetching analytics data Data Access Error.".

In Postgres logs I see this error (<ORG_NAME> - my org name):

SELECT developer,developer_app,sum(message_count) AS mtrc0,sum(error_count) AS mtrc1,date_trunc('month', timestamp at time zone '-0:0:0')::timestamp without time zone AS time_unit FROM analytics."<ORG_NAME>.prod.agg_app" WHERE timestamp >= '2017-5-26 22:0:0' AND timestamp < '2017-6-2 22:0:0' AND ((developer_app != 'na' ) and (developer_app != '(not set)' )) AND developer_app IN ( SELECT developer_app FROM ( SELECT developer_app FROM analytics."<ORG_NAME>.prod.agg_app" WHERE timestamp >= '2017-5-26 22:0:0' AND timestamp < '2017-6-2 22:0:0' AND ((developer_app != 'na' ) and (developer_app != '(not set)' )) GROUP BY developer,developer_app ORDER BY sum(message_count) DESC,sum(error_count) DESC LIMIT 5 OFFSET 0 ) AS subquery ) GROUP BY developer,developer_app,time_unit ORDER BY sum(message_count) DESC,sum(error_count) DESC LIMIT 14400 OFFSET 0 2017-06-02 22:56:43 UTC [10011]: [61-1] user=<USERNAME> db=apigee host=10.227.14.26 ERROR: relation "analytics.<ORG_NAME>.prod.agg_app" does not exist at character 146

Solved Solved
0 9 4,842
1 ACCEPTED SOLUTION

Not applicable
@Dmitriy Bannikov in addition to the instructions Maruti provided, check the analytics group definition to make sure Postgres UUID is present.
curl -u <sysAdminEmail>:<passwd> "http://<ms_IP>:8080/v1/analytics/groups/ax"


As Maruti mentioned, for the problem you are describing restarting edge-postgres-server should fix the issue.

View solution in original post

9 REPLIES 9

Not applicable

@Dmitriy Bannikov Can you check the output of the below APIs and share if possible ?

curl -v -u <sysAdminEmail>"http://<ms-ip>:8080/v1/servers?pod=analytics&region=dc-1" 

you should see both [“reportcrud-datastore”, “analytics-datastore”] in the output which points to your cassandra IP and

curl -v -u <sysAdminEmail> http://<ms-ip>:8080/v1/o/<orgname>/e/<envname>/provisioning/axstatus

@Maruti Chand First curl returned whatever was expected - both [“reportcrud-datastore”, “analytics-datastore”] point to Cassandra hosts, and [ "postgres-server" ] points to the Postgres machine.

Second one returned this:

curl -u <sysAdminEmail> http://<ms-ip>:8080/v1/o/<ORG_NAME>/e/prod/provisioning/axstatus

{ "environments": [ { "components": [ { "hosts": "[localhost]", "message": "success", "name": "qs", "status": "SUCCESS", "uuid": "[<QPID_SERVER_UUID>]" }, { "hosts": "[localhost]", "message": "", "name": "pg", "status": "UNKNOWN", "uuid": "[<POSTGRES_SERVER_UUID>]" } ], "message": "", "name": "prod" } ], "organization": "<ORG_NAME>", "status": "FAILURE" }

@Dmitriy Bannikov

"pg", "status": "UNKNOWN" is the problem

Can you restart the postgres server and check the axstatus again? I remember earlier I had to restart pg whenever we add an environment.

If that doesn't work check

#1 The UUID of the pg server from the /v1/servers output that you already ran and the UUID from the below curl -v http://localhost:8084/v1/servers/self output is same.

Not applicable
@Dmitriy Bannikov in addition to the instructions Maruti provided, check the analytics group definition to make sure Postgres UUID is present.
curl -u <sysAdminEmail>:<passwd> "http://<ms_IP>:8080/v1/analytics/groups/ax"


As Maruti mentioned, for the problem you are describing restarting edge-postgres-server should fix the issue.

@Maudrit @Maruti Chand thanks for your help!

Restart did the job for all my environments except one - DEV. Looks like I've created a second analytics group for this environment at some point, and it causes Data Access Errors.

I used apigee-adminapi tool to remove everything from this second analytics groups. However, I'm not able to delete the group itself.

This is how the group looks like after the cleanup:

{ "name" : "axgroup-002", "properties" : { }, "scopes" : [ "<ORG>~dev" ], "uuids" : { "qpid-server" : [ ], "aries-datastore" : [ ], "postgres-server" : [ ] }, "consumer-groups" : [ ], "data-processors" : { } }

As you can see, no servers or consumer groups present in this group anymore.

And this is what I get when I try to delete it:

/opt/apigee/apigee-adminapi/bin/apigee-adminapi.sh analytics groups delete -g axgroup-002 --admin <sysAdminEmail> --pwd <sysAdminPassword> --host 127.0.0.1

Proceed with API call: [N/y]: y

{ "error" : "Group must be empty before it can be deleted" }

I've restarted Postgres (both apigee-postgresql and edge-postgres-server) - it did not help.

@Dmitriy Bannikov I think you still have the scope in that axgroup.

First delete the scope with this API and then delete the group.

-RemoveScope 

curl -v -u admin:secret -X DELETE "http://${mgmt}:8080/v1/analytics/groups/ax/axgroup6/scopes?org=Org&env=Env"

@Maruti Chand

Great! It worked! Thanks a lot!

How did you solve this issue? I am getting the same error.

Hi!
This error can be caused if you created a new environment, but did not enable analytics for that env. The command you need to run will look something like this:

/opt/apigee/apigee-service/bin/apigee-service apigee-provision enable-ax -f configFile


You can follow the steps outlined below to do this. You may also need to restart your env once you run this command.

[1] https://docs.apigee.com/private-cloud/v4.50.00/creating-organization-environment-and-virtual-host#cr...