Automated schema migration for PostgreSQL

pgmigrate.sh

#!/bin/bash
#
# Script: pgmigrate.sh
# Author: Mathieu D. (MatToufoutu)
# Description: Automated schema migration for PostgreSQL
# Usage: ./pgmigrate.sh DATABASE MIGRATIONS_FOLDER
#
# Details:
# 	Just store the .sql migrations scripts in a folder, file
# 	names must be incremental numbers (1.sql, 2.sql, ...).
# 	The script stores current version in a ".version" file
# 	and logs PostgreSQL output to a ".migrations.log" file,
# 	both stored in the same folder as the scripts.
#
# NOTE: This script requires 'sudo' to run.
#

VERSION_FILE='.version'
CURRENT_VERSION="`cat $VERSION_FILE 2>/dev/null || echo 0`"
USAGE="Usage: $0 DATABASE MIGRATIONS_FOLDER"
ERR_FILE_PATH="/tmp/.migration_error"
TMP_LOGFILE_PATH="/tmp/.migrations.log"
SU_PG="sudo su postgres -c"

function cleanup {
	rm -f /tmp/*.sql
	if [ -e $TMP_LOGFILE_PATH ]; then
		cp $TMP_LOGFILE_PATH $LOG_FILE_PATH
		$SU_PG "rm -f $TMP_LOGFILE_PATH"
	fi
	$SU_PG "rm -f $ERR_FILE_PATH"
}

# Check 'sudo' availability
if [ "`which sudo`" = "" ]; then
	echo "Can not run, 'sudo' is required to switch to 'postgres' user and couldn't be found!"
	exit 2
fi

# Get arguments
if [ $# -ne 2 ]; then
	echo $USAGE
	exit 1
fi
DATABASE="$1"
MIGRATIONS_FOLDER="`readlink -f $2`"
VERSION_FILE_PATH="$MIGRATIONS_FOLDER/$VERSION_FILE"
LOG_FILE_PATH="$MIGRATIONS_FOLDER/.migrations.log"

for FNAME in `ls $MIGRATIONS_FOLDER | sort -n`; do
	if [ "$FNAME" = `basename $0` ]; then
		continue
	fi
	SCRIPT_VERSION="${FNAME:: -4}"
	if [ "$SCRIPT_VERSION" -gt "$CURRENT_VERSION" ]; then
		SCRIPT_PATH="$MIGRATIONS_FOLDER/$FNAME"

		# Prevent permissions problems
		TMP_PATH="/tmp/$FNAME"
		cp $SCRIPT_PATH $TMP_PATH
		
		# Load the .sql file
		echo "Applying $SCRIPT_PATH..."
		PSQL="cd && psql -1 -v ON_ERROR_STOP=1 -d $DATABASE -a -f $TMP_PATH &>>$TMP_LOGFILE_PATH; echo \$? >$ERR_FILE_PATH"
		$SU_PG "$PSQL"
		
		# Handle potential PostgreSQL errors
		RET_CODE=`cat $ERR_FILE_PATH`
		if [ "$RET_CODE" -ne 0 ]; then
			echo "An error occured, check $LOG_FILE_PATH for more details."
			cleanup
			exit $RET_CODE
		fi

		# Update current migration version
		CURRENT_VERSION=$SCRIPT_VERSION
		echo $CURRENT_VERSION > $VERSION_FILE
	fi
done

cleanup