0

I am trying to execute a query on postgres pod in k8s via bash script but cannot get results when i select a large number of columns. Here is my query:

kubectl exec -it postgres-pod-dcd-wvd -- bash -c "psql -U postgres -c \"Select json_build_object('f_name',json_agg(f_name),'l_name',json_agg(l_name),'email',json_agg(email),'date_joined',json_agg(date_joined),'dep_name',json_agg(dep_name),'address',json_agg(address),'zip_code',json_agg(zip_code),'city',json_agg(city), 'country',json_agg(country)) from accounts WHERE last_name='ABC';\""

When i reduce the number of columns to be selected in the query, i get the results but if I use all the column names, the query just hangs indefinitely. What could be wrong here?

Update:

I tried using the query as :

kubectl exec -it postgres-pod-dcd-wvd -- bash -c "psql -U postgres -c \"Select last_name,first_name,...(other column names).. row_to_json(accounts) from register_account WHERE last_name='ABC';\""

But this also hangs.

Improve this question
2
  • Anything in the pod logs? Commented Oct 28, 2020 at 9:51
  • @RamanSailopal Thanks for the reply. No there is nothing in the logs. The query just hangs Commented Oct 28, 2020 at 9:54

2 Answers 2

Reset to default
1

When i try from inside the pod, It works but i need to execute it via bash script

Means it is almost certainly the results pagination; when you run exec -t it sets up a TTY in the Pod, just like you were connected interactively, so it is likely waiting for you to press space or "n" for the next page

You can disable the pagination with env PAGER=cat psql -c "select ..." or use the --pset pager=off as in psql --pset pager=off -c "Select ..."

Also, there's no need to run bash -c unless your .bashrc is setting some variables or otherwise performing work in the Pod. Using exec -- psql should work just fine, all other things being equal. You will need to use the env command if you want to go with the PAGER=cat approach, because $ ENV=var some_command is shell syntax, and thus cannot be fed directly into exec

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

As the resulting columns are having a lot of json processing, I think the time taken to execute these two queries are different.

Maybe you can login into the pod and execute the query and see.

kubectl exec -it postgres-pod-dcd-wvd -- bash

Now you are inside the pod. Then we can execute the query.

# psql -U postgres -c \"Select json_build_object('f_name',json_agg(f_name),'l_name',json_agg(l_name),'email',json_agg(email),'date_joined',json_agg(date_joined),'dep_name',json_agg(dep_name),'address',json_agg(address),'zip_code',json_agg(zip_code),'city',json_agg(city), 'country',json_agg(country)) from accounts WHERE last_name='ABC';\"

# psql -U postgres -c \"Select last_name,first_name,...(other column names).. row_to_json(accounts) from register_account WHERE last_name='ABC';\"

Now you we will be able to see whether one query is taking longer time to execute.

Also, kubectl exec pod command can be executed with a request timeout value (--request-timeout=5m) to see if there is a slowness.

Improve this answer

2 Comments

@Ikamal thank you for the answer. When i try from inside the pod, It works but i need to execute it via bash script
@devcloud yes, but first you will need to figure out the time taken by these two queries. For that you can login into the pod and see. If one query is taking a longer time, you will have to set the --request-timeout to a higher number inside your script.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.