Monday, January 31, 2022

[SOLVED] Cron job not able to establish connection with postgresql database

Issue

I have tried to run the cron job with the following shell script. 

#!/bin/bash

PATH=/opt/someApp/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

gsutil cp gs://api-bucket/order_status.csv order_status.csv

psql -d  apidb -U apidb  << EOF

DELETE FROM apidbgermany.order_status;
\copy apidb.order_status from '/u01/app/postgres/data/silver01/order_status.csv'  delimiter ',' CSV ;
EOF

My cronjob looks like this.

* * * * * /u01/app/postgres/data/silver01/order_status.sh &>/tmp/mycommand.log

getting following error

But if i execute the shell file manually in command line it is working great.


Solution

To access remote server you should execute command psql on this way:

psql -h postgress_host -d  apidb -U apidb  << EOF
....

otherwise psql try to connect local server. And when create cron scripts always use fill path to the executables 

/usr/bin/psql ....

(for example)

and add as second line of your script line like:

source ~/.bash_profile

or

source ~/.bashrc

depend on where your environment settings are



Answered By - Romeo Ninov
Answer Checked By - Timothy Miller (WPSolving Admin)
at