.::: Script Backup Full All Database Using Record to Table in PostgreSQL EDB :::.

 

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

1. create database & table
create database dbatools;
CREATE TABLE backuplogs (
    host VARCHAR(255) NOT NULL,
    date TIMESTAMP NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    backup 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.sh
 

 

3. check result backup record
psql -d dbatools -c "SELECT * FROM public.backuplogs;"
psql -d dbatools -Atc "SELECT * FROM public.backuplogs;"
SELECT *,VERSION() from public.backuplogs;
 

 

4. if want to delete record

5. delete from backuplogs;

6. script

-bash-4.2$ pwd
/var/lib/pgsql/script/backup/record
-bash-4.2$
-bash-4.2$ cat backup_full_daily_with_record.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"

# 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"

  # 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
    psql -d dbatools -Atc "INSERT INTO public.backuplogs (host, date, dbname, backup, size_mb, size, filename)
    VALUES ('$Server', NOW(), '$listdb', 'FULL', '$FILE_SIZEFM', '$FILE_SIZEFB', '$FILEF');"
  else
    echo "Error: Backup file for database '$listdb' not found!"
  fi

  # Tampilkan log backup
  psql -d dbatools -Atc "SELECT * FROM public.backuplogs;"
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$