123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164 |
- #!/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 $?
|