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

 

correlation https://teguhth.blogspot.com/2025/02/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. create table client

CREATE TABLE tbl_clients (
    client_id VARCHAR(20) PRIMARY KEY,  -- ID unik dalam format id-client-XX
    client_name VARCHAR(255) NOT NULL,  -- Nama client
    dbname VARCHAR(255) NOT NULL UNIQUE,  -- Nama database client
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Timestamp pembuatan data
);

3. insert client

INSERT INTO tbl_clients (client_id, client_name, dbname)
VALUES
    ('id-client-01', 'Client A', 'Adventureworks'),
    ('id-client-02', 'Client B', 'dbatools'),
    ('id-client-03', 'Client C', 'teguhth_erpdb'),
    ('id-client-04', 'Client D', 'db_lpg_raju');

INSERT INTO tbl_clients (client_id, client_name, dbname)
VALUES ('id-client-22', 'Client Teguh', 'teguhth');

4. check

select * from backuplogduration ORDER BY backuptime desc;
select * from tbl_clients;;
 

5. if delete

delete from backuplogduration;
delete from tbl_clients;

6. running script
 

7. check file
 

8. check record table

 

9. script as 

 

-bash-4.2$ cat backup_full_daily_with_record_insert.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 -d dbatools -Atc "SELECT datname FROM pg_database WHERE datname IN (SELECT dbname FROM tbl_clients);")

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;"
  psql -d dbatools -Atc "SELECT * FROM public.backuplogduration ORDER BY backuptime DESC LIMIT 1;"
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$