PostgreSQL : Backup script creating only once backup instead of hourly

Question

I am working on a Debian system, where I have a PostgreSQL server running. I modified a backup script and installed a cron job to take hourly backups of our database, and store it in a folder. Along with that, there is a script executed, which deletes backups which are older than 3 days. I had installed that same script on 3 servers, and I saw that there was only one backup present. Instead of 15, as 15 hours have passed. What am I doing wrong, or is the deletion script wrong?

post_back.sh :

#! /bin/bash
DUMPALL='/usr/bin/pg_dumpall'
PGDUMP='/usr/bin/pg_dump'
PSQL='/usr/bin/psql'

# directory to save backups in, must be rwx by postgres user
BASE_DIR='/media/attachment/backups/postgres'
YMD=$(date "+%Y-%m-%d-%T")
DIR="$BASE_DIR/$YMD"
mkdir -p "$DIR"
cd "$DIR"

# get list of databases in system , exclude the tempate dbs
DBS=($($PSQL --list --tuples-only |
          awk '!/template[01]/ && $1 != "|" {print $1}'))

# first dump entire postgres database, including pg_shadow etc.
$DUMPALL --column-inserts | gzip -9 > "$DIR/db.out.gz"

# next dump globals (roles and tablespaces) only
$DUMPALL --globals-only | gzip -9 > "$DIR/globals.gz"

# now loop through each individual database and backup the
# schema and data separately
for database in "${DBS[@]}" ; do
    ALL="$DIR/$database.all.backup" 
 #dump all
    $PGDUMP -Fc "$database" > "$ALL"
done

# delete backup files older than 3 days
echo deleting old backup files:
find "$BASE_DIR/" -mindepth 1 -type d -mtime +3 -print0 |
    xargs -0r rm -rfv

cron job :

0 * * * * ./home/postgres/post_back.sh

ls command for backup dir :

/media/attachment/backups/postgres # ls
2016-09-13-14:00:01

Thank you.

One thing that irritates me is the dot at the beginning of the path in your cronjob. Is that a mistake? Perhaps the cron job fails before it can write any data. cron normally sends an e-mail with any output the script produces. Look for e-mails from cron and maybe ad some debugging output to your script so you can figure out what is wrong. — Laurenz Albe
– Laurenz Albe, Commented Sep 14, 2016 at 7:34
@LaurenzAlbe : How do you want me to call the script, when I call sh script name, it throws me some bracket errors, which makes no sense. So I had to add ./path/to/script. About emails. I have multiple scripts running, so I just now cleared all emails, I have to wait 1 hour for the mail to be generated. Can you tell me what I can add to script? Thank you. — We are Borg
– We are Borg, Commented Sep 14, 2016 at 7:37
Apart from the pathname(s), I would add some locking mechanism to avoid cron overrun. — wildplasser
– wildplasser, Commented Sep 14, 2016 at 7:39
If the path starts with . it is a relative path (relative to your current directory). In a cron job you want an absolute path (that starts with a /). About adding diagnostic output: Maybe things like echo "global dump completed" or echo "dumping database $database". — Laurenz Albe
– Laurenz Albe, Commented Sep 14, 2016 at 7:42
@LaurenzAlbe : Okay, I will change it to 0 * * * * /home/postgres/post_back.sh . Add echoes and get back to you in 1 hour. Thank you. — We are Borg
– We are Borg, Commented Sep 14, 2016 at 7:45

Laurenz Albe · Accepted Answer · 2016-09-14 12:18:00Z

2

The problem is the leading . in the path in the crontab entry.

You are advised to use absolute paths in crontab.

answered Sep 14, 2016 at 12:18

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL : Backup script creating only once backup instead of hourly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related