Issue
I am using the below script "load.sh" to run a sql script in postgresql:
#!/bin/bash
gawk -i inplace '!a[$0]++' *.txt
mv *.txt txtdir/
sudo -u postgres psql -d datdb -f /var/lib/postgresql/run.sql
exit
If I run it as just ./load.sh from the command line as root it works perfectly.
The .sql truncates some tables and loads some data. No issues.
However, if I run it as a cron job, the .sql script does not work.
Nothing changes, but for the fact it is scheduled from cron.
If I pipe the shell script to a logfile using:
crontab -l
0 0,2,4,6,8,10,12,14,16,18,20,22 * * * /txtdir/load.sh > /txtdir/load.log
The shell script runs I can see, and works and the log does not contain any errors but the sql parts are simply absent from the log and I can see the tables are not being loaded, it is not working from cron.
If I run it all as below though from a terminal session:
/txtdir/load.sh > /txtdir/load.log
Everything is perfect, I can see the data load references in the log, data is loaded to the tables.
I cannot understand why this would work from the terminal but not from cron whilst both being run by the same user.
Solution
Cron's default PATH on Linux and macOS is PATH=/usr/bin:/bin. For Linux, psql is usually installed in /usr/bin, so it shouldn't be the issue.
The other issue is sudo, it will have a number of issues running under cron. First, it will attempt to set up a pty. So on Ubuntu 22 use this:
sudo -b -n -H -u postgres
Second, you must modify your /etc/sudoers file by running sudo visudo and adding a line like this at the bottom:
%sudo ALL=(postgres) NOPASSWD: /usr/bin/psql
This allows anyone with sudo permission to run /usr/bin/psql to postgres on any host (ALL) with no password.
I add this line for psql to your load.sh:
cd /txtdir; sudo -b -n -H -u postgres psql -d datdb -f run.sql
Add this line to your personal crontab (not-root)
* * * * * /txtdir/load.sh > /txtdir/load.log 2>&1
The 2>&1 changes where stderr is delivered, in this case to the same output as stdout.
Answered By - James Risner Answer Checked By - Marie Seifert (WPSolving Admin)