Backup Postgres DB and Rotate using Logrotate – CentOS

Posted on by Jordansphere

Here is a rudimentary script to backup a specific Postgres DB. Logrotate then takes care of the log rotation. As you'll see below we will keep 7 days worth of compressed files.

  


#! /bin/bash

# This script is called from logrotate /etc/logrotate.d/my-dumps
# It backs up myDB every evening and logrotate takes care of compression and removal

# Notes
# Ensure DUMPDIR is writable from postgres user
# Place script in /tmp
# Ensure this script is executable by postgres user

# Shortcut
DUMPDB='/usr/bin/pg_dump'

# DB DUMP DIRECTORY
DUMPDIR='/DBBACKUPS'

# STAMP DATE
DSTAMP=$(date "+%d-%m-%Y")

# Execute DUMP
$DUMPDB -U postgres mydb > $DUMPDIR/mydb-$DSTAMP.bak

 

Now create the logrotate config file 

vi /etc/logrotate.d/my-dumps
/DBBACKUPS/*.bak {
   daily
   rotate 7
   nocreate
   compress
   sharedscripts
   postrotate
       /tmp/backup-mydb.sh || true
   endscript
}

 

Check the config by running the following command 

/usr/sbin/logrotate -v /etc/logrotate.d/my-dumps

 

Logrotate should run every evening. You can check this by looking in /etc/anacron. Find the START_HOURS_RANGE

# See anacron(8) and anacrontab(5) for details.

SHELL=/bin/sh
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
# the maximal random delay added to the base delay of the jobs
RANDOM_DELAY=45
# the jobs will be started during the following hours only
START_HOURS_RANGE=3-22

#period in days   delay in minutes   job-identifier   command
1       5       cron.daily              nice run-parts /etc/cron.daily
7       25      cron.weekly             nice run-parts /etc/cron.weekly
@monthly 45     cron.monthly            nice run-parts /etc/cron.monthly

 

As you'll see the start range is 3am so the DB and logrotate should happen around this time.