#!/bin/bash
# FN: pgsnapshot.sh
# AB: creates and mounts snapshot of $PGDATA; rsyncs from bkHost;
#		unmounts and removes snapshot; reinitializes database on backup server
# DT: 2003-03-05
# AU: S. Murthy Kambhampaty
#
DISCLAIMER="
This document is provided without restriction to aid discussion about filesystem
	online backup and recovery (BAR) of the postgresql database cluster.
	Any other use is very likely to cause all sorts of damage, and is
	hazardous."
# PREREQUISITES:
#	postgresql (version 7.2.4 and 7.3.2 have been found to work)
#	rsync (2.5.6) with suitable server configuration on the backup server
#	LVM (1.0.3, included in latest SGI XFS enhanced kernels, and 1.0.7 have been found to work)
#	ssh with certificate based authentication of postgres user logging in from production server
#		to backup server

echo $DISCLAIMER
exit 9999


PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
# What is this script called and where is it running:
ThisCMD=$(echo -e "$(basename $0)" | awk 'BEGIN{FS="."}{print $1}')
pgSRV=$(echo -e "$(hostname)" | awk 'BEGIN{FS="."}{print $1}')


# The postgresql superuser
# (if you want to mail error logs somewhere else, either
# set your mail server to forward pgSuper's mail, or add a pgMonitor
# and change the two sendmail command lines below:
pgSuper=postgres
BackupSRV=bkHost
DataFS=/home/db
pgData=/home/db/pgsql/pgdata


RsyncDM="mirrors_$pgSRV" # What is the rsync module on the backup server called
SnapNM=pgsnapshot
XLSnapNM=pgxlsnapshot
#
# As long as you have installed postgresql in
# "/usr/bin/pgsql-M.m/, where M.m=$(echo -n $(cat $PGDATA/PG_VERSION)),
# on both the production server and the backup server,
# you shouldn;t have to edit beyond this point
#


# If $pgData is a symlink, redefine pgData
[[ -L "$pgData" ]] && pgData=$(ls -l "$pgData" | awk '{FS=" "}{print $NF}')
# Which version of postgresql?
pgMinor=$(echo -n "$(cat "$pgData/PG_VERSION")")

# A function to execute commands on the backup server:
RMTExec () {
	ssh -i "/home/$pgSuper/.ssh/id_rsa" $pgSuper@$BackupSRV "$1"
}


# A function to do the rsync
# 	/etc/rsync.d on the backup server must be configured
#	A module named mirrors_$pgSRV must point to a directory
#	on the backup server. Within that folder a subdirectory
# 	named pgdata must exist, and will contain the backup
#	copy of the database
RsyncLockFile="/home/$pgSuper/$ThisCMD.lock"
# rsync options:
# The -B option to increase block size on large files:
# 1. Reduces the chance of changes being missed
# 2. Uses more bandwidth
# If you are really paranoid about missing changes, or if most
# tables have very little common data from backup to backup, add the
# -W option to rsync whole files, rather than changes only.
# NOTE: give the -n option to rsync until sure that it
#	is configured properly. rsync is not forgiviging.
RsyncCMD () {
	rsync -avW -B $((1024 *1024)) --stats \
	 --delete --force --exclude='postmaster.pid' \
	$1/ \
	$BackupSRV::$RsyncDM/pgdata/$2/ # If you don't give a second arguement, $2 is null
}
# When the postmaster is started on the backup server,
#	the data directory is:
pgDataBK=$(RMTExec "cat /etc/rsyncd.conf" | sed -n /$RsyncDM/,/^$/p | grep path | awk 'BEGIN{FS=" = "}{print $2}')"/pgdata"


# A function to print a formatted timestamp in the log
DateCMD () {
	# Two trailing spaces are intentional
	echo -n "$(date +%Y-%m-%d) $(date +%X)  "
	return 0
}


# A function to mail the error log and bailout; called on program error
ExitOnError () {
	# This function takes two arguments an "exit code" and and "error message"
	# in order
	rm -f "$DoSnapLockFile" &>/dev/null
	echo $'\n\n'"$(DateCMD) $2"$'\n\t'\
		"Notified superuser"\
		| tee -a "$STASFile" >&2
	exec  >&6 6>&-
	exec 2>&7 7>&-
	cat <(echo "Subject: ATTN: $2"\
			$'\n'"X-Priority: 2 (high)"\
			$'\n'"!"\
			$'\n\t'"Review $(echo -n "$LogFile"), the command log"\
			$'\n\t\t'"Error report listed below:" $'\n')\
		"$LogFile.err"\
		| sendmail "$pgSuper"
	exit $1
	return $1 # Superfluous: this function does not return
}


# A function for LVM snapshot creation and mounting
#	There must exist a directory named /mnt/$SnapNM
#	to which the snapshot is mounted
DataLV="$(mount | grep "$DataFS\>[^/]" | awk 'BEGIN{FS=" "}{print $1}')"
DataVG="$(dirname "$DataLV")" # Please keep the XLogLV and DataLV in the same VG
SnapDir="/mnt/$SnapNM"
XlogLV="$(mount | grep "$pgData/pg_xlog\>[^/]" | awk 'BEGIN{FS=" "}{print $1}')"
[[ -n "$XlogLV" ]] && XLSnapMB=512
DoSnap () {
	AvailMB="$(echo -n $(vgdisplay --colon "$DataVG") | awk 'BEGIN{FS=":"}{print $16 *$13/2048}')" # 2048 512-byte blocks per MB
	if [[ -n "$(lvscan | grep "$SnapNM")" || -n "$(lvscan | grep "$XLSnapNM")" ]]; then
		echo $'\n\t'"WARNING: Snapshot named $SnapNM or $XLSnapNM already exists. Terminating ..." >&2
		SnapTest=32 # lvcreate error: "32 snapshot already exists"
	elif ((-XLSnapMB +AvailMB <=0)); then # Variables need not be preceeded by '$' in arithmetic expansion;
			# the above syntax does not work if you test ((-$XLSnapMB +$AvailMB)), so you'll
			# have to put up with the confusion over referencing variables without the preceding
			# $-sign sometimes
		echo $'\n\t'"WARNING: No space left in $DataVG for snapshot creation. Terminating ..." >&2
		SnapTest=19 # lvcreate error: "19 not enoungh space available to create logical volume"
	else
		# Use all the free space available in $DataVG for the snapshot!
		[[ -n "$XlogLV" ]] && xfs_freeze -f "$pgData/pg_xlog"
		xfs_freeze -f "$DataFS"
		[[ -n "$XlogLV" ]] && { lvcreate -s -L "$XLSnapMB"M -n "$XLSnapNM" "$XlogLV"; XLSnapTest=$?; }
		lvcreate -s -L "$((-XLSnapMB +AvailMB))"M -n "$SnapNM" "$DataLV"
		SnapTest=$?
		xfs_freeze -u "$DataFS"
		[[ -n "$XlogLV" ]] && xfs_freeze -u "$pgData/pg_xlog"
	fi
	# If snapshot creation failed bail; else mount the snapshot volume
	if ((+XLSnapTest +SnapTest >0)); then
		ExitOnError 991$SnapTextXL$SnapTest "pgBackup; Snapshot creation failed"
	else
		echo $'\n\t'"$(DateCMD) Successfully created snapshot"
		lvscan | grep snap # Report the snapshot volumes
		[[ -n "$XlogLV" ]] && { mount -t xfs "$DataVG/$XLSnapNM" "/mnt/$XLSnapNM" -o nouuid,ro; XLMountTest=$?; }
		LogDev="$(cat "/etc/mtab" | grep "$DataFS\>[^/]" | awk 'BEGIN{FS="logdev="}{print $2}' | awk 'BEGIN{FS=","}{print $1}')"
		if [[ -n "$LogDev" ]]; then
				mount -t xfs "$DataVG/$SnapNM" "$SnapDir" -o nouuid,ro,logdev="$LogDev"
				MountTest=$?
		else
				mount -t xfs "$DataVG/$SnapNM" "$SnapDir" -o nouuid,ro
				MountTest=$?
		fi
		mount | grep snap # Report the mounts
	fi
	# If snapshot mount failed, then bail; else return
	if ((+$MountTestXL +$MountTest >0)); then
		lvremove -f "$DataVG/$SnapNM"
		[[ -n "$XlogLV" ]] && lvremove -f "$DataVG/$XLSnapNM"
		ExitOnError 992$MountTest "pgBackup; Snapshot mount failed"
	fi
	return 0 # Only returns on success
}


# Setup logging, and get going:
STASFile="$pgData/pgSnapBack.status"
LogFile="/home/$pgSuper/$ThisCMD-$(date +%Y%m%d-%H%M)"
exec 6>&1; exec  > "$LogFile"
exec 7>&2; exec 2> "$LogFile.err"
echo "$(DateCMD) Backup initiated"\
	| tee "$STASFile"


# First vacuum all databases
# echo $'\n\n'"$(DateCMD) Vacuuming databases on $(hostname)"
# # Mark vacuum start and end in the log to ease log navigation
# /usr/local/pgsql/bin/psql -U gouser -d template1 -c \
# 	"select '+++++ Nightly Vacuum About To Commence +++++'" >/dev/null
# /usr/local/pgsql/bin/vacuumdb -U postgres -q -a -z && \
# 	/usr/local/pgsql/bin/psql -U gouser -d template1 -c \
# 		"select '+++++ Nightly Vacuum Completed +++++'" >/dev/null && \
# 	echo $'\n\t' "$(DateCMD) Successfully vacuumed databases on $(hostname)"


# Phase 1 rsync
echo $'\n\n'"$(DateCMD) Phase 1 - rsync from pgdata to mirror on $BackupSRV"\
	| tee -a "$STASFile"
while [[ -f "$RsyncLockFile" ]]; do
		echo "$(DateCMD) previous Rsync still running"$'\n'\
		"Waiting for completion" >&2
		sleep 300
done
# Before you start the backup, make sure that no postmaster is running on the backup folder:
# 	(test cribbed from the pg_ctl script)
while [[ -n $(RMTExec "sed -n 1p $pgDataBK/postmaster.pid 2>/dev/null") ]]; do
		echo "$(DateCMD) A postmaster is running at the remote location; waiting 5 minutes ..."
		sleep 300
done
# You don't want to start simultaneous rsyncs, and you don't want to
# start the postmaster while an rsync is running; so put down some
# lock files (there was something on linux-xfs about xfs not recovering
# inodes allocated to empty files, so don't just "touch" a lockfile, put
# something in it).
echo "$(DateCMD) Phase 1 - rsync" > "$RsyncLockFile"
cat "$RsyncLockFile" | RMTExec "cat - > ~/RsyncLockFile"
RsyncCMD "$pgData/" &&
	echo $'\n'"$(DateCMD) Rsync complete"$'\n\n'
rm -f "$RsyncLockFile"


# Now create and mount the snapshot volume
# I'm still seing XFS lockups that go away with xfs_freeze -u $DataFS
# So, put up a monitor for all processes wanting to write to to the $DataFS, and if
# you see a D-state on those processes, issue xfs_freeze -u $DataFS
DoSnapLockFile="/usr/var/lock/$(basename "$LogFile").PID"
ParentPID=$$
echo $ParentPID > "$DoSnapLockFile" # Initailize "$DoSnapLockFile"
(
while [[ -f "$DoSnapLockFile" ]]; do
	sleep 60.$ParentPID # snaphsots should not take a full minute, but keep an eye on this one
	[[ -f "$DoSnapLockFile" ]] && # In a successful run, it could be gone by the time we wake this loop
	{
	if [[ "$(ps -eo pid,ppid,session,stat,wchan | grep "D    xfs_check_frozen" | grep $ParentPID )" != "" ]]; then
		echo $'\n'"Snapshot creation seems to have led to system lockup." \
			$'\n\t'"Process list from backup session:" >&2
		ps -eo pid,ppid,session,flags,pagein,stackp,stat,args,wchan | grep $ParentPID | sed -e "/grep/d" >&2
		echo $'\n'"Issuing xfs_freeze -u $DataFS" \
			$'\n\t'"Consider this backup unsuccessful." >&2
		xfs_freeze -u "$DataFS"
		[[ -n "$XlogLV" ]] && xfs_freeze -u "$pgData/pg_xlog"
		rm -f "$DoSnapLockFile"
		break
	fi
	}
done
) &

echo $'\n\n'"$(DateCMD) Snapshot creation and mounting"\
	| tee -a "$STASFile"
DoSnap


# Phase 2 rsync
echo $'\n\n'"$(DateCMD) Phase 2 - rsync from snapshot to mirror on ""$BackupSRV"
while [[ -f "$RsyncLockFile" ]]; do
		echo "$(DateCMD) previous Rsync still running"$'\n'\
		"Waiting for completion" >&2
		sleep 300
done
echo "$(DateCMD) Phase 2 rsync" > "$RsyncLockFile"
cat "$RsyncLockFile" | RMTExec "cat - > ~/RsyncLockFile"
RsyncCMD "--exclude='pg_xlog/' $SnapDir${pgData#$DataFS}" / &&
	{ [[ -n "$XlogLV" ]] && RsyncCMD "/mnt/$XLSnapNM/" "pg_xlog"; } &&
	echo $'\n\t'"$(DateCMD) Phase 2 rsync complete"$'\n'\
	| tee -a "$STASFile"
rm -f "$RsyncLockFile"
RMTExec "rm -f ~/RsyncLockFile"


# Now remove the snapshot and monitoring stuff
umount "$DataVG/$SnapNM"
umount "/mnt/$XLSnapNM"
lvremove -f "$DataVG/$SnapNM"
[[ -n "$XlogLV" ]] && lvremove -f "$DataVG/$XLSnapNM"
# 	should no longer be an issue
rm -f "$DoSnapLockFile"
kill -INT $(ps ax | grep sleep | grep $ParentPID | awk 'BEGIN{FS=" "}{print $1}') &>/dev/null && \
	echo "$(DateCMD) Removed snapshot of \$PGDATA"$'\n\n'


# "Reinitialize" the snapshot of the database on the backup server
echo $'\n\n'"$(DateCMD) Start postmaster on $BackupSRV, capture output, and shutdown"
RMTExec "/usr/local/pgsql-$pgMinor/bin/postmaster -B4096 -p55432\
	-D $pgDataBK 2>&1" &
sleep 300 # give the postmaster time for recovery
RMTExec "/usr/local/pgsql-$pgMinor/bin/pg_ctl stop -D $pgDataBK -m immediate" &&
	echo $'\n\n'"$(DateCMD) Attempted to reinitialize backup; check for errors above"


# Mop up
if [[ ! -s "$LogFile.err" ]]; then
	echo "Completed, no errors" > "$LogFile.err"
	rm -f "$LogFile.err"
	echo $'\n\n'"$(DateCMD) Backup completed."\
		| tee -a "$STASFile"
	echo "$(DateCMD) Verify postmaster-messages, listed above, for success."
	exec  >&6 6>&-
	exec 2>&7 7>&-
	exit 0
else
	ExitOnError 999 "pgBackup; Backup encountered errors"
fi
true # Superflous