Postgres Backup Script BASH

How It Works

What it does

At Splice, we run Davinci Resolve to do all of our coloring and finishing. Like many other applications, Resolve uses a database to keep track of every aspect of your project. Media applications really are not file-based anymore. They are data driven. We needed a script that would back up databases every hour as we worked. After all, a lost database could ruin an entire project. I wrote this script in BASH, it only took a couple hours to work through all the details.

You can see the actual code I wrote in the next tab. You can also see the email output from the LaunchD job when you run the script.

The code

#!/bin/bash

#here we will attempt to login to the database abd get a list of db's to dump:

DATABASES=`/Library/PostgreSQL/8.4/bin/psql --user=postgres -w --no-align --tuples-only --command="SELECT datname from pg_database WHERE NOT datistemplate"`

EMAILBODYSTATUS=""
EXITCODE=0

#ECHO $DATABASES

for DATABASE in $DATABASES; do

	EMAILBODYSTATUS+="`date +%H:%M:%S`: Attempting to dump database: $DATABASE
"
	/Library/PostgreSQL/8.4/bin/pg_dump -h localhost --username postgres $DATABASE --blobs --file /Volumes/ResolveServer_v12_Dumps/Resolve.$DATABASE.backup.`date +%Y%m%d_%H%M%S` --format=custom --verbose --no-password

	if [ $? == 0 ]
	then

		EMAILBODYSTATUS+="`date +%H:%M:%S`: Successfully dumped database: $DATABASE

"
	else
		
		EMAILBODYSTATUS+="WARNING!!!!! WARNING!!!! $DATABASE dump encountered an ERROR at `date +%H:%M:%S`!!! \n \n

"
		echo "$DATABASE dump encountered an error at `date +%H:%M:%S`!!!" >&2 

		EXITCODE=1
	fi	
done	

FROM='resolve@splice.tv'
TO='resolve@splice.tv'
SUBJECT='Resolve Server Backup Status'

BODY=$EMAILBODYSTATUS

printf "From: <%s>\nTo: <%s>\nSubject: %s\n\n%s" "$FROM" "$TO" "$SUBJECT" "$BODY" | sendmail -f "$FROM" -t "resolve@splice.tv"

find /Volumes/ResolveServer_v12_Dumps/* -mtime +1 -exec rm {} \;

if [ $EXITCODE -eq 0 ]
then
  echo "Successfully created database backups on `date +%Y%m%d_%H%M%S`"
  exit 0
else
  echo "Could not create database backups on `date +%Y%m%d_%H%M%S`" >&2
  exit 1
fi

The result

15:00:06: Attempting to dump database: postgres
15:00:07: Successfully dumped database: postgres

15:00:07: Attempting to dump database: vftscons
15:04:40: Successfully dumped database: vftscons

15:04:40: Attempting to dump database: resolveservertest
15:05:03: Successfully dumped database: resolveservertest

15:05:03: Attempting to dump database: betatestserverv12
15:07:21: Successfully dumped database: betatestserverv12

15:07:21: Attempting to dump database: committeefilms2015
15:12:07: Successfully dumped database: committeefilms2015

15:12:07: Attempting to dump database: dv12sever
15:18:44: Successfully dumped database: dv12sever

15:18:44: Attempting to dump database: cf2015_iai200
15:25:29: Successfully dumped database: cf2015_iai200

15:25:29: Attempting to dump database: bv12server
15:26:14: Successfully dumped database: bv12server

Code Samples

Nagios Script

A custom Nagio Monitoring plugin for OS X machines.

Python Backup Script

A script to back up web domains from a Linux VPS.

Resolve Database

Script to back up multiple databases for a Resolve Server running Postgres.

Producer Portal

This is a PHP web application that is used by producers to start a job.

Editor's WTF?!

My effort to create a desktop app written in Python. This is a simple video file transcoder.