We are Borg We are Borg - 2 months ago 15
Linux Question

PostgreSQL : Backup script creating only once backup instead of hourly

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.

Answer

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

You are advised to use absolute paths in crontab.