Cron job failing to backing up a Postgres database

backupcronpostgresql

I'm unsure what's going on here:

I've got a backup script which runs fine under root. It produces a >300kb database dump in the proper directory.

When running it as a cron job with exactly the same command however, an empty gzip file appears with nothing in it.

The cron log shows no error, just that the command has been run.

This is the script:

#! /bin/bash

DIR="/opt/backup"
YMD=$(date "+%Y-%m-%d")
su -c "pg_dump -U postgres mydatabasename | gzip -6 > "$DIR/database_backup.$YMD.gz" " postgres

# delete backup files older than 60 days
OLD=$(find $DIR -type d -mtime +60)
if [ -n "$OLD" ] ; then
    echo deleting old backup files: $OLD
    echo $OLD | xargs rm -rfv
fi

When changed to:

 pg_dump -U postgres mydatabasename | gzip -6 > "$DIR/database_backup.$YMD.gz"

The same thing happens.

And the cron job:

01 10 * * * root sh /opt/daily_backup_script.sh

It produces a database_backup file, just an empty one. Anyone know what's going on here?

edit:

Ok, simplified to this but it's still not working via cron

#! /bin/bash

DIR="/opt/backup"
YMD=$(date "+%Y-%m-%d")

pg_dumpall -U postgres > "$DIR/database_backup.$YMD"

And

01 10 * * * root /opt/daily_backup_script.sh

Best Answer

You need to specify the full path to pg_dump -- cron runs it's scripts with a very restrictive PATH by default.

Related Solutions

Cron Job – How to Run a Cron Job Manually and Immediately

Here's what I did, and it seems to work in this situation. At least, it shows me an error, whereas running from the command line as the user doesn't show the error.

Step 1: I put this line temporarily in the user's crontab:

* * * * *   /usr/bin/env > /home/username/tmp/cron-env

then took it out once the file was written.

Step 2: Made myself a little run-as-cron bash script containing:

#!/bin/bash
/usr/bin/env -i $(cat /home/username/tmp/cron-env) "$@"

So then, as the user in question, I was able to

run-as-cron /the/problematic/script --with arguments --and parameters

This solution could obviously be expanded to make use of sudo or such for more flexibility.

Hope this helps others.

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Related Topic