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

Scouse_Bob 0
Viewed 54

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.

shell bash cron postgresql
1 Answers

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.

James Risner0
Related