postgresql-backup/backupdb.sh

#!/usr/bin/env bash
(
	flock -n 9 || { logger -t PostgreSQL databases backup is already running!; exit 1; }
	
	print_elapsed_time() {
		time_elapsed=$(( $2-$1 ))
		milliseconds=$(( $time_elapsed%1000 ))
		seconds=$(( ($time_elapsed/1000)%60 ))
		minutes=$(( ($time_elapsed/1000/60)%60 ))
		hours=$(( ($time_elapsed/1000/60/60)%24 ))

		(( $hours > 0 )) && printf '%dh' $hours
		(( $minutes > 0 )) && printf '%dm' $minutes
		(( $seconds > 0 )) && printf '%ds' $seconds
		printf '%dms\n' $milliseconds
	}
	
	# ... commands executed under lock ...
	
	# Milliseconds from epoch
	backup_start_time=$(date +%s%3N)
	
	global_log_file="/tmp/backupdb-global.log"
	echo '###################################################################################################' | tee $global_log_file
	echo '###########################   PostgreSQL databases backup has started!   ##########################' | tee -a $global_log_file
	echo "###########################           $(date '+%Y-%m-%d %H:%M:%S')                 #########################" | tee -a $global_log_file
	echo '###################################################################################################' | tee -a $global_log_file

	# Connect to remote storage and mount the DIR using sshfs, webdav, rclone, or samba
	echo "$(date '+%Y-%m-%d %H:%M:%S') - mount remote storage..." | tee -a $global_log_file
	# sshfs -o port=22 remote-user@remote-server:/remote-dir/ /mnt/backups/

	count=$(ls /mnt/backups/ | wc -l)

	if [[ $count > 0 ]]; then
		echo "$(date '+%Y-%m-%d %H:%M:%S') - remote storage has been mounted successfully..." | tee -a $global_log_file
	else
		echo "$(date '+%Y-%m-%d %H:%M:%S') - error mounting remote storage, backup process can't continue!" | tee -a $global_log_file
		exit 2
	fi

	# Create temporary DIR
	tmp_dir=$(mktemp -d)
	echo "$(date '+%Y-%m-%d %H:%M:%S') - $tmp_dir dir has created!" | tee -a $global_log_file

	last_backup_file=$tmp_dir/last-backup.sh
	echo "#!/usr/bin/env bash" > $last_backup_file
	echo "declare -A backups" >> $last_backup_file
	echo "$(date '+%Y-%m-%d %H:%M:%S') - $last_backup_file file has been created!" | tee -a $global_log_file

	# Retrieve all databases from PostgreSQL
	excluded_db="db.datname NOT LIKE E'\'template%\''"
	excluded_schema="nspname NOT LIKE E'\'pg_toast%\'' AND nspname NOT LIKE E'\'pg_temp%\'' AND nspname NOT IN (E'\'pg_catalog\'', E'\'information_schema\'')"

	echo "$(date '+%Y-%m-%d %H:%M:%S') - retrieve all databases" | tee -a $global_log_file
	databases=$(su - postgres -c "psql -Atc 'SELECT db.datname FROM pg_database db INNER JOIN pg_authid a ON a.oid = db.datdba WHERE $excluded_db ORDER BY db.datname'")
	#echo "|-- $databases" | cut -d'|' -f 1 | tee -a $global_log_file
	#i=0
	for db in $databases; do
		#(($i >= 3)) && continue
		#((i++))
		# Milliseconds from epoch
		#dbname=$(echo $db | cut -d'|' -f 1)

		start_time=$(date +%s%3N)
		dbname=$db
		
		log_file="$tmp_dir/${dbname}.log"
		echo "$(date '+%Y-%m-%d %H:%M:%S') - ===============================================================================" | tee -a $global_log_file $log_file
		echo "$(date '+%Y-%m-%d %H:%M:%S') - backup for $dbname database has started!"   					   | tee -a $global_log_file $log_file
		echo "$(date '+%Y-%m-%d %H:%M:%S') - ===============================================================================" | tee -a $global_log_file $log_file

		# For later uses
		#username=$(echo $db | cut -d'|' -f 2)
    		
		local_dir="/srv/backups/postgres/$dbname"
		remote_dir="/mnt/backups/postgres/$dbname"
		backup_dir_name=$(date '+%Y%m%d-%H%M')
		backup_dir="$remote_dir/$backup_dir_name"
		temp_file="$tmp_dir/${dbname}.gz"
		
		# Make remote and local dirs if not exits
		mkdir -p "$local_dir" "$backup_dir"
	
		# Dumping the sql and gzip
		echo "$(date '+%Y-%m-%d %H:%M:%S') - * dump sql file and compress with gzip..." | tee -a $global_log_file $log_file
		op_start_time=$(date +%s%3N)
		su - postgres -c "pg_dump -U postgres --clean --if-exists $dbname" | gzip > $temp_file
		op_end_time=$(date +%s%3N)
		echo "$(date '+%Y-%m-%d %H:%M:%S') - * dump completed after" $(print_elapsed_time $op_start_time $op_end_time) | tee -a $global_log_file $log_file 
	
		# Track last backup, important for restore
		echo "$(date '+%Y-%m-%d %H:%M:%S') - * track backup dir: $backup_dir" | tee -a $global_log_file $log_file
		echo "backups['$dbname']=${backup_dir_name}" >> $last_backup_file 
		
		# Copy backup to local dir	
		cp -vf $temp_file $local_dir | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - * copied: {}" | tee -a $global_log_file $log_file
		
		# Copy backup to remote dir	
		cp -vf $temp_file $backup_dir | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - * copied: {}" | tee -a $global_log_file $log_file

		# Remove backup temporary file
		rm -vf $temp_file | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - * {}" | tee -a $global_log_file $log_file

		# Retrieve all shemas for current database
		schemas=$(su - postgres -c "psql -d $dbname -Atc 'SELECT nspname FROM pg_catalog.pg_namespace WHERE $excluded_schema ORDER BY nspname'")

		for schema in $schemas; do
			schema_start_time=$(date +%s%3N)

			echo "$(date '+%Y-%m-%d %H:%M:%S') - * :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::" | tee -a $global_log_file $log_file
			echo "$(date '+%Y-%m-%d %H:%M:%S') - * backup for $dbname.$schema schema has started!" 								    | tee -a $global_log_file $log_file
			echo "$(date '+%Y-%m-%d %H:%M:%S') - * :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::" | tee -a $global_log_file $log_file

			temp_file="$tmp_dir/${dbname}-${schema}.gz"
			
			# Dumping the sql and gzip
			echo "$(date '+%Y-%m-%d %H:%M:%S') - * + dump sql file and compress with gzip..." | tee -a $global_log_file $log_file
			op_start_time=$(date +%s%3N)
			su - postgres -c "pg_dump -U postgres --clean --if-exists -n $schema $dbname" | gzip > $temp_file
			op_end_time=$(date +%s%3N)
			echo "$(date '+%Y-%m-%d %H:%M:%S') - * + dump completed after" $(print_elapsed_time $op_start_time $op_end_time) | tee -a $global_log_file $log_file

			# Copy backup to local dir	
			cp -vf $temp_file $local_dir | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - * + copied: {}" | tee -a $global_log_file $log_file
			
			# Copy backup to remote dir	
			cp -vf $temp_file $backup_dir | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - * + copied: {}" | tee -a $global_log_file $log_file

			# Remove backup temporary file
			rm -vf $temp_file | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - * + {}" | tee -a $global_log_file $log_file

			schema_end_time=$(date +%s%3N)
			echo "$(date '+%Y-%m-%d %H:%M:%S') - * + backup for $dbname.$schema schema has completed after" $(print_elapsed_time $schema_start_time $schema_end_time) | tee -a $global_log_file $log_file
		done

		end_time=$(date +%s%3N)
		echo "$(date '+%Y-%m-%d %H:%M:%S') - * backup for $dbname database has completed after" $(print_elapsed_time $start_time $end_time) | tee -a $global_log_file $log_file
		
		# Copy log file	
		cp -vf $log_file $local_dir | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - * copied {}" | tee -a $global_log_file
		cp -vf $log_file $backup_dir | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - * copied {}" | tee -a $global_log_file
	done

	cp -fv $last_backup_file /srv/backups/postgres | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - copied {}" | tee -a $global_log_file
	cp -fv $last_backup_file /mnt/backups/postgres | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - copied {}" | tee -a $global_log_file 
	
	# Remove backup temporary file
	rm -rfv $tmp_dir | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - {}" | tee -a $global_log_file

	# Remove old backups
	echo "$(date '+%Y-%m-%d %H:%M:%S') - Remove old backups (+60 days)" | tee -a $global_log_file
	find /mnt/backups/postgres -maxdepth 2 -mtime +60 -print0 | xargs -0 rm -rfv | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') - !! {}" | tee -a $global_log_file

	backup_end_time=$(date +%s%3N)
	echo "$(date '+%Y-%m-%d %H:%M:%S') - PostgreSQL backup has completed after" $(print_elapsed_time $backup_start_time $backup_end_time) | tee -a $global_log_file

	cp -fv $global_log_file /srv/backups/postgres/global.log | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') => copied {}"
	cp -fv $global_log_file /mnt/backups/postgres/global.log | xargs -I{} echo "$(date '+%Y-%m-%d %H:%M:%S') => copied {}"

	echo "$(date '+%Y-%m-%d %H:%M:%S') => unmount remote storage..."
	#umount -lq /mnt/backups	
) 9> /var/tmp/backupdb.lock
exit $?