Postgresql SQL script runs in shell script from terminal but not from cron-CodePudding

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.

CodePudding user response：

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.