.::: Script Backup Full All Database Using Record to Table include start, finish & duration backup in PostgreSQL EDB :::.

 

correlation https://teguhth.blogspot.com/2024/02/script-backup-full-all-database-in.html
https://teguhth.blogspot.com/2025/01/script-backup-full-all-database-using.html

1. create database & table
CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    finish TIMESTAMP NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);
 
2. run sh backup_full_daily_with_record_duration.sh
 
-bash-4.2$ sh backup_full_daily_with_record_duration.sh
 

3. check result backup record
psql -d dbatools -c "SELECT * FROM public.backuplogduration order by backuptime desc;;"
psql -d dbatools -Atc "SELECT * FROM public.backuplogduration order by backuptime desc;;"
SELECT *,VERSION() from public.backuplogduration order by backuptime desc;
 

4. if want to delete record
delete from backuplogduration;

5. script

-bash-4.2$ pwd
/var/lib/pgsql/script/backup/record
-bash-4.2$

-bash-4.2$ cat backup_full_daily_with_record_duration.sh

#!/bin/bash

# Variabel dasar
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
dbversion=$(psql -Atc "select version();")
infotgl=$(date)
year=$(date +%Y)
month=$(date +%m)
day=$(date +%d)
hour=$(date +%H)
min=$(date +%M)
sec=$(date +%S)
underscore='_'
backuppath="/data/edb/edbbackup"

backuptime=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");


# Header informasi
echo ".::: Backup Full All database using custom in '$Server' with IP $ip_address :::."
echo "PostgreSQL Version : '$Server' $dbversion"
echo ""

# Backup mulai
echo "Backup begin $infotgl"
dbname=$(psql -Atc "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres');")

for listdb in $dbname; do
  # Buat direktori untuk backup
  mkdir -p "$backuppath/$year$month$day/$listdb"
  path="$backuppath/$year$month$day/$listdb"

startbackup=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_startf=$(date +%s)

  # Backup database
  echo "Backup Full for database '$listdb' in $path start at ${year}${month}${day}${underscore}${hour}${min}${sec}"
  pg_dump "$listdb" | gzip -c > "$path/[FULL]_${listdb}_${year}${month}${day}${underscore}${hour}${min}${sec}.sql.gz"
  echo "Finish Backup Full for database '$listdb' in $path at ${year}${month}${day}${underscore}${hour}${min}${sec}"



  # File dan ukuran
  FILEF="$path/[FULL]_${listdb}_${year}${month}${day}${underscore}${hour}${min}${sec}.sql.gz"
  if [[ -f "$FILEF" ]]; then
    FILE_SIZEFB=$(stat -c%s "$FILEF")
    FILE_SIZEFM=$(echo "scale=2; $FILE_SIZEFB / 1024 / 1024" | bc)

    # Masukkan log ke database

######## Script Begin ########
######## Script Finish ########

finishbackup=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_endf=$(date +%s)

# Menghitung selisih waktu dalam detik
finish=$((epoch_endf - epoch_startf))

# Menghitung hari, jam, menit, dan detik
days=$((finish / 86400))
hours=$(((finish % 86400) / 3600))
minutes=$(((finish % 3600) / 60))
seconds=$((finish % 60))

# Format output
formatted_time=$(printf "%d:%02d:%02d:%02d" "$days" "$hours" "$minutes" "$seconds")

echo "Duration Process"
echo "Duration : $formatted_time"
echo ""

psql -d dbatools -Atc "INSERT INTO public.backuplogduration (host, backuptime, start, finish, duration,dbname, type, size_mb, size, filename)
VALUES ('$Server', '$backuptime','$startbackup','$finishbackup','$formatted_time','$listdb', 'FULL', CAST(ROUND($FILE_SIZEFM) AS BIGINT), '$FILE_SIZEFB', '$FILEF' );"


  else
    echo "Error: Backup file for database '$listdb' not found!"
  fi

  # Tampilkan log backup
  psql -d dbatools -Atc "SELECT * FROM public.backuplogduration order by backuptime desc;"
done

# Backup selesai
echo ""
echo "Backup finish $infotgl"
echo ""

# Informasi akhir
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"

-bash-4.2$