#!/bin/ksh93

typeset -r VERSION='1.0' FPROG=${.sh.file} PROG=${FPROG##*/} SDIR=${FPROG%/*}

typeset -r -A DEFAULTS=(
	[script_user]=
	[host]="${PGHOST}" [port]="${PGPORT}" [db_user]='postgres'
	[schemas]=
	[db_dir]='/var/data/postgres/backup'
	[keep]=0
	[days]=7 [weeks]=5 [weekday]=1	# monday
	[jobs]=-1
	[format]='directory'
	[xid]=0
	[zlevel]=6
)

function showUsage {
	[[ -n $1 ]] && X='-?' ||  X='--man'
	getopts -a ${PROG} "${ print ${USAGE} ; }" OPT $X
}

function hasChanged {
	typeset -A NEW OLD
	typeset LINE DIR="$1" DBS= C ALL=
	[[ -n $2 ]] && MKNEW=1 || MKNEW=0
	ARGS=( -d postgres -U "${OPTS[db_user]}" --no-align --tuples-only )
	[[ -n ${OPTS[host]} ]] && ARGS+=( -h "${OPTS[host]}" )
	[[ -n ${OPTS[port]} ]] && ARGS+=( -p ${OPTS[port]} )
	ARGS+=( -F '@' -c "SELECT datname,xact_commit FROM pg_stat_database;" )
	psql "${ARGS[@]}" | while read LINE ; do
		ALL+="${LINE}\n"
		C="${LINE#*@}"
		DB="${.sh.match%@}"
		[[ -n $C ]] && NEW["${DB}"]=$C
		DBS+="${DB}\n"
	done
	if [[ ! -f ${DIR}/xact.lst ]]; then
		if (( MKNEW )); then
			[[ -n ${DRY} ]] && ${DRY} print -n "'${ALL}' >'${DIR}/xact.lst'" ||\
				print -n "${ALL}" >"${DIR}/xact.lst"
		fi
		return 1
	fi

	while read LINE ; do
		C="${LINE#*@}"
		DB="${.sh.match%@}"
		[[ -n $C ]] && OLD["${DB}"]=$C
		DBS+="${DB}\n"
	done < "${DIR}/xact.lst"

	integer RES
	print -n -- "${DBS}" |sort -u | while read DB ; do
		[[ ${OLD["${DB}"]} == ${NEW["${DB}"]} ]] || (( RES++ ))
	done
	return ${RES}
}

function doBackup {
	typeset DEST_DIR="$1" ARGS DBS= X T FMT= WHAT= SFX=
	typeset -n DONE=$2
	integer SO=$3 C=0

	T='SELECT datname FROM pg_database WHERE '
	if (( SO )); then
		WHAT='_SCHEMA'
		FMT='plain'
		for X in ${OPTS[schemas]//,/ } ; do
			[[ -n $X ]] && DBS+=" OR datname ~ '$X'"
		done
		[[ -z ${DBS} ]] && return 0		# nothing to do
		T+=" ${DBS:4} "
	else
		WHAT='_FULL'
		FMT=${OPTS[format]}
		T+='NOT DATISTEMPLATE AND DATALLOWCONN'
		for X in ${OPTS[schemas]//,/ }; do
			T+=" AND datname !~ '${X}'"
		done
	fi
	T+=' ORDER BY datname;'
 
	print "\n\n${P}Performing ${WHAT:1} dumps\n${P}-----------------------"
	ARGS=( -d postgres -U "${OPTS[db_user]}" --no-align --tuples-only )
	[[ -n ${OPTS[host]} ]] && ARGS+=( -h "${OPTS[host]}" )
	[[ -n ${OPTS[port]} ]] && ARGS+=( -p ${OPTS[port]} )
	ARGS+=( -c "$T" )
 
	DBS=${ psql "${ARGS[@]}" ; }

	if [[ -z ${DBS} ]]; then
		print "${P}No matching DBs found."
		return 0
	fi

	# don't rise compression to -Z9 for non-schema-only dumps: it increases
	# the dump time wrt. -Z6 by factor 1.5..3 dramtically (xz -z6 by x10..20)
	# and just saves ~4-5% HDD space (xz -z6 ~50%).
	ARGS=( -U "${OPTS[db_user]}" )		# different cmd, so reset
	[[ -n ${OPTS[host]} ]] && ARGS+=( -h "${OPTS[host]}" )
	[[ -n ${OPTS[port]} ]] && ARGS+=( -p ${OPTS[port]} )
	(( SO )) && ARGS+=( -s -Z 9 ) && SFX='_SCHEMA.sql.gz'
	if [[ ${FMT} == 'directory' ]]; then
		if (( OPTS[jobs] != 1 )); then
			if (( OPTS[jobs] < 1 )); then
				if [[ ${ uname -s ; } == 'SunOS' ]]; then
					C=${ psrinfo | fgrep 'on-line' | wc -l ; }
				else
					# assume linux
					C=${ grep ^processor /proc/cpuinfo | wc -l ; }
				fi
				(( C-=2 ))
				(( C < 2 )) && C=2
			else
				C=${OPTS[jobs]}
			fi
			ARGS+=( -j $C )
		fi
		T='-d'
		SFX='.db'
	else
		T=
		[[ ${FMT} == 'plain' ]] && SFX='.sql.gz' || SFX='.pgdmp'
	fi
	(( OPTS[zlevel] != 6 && ! SO )) && ARGS+=( -Z ${OPTS[zlevel]} )
	for X in ${DBS} ; do
		[[ -n ${DRY} ]] && FILE="${DEST_DIR}/tmp_pgdump.XXXXXX" || \
			FILE=${ mktemp ${T} -p "${DEST_DIR}" tmp_pgdump.XXXXXX ; }
		[[ -z ${FILE} ]] && print -u2 "${P}\t'$X' skipped!" && continue
		print "${P}\t'$X' (format=${FMT}) ..."
		${DRY} pg_dump -d "$X" "${ARGS[@]}" --format=${FMT} --file="${FILE}"
		if (( $? )); then
			print -u2 "${P}\tERROR: Failed - '$X' skipped!"
			${DRY} rm -rf "${FILE}"
		else
			${DRY} mv "${FILE}" "${DEST_DIR}/${X}${SFX}"
			[[ -n ${DRY} ]] && rm -rf "${FILE}"
			DONE+=( "$X" )
		fi
	done
}

function cleanup {
	integer KEEP=$2
	typeset X="$1" KEEP="$2" DIR SFX OLD
	DIR="${1%/*}"
	OLD="${.sh.match%-*}"
	SFX="${.sh.match}"

	if (( KEEP )); then
		for X in ~(N)"${DIR}"/dump.[0-9]* ; do
			T=${X:${#DIR}+6}
			[[ -d $X && $T =~ ^[0-9]+$ ]] && (( T >= KEEP )) && \
				${DRY} rm -rf "$X"
		done
		return
	fi

	for X in ~(N)"${DIR}"/[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]${SFX} ; do
		T=${X##*/}
		[[ -d $X && ${T%${SFX}} < ${OLD} ]] && ${DRY} rm -rf "$X"
	done
}

function doMain {
	checkOpts || { print -u2 "${P}Backup failed."; exit 2; }

	integer N=-1
	typeset DIR="${OPTS[db_dir]%%/}" SFX OLD X
	typeset -a DONE

	if (( ${OPTS[keep]} )); then
		OLD="${DIR}/dump.latest"
		if [[ -e  ${OLD} ]]; then
			if [[ -h ${OLD} ]]; then
				SFX=( ${ ls -l "${OLD}" ; } )
				[[ ${SFX[10]}  =~ ^(\./)?dump\.[0-9]+$ ]] && N=${SFX[10]##*.}
			fi
			(( N == -1 || N >= OPTS[keep] )) && N=0 || (( N++ ))
		else
			N=0
		fi
		DIR+="/dump.$N"
		if (( OPTS[xid] )); then
			hasChanged "${OLD}"
			if (( $? == 0 )) ; then
				print "${P}Nothing has changed, skipping dumps."
				return 0
			fi
		fi
	else
		N=${ date '+%d' ; }		# day of month
		OLD=${ date '+%Y-%m-%d' ; }
		if (( N == 1 )); then
			SFX='-monthly'
		else 
			N=${ date '+%u' ; }		# weekday 1-7 (Monday-Sunday)
			if (( N == OPTS[weekday] )); then
				OLD=${ ${GNUDATE} "${OPT[weeks]} weeks ago" '+%Y-%m-%d' ; }
				SFX='-weekly'
			else
				SFX='-daily'
			fi
		fi
		OLD="${DIR}/${OLD}${SFX}"
		DIR+="/${ date '+%Y-%m-%d' ; }${SFX}"
	fi

	cleanup "${OLD}" ${OPTS[keep]}

	[[ -e ${DIR} ]] && ${DRY} rm -rf "${DIR}"
	if [[ ! -d "${DIR}" ]]; then
		${DRY} mkdir "${DIR}" || return 2
	fi
	doBackup "${DIR}" DONE 1		# schemas
	doBackup "${DIR}" DONE 0		# full
	if [[ -z ${DONE} ]]; then
		${DRY} rmdir "${DIR}" 2>/dev/null	# if empty, remove it
	else
		SFX=
		if (( ${OPTS[keep]} )); then
			${DRY}  rm -f "${OLD}" && ${DRY} ln -sf ${DIR##*/} "${OLD}"
			hasChanged "${DIR}" 1
			if [[ -z ${DRY} ]]; then
				( cd ${DIR} ; ls -1 *.db/* ) >${DIR}/dbls.lst
			fi
		fi
		for X in "${DONE[@]}" ; do
			SFX+="$X\n"
		done
		[[ -z ${DRY} ]] && print -n "${SFX}" >${DIR}/db.lst || \
			${DRY} print -n "'${SFX}' >${DIR}/db.lst"
	fi

	print "\n${P}Database dumps complete!"
}

function readConfig {
	[[ -r $1 ]] || { print -u2 "${P}Config '$1' not readable."; return 1 ; }

	typeset BACKUP_USER= HOSTNAME= USERNAME= BACKUP_DIR= SCHEMA_ONLY_LIST= \
		ENABLE_CUSTOM_BACKUPS= ENABLE_PLAIN_BACKUPS= DAY_OF_WEEK_TO_KEEP= \
		DAYS_TO_KEEP= WEEKS_TO_KEEP= KEEP= X

	. "$1" || { print -u2 "${P}Config '$1' has errors."; return 2 ; }
	[[ -n ${BACKUP_USER} ]] && OPTS[script_user]="${BACKUP_USER}"
	[[ -n ${HOSTNAME} ]] && OPTS[host]="${HOSTNAME}"
	[[ -n ${USERNAME} ]] && OPTS[db_user]="${USERNAME}"
	[[ -n ${BACKUP_DIR} ]] && OPTS[db_dir]="${BACKUP_DIR}"
	[[ -n ${SCHEMA_ONLY_LIST} ]] && OPTS[schemas]="${SCHEMA_ONLY_LIST}"
	X="${ENABLE_PLAIN_BACKUPS}"
	[[ -n $X ]] && [[ $X == 'yes' ]] && OPTS[format]='plain'
	X="${ENABLE_CUSTOM_BACKUPS}"
	[[ -n $X ]] && [[ $X == 'yes' ]] && OPTS[format]='custom'
	[[ -n ${DAY_OF_WEEK_TO_KEEP} ]] && OPTS[weekday]="${DAY_OF_WEEK_TO_KEEP}"
	[[ -n ${DAYS_TO_KEEP} ]] && OPTS[days]="${DAYS_TO_KEEP}"
	[[ -n ${WEEKS_TO_KEEP} ]] && OPTS[weeks]="${WEEKS_TO_KEEP}"
	[[ -n ${KEEP} ]] && OPTS[keep]="${KEEP}"
	return 0
}

function checkOpts {
	integer ERR=0
	typeset X

	if [[ -n ${OPTS[script_user]} && ${ id -un ; } != ${OPTS[script_user]} ]]
	then
		print -u2 "${P}This script is expected to be run as"
			"${OPTS[script_user]}."
		(( ERR++ ))
	fi
	if [[ -z ${OPTS[keep]} ]]; then
		OPTS[keep]=0
		for  X in weekday days weeks ; do
			[[ -z ${OPTS[${X}]} ]] && OPTS[${X}]=${DEFAULTS[${X}]}
		done
		if [[ ! ${OPTS[weekday]} =~ ^[0-9]+$ ]] || (( ${OPTS[weekday]} < 1 )) \
			|| (( ${OPTS[weekday]} > 7 )) 
		then
			print -u2 "${P}Invalid day of the week '${OPTS[weekday]}'" \
				"(number in the range of 1..7 expected)."
			(( ERR++ ))
		fi
		if [[ ! ${OPTS[days]} =~ ^[0-9]+$ ]]; then
			print -u2 "${P}Days to keep ('${OPTS[days]}') is not a number!"
			(( ERR++ ))
		fi
		if [[ ! ${OPTS[weeks]} =~ ^[0-9]+$ ]]; then
			print -u2 "${P}Weeks to keep ('${OPTS[weeks]}') is not a number!"
			(( ERR++ ))
		fi
	elif [[ ! ${OPTS[keep]} =~ ^[0-9]+$ ]]; then
		print -u2 "${P}Backups to keep ('${OPTS[keep]}') is not a number!"
		(( ERR++ ))
	fi
	[[ -z ${OPTS[db_dir]} ]] && OPTS[db_dir]="${DEFAULTS[db_dir]}"
	if [[ ! -d ${OPTS[db_dir]} ]]; then
		${DRY} mkdir -p "${OPTS[db_dir]}" || (( ERR++ ))
	fi
	if [[ -n ${OPTS[format]} ]]; then
		case "${OPTS[format]}" in
			p) OPTS[format]='plain' ;;
			c) OPTS[format]='custom' ;;
			d) OPTS[format]='directory' ;;
			plain|custom|directory) : ;;
			*) print -u2 "${P}Unknown format '${OPTS[format]}'!"; (( ERR++ )) ;;
		esac
	else
		OPTS[format]=${DEFAULTS[format]}
	fi
	for X in host port zlevel db_user schemas ; do
		[[ -z ${OPTS[$X]} ]] && OPTS[$X]="${DEFAULTS[$X]}"
		[[ -z ${OPTS[$X]} ]] && OPTS[$X]="${DEFAULTS[$X]}"
	done
	if [[ -n ${OPTS[port]} && ! ${OPTS[port]} =~ ^[0-9]+$ ]]; then
		print -u2 "${P}Invalid port '${OPTS[port]}' (not a number)!"
		(( ERR++ ))
	elif (( OPTS[port] < 0 || OPTS[port] > 65535 )); then
		print -u2 "${P}Invalid port '${OPTS[port]}' (out of range)!"
		(( ERR++ ))
	fi
	if [[ ! ${OPTS[zlevel]} =~ ^[0-9]$ ]] || \
		(( OPTS[zlevel] < 0 || OPTS[zlevel] > 9 ))
	then
		print -u2 "${P}Invalid compression level '${OPTS[zlevel]}'!"
		(( ERR++ ))
	fi
	if [[ ${ uname -s ; } == 'Linux' ]]; then
		GNUDATE=${ whence date ; }	# assume a GNU system
	else
		GNUDATE=${ whence gdate ; }
	fi
	[[ -z ${GNUDATE} ]] && \
		print -u2 "${P}GNU date utilitity 'gdate' not found!" && (( ERR++ ))
	return ${ERR}
}

USAGE="[-?${VERSION}"' ]
[-copyright?Copyright (c) 2016 Jens Elkner. All rights reserved.]
[-license?CDDL 1.0]
[+NAME?'"${PROG}"' - Dump the contents of a posgresql database.]
[+DESCRIPTION?A simple wrapper around \bpg_dump\b(1), which stores the dumps into certain directories and removes not anymore needed dumps according to the choosen policy.]
[+?It can e.g. be used as a hook to execute when NetBackup starts its daily backup job by using the \b/etc/netbackup/bpstart_notify\b script. However, for huge DBs this may delay the backup itself a lot and therefore running this script for such DBs should rather be scheduled via a normal cronjob.]
[h:help?Print this help and exit.]
[F:functions?Print a list of all functions available.]
[T:trace]:[functionList?A comma separated list of functions of this script to trace (convinience for troubleshooting).] 
[+?]
[H:host]:[hostname?The name or IP address of the machine to backup. If ommitted localhost is assumed and the UNIX socket will be used to connect to the DB.] 
[P:port]:[port?The \aport\a to use to connect to the DB. Ignored for socket based connections.]
[U:user]:[user?Check first whether this script is running as \auser\a. If not, the script exits immediately, i.e. no dump will be made.]
[W:weekday]:[wday?The number of the day of the week, when a "weekly" dump should be made (1..7, 1==monday). The difference to a normal daily dump is the dump folder to use, i.e. "*-weekly" instead of "*-daily". Default: '"${DEFAULTS[weekday]}"'] 
[b:basedir]:[path?The directory to use as parent directory for all dumps. This directory should not contain any data not produced by this script, otherwise the removal of unintended files and directories may occure. Default: '"${DEFAULTS[db_dir]}"']
[c:config]:[cfgfile?Obsolete: read and apply the settings from the given \acfgfile\a immediately, i.e. current settings (defaults or given via CLI options) might be overwritten AND options following this one may in turn overwrite the settings from the given \afile\a, so CLI option order is important!]
[d:days]:[days?Remove all daily dumps older than the given number of \adays\a before doing a new daily dump. Default: '"${DEFAULTS[days]}"']
[f:format]:[fmt?Use the given format \afmt\a for dumps. Supported values are \bdirectory\b, \bcustom\b, or \bplain\b. The last two options automatically disable parallel dumps, the first one enables them. Default: '"${DEFAULTS[format]}"'] 
[k:keep]:[keep?Keep not more than \akeep\a dumps (simple round-robin store). If specified, the options -d ..., -W ... and -w ... are ignored. ]
[n:dry?Just show, what would be done, but do not do it.]
[p:parallel]:[jobs?Does a parallel dump of the database using the given number of \ajobs\a. A value < 1 (the default) implies MAX(2,number of CPU strands - 2). This option also sets the dump format to use to \bdirectory\b, i.e. any previous -f ... options are ignored.]
[s:schemas]:[schemas?A comma separated list of DB names, whose schema should be dumped only, i.e. no table data. Therefore the format used for these dumps is always \bplain\b.]
[w:weeks]:[weeks?Remove all weekly dumps older than the given number of \aweeks\a before starting a new weekly dump. Default: '"${DEFAULTS[weeks]}"']
[u:user]:[dbuser?Connect as \adbuser\a to the DBs. Default: '"${DEFAULTS[db_user]}"']
[x:xid?Check the global transaction ID for each table to determine, whether the content has changed and thus a new dump is needed. Ignored if no -k ... option is given as well.]
[z:zlevel]:[level?The \alevel\a of compression to pass to \bpg_dump\b(1). Allowed values are 0..9, whereby 6 instructs to use the default pg_dump value. Default: 6]
[+NOTES?This script should be compatible to the \bpg_backup_rotated.sh\b script, which can be found in the internet. However, it uses NO default config file and provides an additional, real rotate policy, better suitable for cases, where the machine backup gets made by ancient software like NetBackup, which do not provide "snapshot" functionality or only by using a stupid amount of ressources.]
[+?This scripts removal of old dumps is not based on a directories last modification time (like in \bpg_backup_rotated.sh\b), but on path basenames, only!]
[+?Any dump not made using the "-k ..." option will overwrite any dump made previously on the same day, because the related dump directories are YY-mm-dd organized. Furthermore a monthly dump will be made on the 1st day of each month, whereby all previously monthly dumps get removed before the new one gets started! The difference between a normal daily and a monthly dump is the dump folder name to use, i.e. "*-monthly" instead of "*-daily". All this questionable behavior is provided for backward compatibility, only.]
[+?\bpg_dump\b(1) as well as \bpgsql\b(1) use certain environment variables for setting its defaults. This script does not change or unsets them and thus they may alter the behavior of these utilities.]
[+EXAMPLES]
[+?'"${PROG} -b /var/tmp/pg-backup -k 7 -x"']
'
unset OPTS DRY GNUDATE P ; typeset -A OPTS
integer L
X="${ print ${USAGE} ; }"
while getopts "${X}" OPT ; do
	case ${OPT} in
		h) showUsage ; exit 0 ;;
		T)  if [[ ${OPTARG} == 'ALL' ]]; then
				typeset -ft ${ typeset +f ; }
			else
				typeset -ft ${OPTARG//,/ }
			fi
			;;
		F) typeset +f && exit 0 ;;
		H) OPTS[host]="${OPTARG}" ;;
		P) OPTS[port]="${OPTARG}" ;;
		U) OPTS[script_user]="${OPTARG}" ;;
		W) OPTS[weekday]="${OPTARG}" ;;
		b) OPTS[db_dir]="${OPTARG}" ;;
		c) readConfig "${OPTARG}"||{ print -u2 "${P}Backup failed.";exit 1; } ;;
		d) OPTS[days]="${OPTARG}" ;;
		f) OPTS[format]="${OPTARG}" ;;
		k) OPTS[keep]="${OPTARG}" ;;
		n) DRY='print -r --' ; P='# ' ;;
		p) [[ ${OPTARG} =~ ^[0-9]+$ ]] && OPTS[jobs]=${OPTARG} || OPTS[jobs]=0
			OPTS[format]='directory' ;;
		s) OPTS[schemas]="${OPTARG}" ;;
		u) OPTS[db_user]="${OPTARG}" ;;
		x) OPTS[xid]=1 ;;
		z) OPTS[zlevel]="${OPTARG}" ;;
		*) showUsage 1 ; exit 1 ;;
	esac
done

X=$((OPTIND-1))
shift $X && OPTIND=1
unset X

if [[ ${ id -un ; } == 'root' ]]; then
	print -u2 "\n\nDu bist 'root' - ach geh doch nach Hause!!! Ich mach nix.\n"
	exit 1
fi
doMain "$@"