issues backing up postgres databases in cron

Question

I am trying to backup postgres databases. I am running a cron job to do so. Issue is that postgres runs under user postgres and I dont think I can run a cron job under ubuntu user. I tried to create a cron job under postgres user and that also did not work. My script, if login as postgres user works just fine. Here is my script

#!/bin/bash
# Location to place backups.
backup_dir="/home/postgres-backup/"
#String to append to the name of the backup files
backup_date=`date +%d-%m-%Y`
#Numbers of days you want to keep copie of your databases
number_of_days=30
databases=`psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
for i in $databases; do
  if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
    echo Dumping $i to $backup_dir$i\_$backup_date
    pg_dump -Fc $i > $backup_dir$i\_$backup_date
  fi
done
find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;

if I do

sudo su - postgres

I see

-rwx--x--x 1 postgres postgres  570 Jan 12 20:48 backup_all_db.sh

and when I do

./backup_all_db.sh

it gets backed up in /home/postgres-backup/

however with cronjob its not working, regardless if I add the cron job under postgres or under ubuntu.

here is my cronjob

0,30 * * * * /var/lib/pgsql/backup_all_db.sh 1> /dev/null 2> /home/cron.err

Will appreciate any help

Answer 1

Enable user to run cron jobs

If the /etc/cron.allow file exists, then users must be listed in it in order to be allowed to run the crontab command. If the /etc/cron.allow file does not exist but the /etc/cron.deny file does, then users must not be listed in the /etc/cron.deny file in order to run crontab.

In the case where neither file exists, the default on current Ubuntu (and Debian, but not some other Linux and UNIX systems) is to allow all users to run jobs with crontab.

Add cron jobs

Use this command to add a cron job for the current user:

crontab -e

Use this command to add a cron job for a specified user (permissions are required):

crontab -u <user> -e

Additional reading

man 5 crontab

Crontab in Ubuntu: https://help.ubuntu.com/community/CronHowto