.jpeg)
https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
https://teguhth.blogspot.com/2025/09/script-backup-full-incremental-all.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
3. check result backup record
psql -d dbatools -c "SELECT * FROM public.backuplogduration order by start desc;;"
psql -d dbatools -Atc "SELECT * FROM public.backuplogduration order by start desc;;"
SELECT *,VERSION() from public.backuplogduration order by start desc;
SELECT * from backuplogduration ORDER BY start DESC;
4. if want to delete record
delete from backuplogduration;
5. script
[postgres@teguhth durationtotb1]$ pwd
/var/lib/pgsql/script/fullinc/durationtotb1
[postgres@teguhth durationtotb1]$
[postgres@teguhth durationtotb1]$ cat backup_full_inc_daily_with_record_duration.sh
#$ cat backup_full_inc_daily_with_record_duration.sh
# -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="/var/lib/pgsql/outputdb"
backuptime=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
# Interval backup incremental
intervalinc="1 hour"
# 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
mkdir -p $backuppath/$year$month$day/$listdb;
path="$backuppath/$year$month$day/$listdb";
# Check for files with "full" and "log" filters
full_files=$(find "$path" -type f -name "*FULL*")
if [ -n "$full_files" ] ; then
echo ""
echo "Running Backup Log"
echo ""
# incremental backup logic ...
mkdir -p $backuppath/$year$month$day/$listdb;
path="$backuppath/$year$month$day/$listdb";
#dateinc=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
dateinc=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
startbackupinc=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_startinc=$(date +%s)
countx=$(psql -d dbatools -Atc "SELECT count(query)
FROM (
SELECT (to_timestamp(trim(replace(session_start_time, 'WIB', '')), 'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'Asia/Jakarta') AS session_ts,
dbname, command_tag, query
FROM pgaudit_log
) t
WHERE session_ts >= (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta') - INTERVAL '$intervalinc'
AND session_ts < (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta')
AND dbname = '$listdb'
AND command_tag != 'SELECT';");
#counttotal=$(psql -d dbatools -Atc "SELECT COUNT(*) FROM pgaudit_log");
counttotal=$(psql -d dbatools -Atc "SELECT count(query)
FROM (
SELECT (to_timestamp(trim(replace(session_start_time, 'WIB', '')), 'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'Asia/Jakarta') AS session_ts,
dbname, command_tag, query
FROM pgaudit_log
) t
WHERE session_ts >= (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta') - INTERVAL '$intervalinc'
AND session_ts < (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta');");
echo "$dateinc --->>> Backup Incremental($countx transactions from $counttotal queries) for database '$listdb' in $path"
echo "--- $dateinc --->>> Backup Incremental ($countx transactions from $counttotal queries) for database '$listdb' in $path" > "$path"/INC_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql;
#pg_dump $listdb | gzip -c > "$path"/INC_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql.gz;
psql -d dbatools -Atc "SELECT query
FROM (
SELECT (to_timestamp(trim(replace(session_start_time, 'WIB', '')), 'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'Asia/Jakarta') AS session_ts,
dbname, command_tag, query
FROM pgaudit_log
) t
WHERE session_ts >= (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta') - INTERVAL '$intervalinc'
AND session_ts < (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta') and dbname ='$listdb' AND command_tag !='SELECT'
ORDER BY session_ts asc;" >> "$path"/INC_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql;
dateincy=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
echo "--- $dateincy --->>> Finish Backup Incremental ($countx transactions from $counttotal queries)for database '$listdb' in $path" >> "$path"/INC_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql;
# compress backup
gzip "$path"/INC_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql;
finishbackupinc=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_endinc=$(date +%s)
# Menghitung selisih waktu dalam detik
finishinc=$((epoch_endinc - epoch_startinc))
# Menghitung hari, jam, menit, dan detik
daysi=$((finishinc / 86400))
hoursi=$(((finishinc % 86400) / 3600))
minutei=$(((finishinc % 3600) / 60))
secondsi=$((finishinc % 60))
# Format output
formatted_timeinc=$(printf "%d:%02d:%02d:%02d" "$daysi" "$hoursi" "$minutesi" "$secondsi")
echo "$dateincy --->>> Finish Backup Incremental ($countx transactions from $counttotal queries) for database '$listdb' in $path"
FILEF="$path/INC_${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)
psql -d dbatools -Atc "INSERT INTO public.backuplogduration (host, backuptime, start, finish, duration,dbname, type, size_mb, size, filename)
VALUES ('$Server', '$backuptime','$startbackupinc','$finishbackupinc','$formatted_timeinc','$listdb', 'INC', CAST(ROUND($FILE_SIZEFM) AS BIGINT), '$FILE_SIZEFB', '$FILEF' );"
fi
else
echo ""
echo "Running Backup Full"
echo ""
datefx=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
startbackupfull=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_startfull=$(date +%s)
echo "$datefx --->>> Backup Full for database '$listdb' in $path"
pg_dump $listdb | gzip -c > "$path/FULL_${listdb}_${year}${month}${day}${underscore}${hour}${min}${sec}.sql.gz";
datefy=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
echo "$datefy --->>> Finish Backup Full for database '$listdb' in $path"
finishbackupfull=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_endfull=$(date +%s)
# Menghitung selisih waktu dalam detik
finishfull=$((epoch_endfull - epoch_startfull))
# Menghitung hari, jam, menit, dan detik
daysf=$((finishfull / 86400))
hoursf=$(((finishfull % 86400) / 3600))
minutesf=$(((finishfull % 3600) / 60))
secondsf=$((finishfull % 60))
# Format output
formatted_timefull=$(printf "%d:%02d:%02d:%02d" "$daysf" "$hoursf" "$minutesf" "$secondsf")
# 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)
psql -d dbatools -Atc "INSERT INTO public.backuplogduration (host, backuptime, start, finish, duration,dbname, type, size_mb, size, filename)
VALUES ('$Server', '$backuptime','$startbackupfull','$finishbackupfull','$formatted_timefull','$listdb', 'FULL', CAST(ROUND($FILE_SIZEFM) AS BIGINT), '$FILE_SIZEFB', '$FILEF' );"
fi
# 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 ""
# Tampilkan log backup
psql -d dbatools -Atc "SELECT * FROM public.backuplogduration order by start desc limit 1;"
fi # ? tutup if-else
# Tampilkan log backup
psql -d dbatools -Atc "SELECT * FROM public.backuplogduration order by start 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"
[postgres@teguhth durationtotb1]$
https://teguhth.blogspot.com/2025/09/script-backup-full-incremental-all.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
3. check result backup record
psql -d dbatools -c "SELECT * FROM public.backuplogduration order by start desc;;"
psql -d dbatools -Atc "SELECT * FROM public.backuplogduration order by start desc;;"
SELECT *,VERSION() from public.backuplogduration order by start desc;
SELECT * from backuplogduration ORDER BY start DESC;
4. if want to delete record
delete from backuplogduration;
5. script
[postgres@teguhth durationtotb1]$ pwd
/var/lib/pgsql/script/fullinc/durationtotb1
[postgres@teguhth durationtotb1]$
[postgres@teguhth durationtotb1]$ cat backup_full_inc_daily_with_record_duration.sh
#$ cat backup_full_inc_daily_with_record_duration.sh
# -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="/var/lib/pgsql/outputdb"
backuptime=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
# Interval backup incremental
intervalinc="1 hour"
# 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
mkdir -p $backuppath/$year$month$day/$listdb;
path="$backuppath/$year$month$day/$listdb";
# Check for files with "full" and "log" filters
full_files=$(find "$path" -type f -name "*FULL*")
if [ -n "$full_files" ] ; then
echo ""
echo "Running Backup Log"
echo ""
# incremental backup logic ...
mkdir -p $backuppath/$year$month$day/$listdb;
path="$backuppath/$year$month$day/$listdb";
#dateinc=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
dateinc=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
startbackupinc=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_startinc=$(date +%s)
countx=$(psql -d dbatools -Atc "SELECT count(query)
FROM (
SELECT (to_timestamp(trim(replace(session_start_time, 'WIB', '')), 'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'Asia/Jakarta') AS session_ts,
dbname, command_tag, query
FROM pgaudit_log
) t
WHERE session_ts >= (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta') - INTERVAL '$intervalinc'
AND session_ts < (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta')
AND dbname = '$listdb'
AND command_tag != 'SELECT';");
#counttotal=$(psql -d dbatools -Atc "SELECT COUNT(*) FROM pgaudit_log");
counttotal=$(psql -d dbatools -Atc "SELECT count(query)
FROM (
SELECT (to_timestamp(trim(replace(session_start_time, 'WIB', '')), 'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'Asia/Jakarta') AS session_ts,
dbname, command_tag, query
FROM pgaudit_log
) t
WHERE session_ts >= (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta') - INTERVAL '$intervalinc'
AND session_ts < (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta');");
echo "$dateinc --->>> Backup Incremental($countx transactions from $counttotal queries) for database '$listdb' in $path"
echo "--- $dateinc --->>> Backup Incremental ($countx transactions from $counttotal queries) for database '$listdb' in $path" > "$path"/INC_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql;
#pg_dump $listdb | gzip -c > "$path"/INC_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql.gz;
psql -d dbatools -Atc "SELECT query
FROM (
SELECT (to_timestamp(trim(replace(session_start_time, 'WIB', '')), 'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'Asia/Jakarta') AS session_ts,
dbname, command_tag, query
FROM pgaudit_log
) t
WHERE session_ts >= (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta') - INTERVAL '$intervalinc'
AND session_ts < (date_trunc('hour', now()) AT TIME ZONE 'Asia/Jakarta') and dbname ='$listdb' AND command_tag !='SELECT'
ORDER BY session_ts asc;" >> "$path"/INC_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql;
dateincy=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
echo "--- $dateincy --->>> Finish Backup Incremental ($countx transactions from $counttotal queries)for database '$listdb' in $path" >> "$path"/INC_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql;
# compress backup
gzip "$path"/INC_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql;
finishbackupinc=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_endinc=$(date +%s)
# Menghitung selisih waktu dalam detik
finishinc=$((epoch_endinc - epoch_startinc))
# Menghitung hari, jam, menit, dan detik
daysi=$((finishinc / 86400))
hoursi=$(((finishinc % 86400) / 3600))
minutei=$(((finishinc % 3600) / 60))
secondsi=$((finishinc % 60))
# Format output
formatted_timeinc=$(printf "%d:%02d:%02d:%02d" "$daysi" "$hoursi" "$minutesi" "$secondsi")
echo "$dateincy --->>> Finish Backup Incremental ($countx transactions from $counttotal queries) for database '$listdb' in $path"
FILEF="$path/INC_${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)
psql -d dbatools -Atc "INSERT INTO public.backuplogduration (host, backuptime, start, finish, duration,dbname, type, size_mb, size, filename)
VALUES ('$Server', '$backuptime','$startbackupinc','$finishbackupinc','$formatted_timeinc','$listdb', 'INC', CAST(ROUND($FILE_SIZEFM) AS BIGINT), '$FILE_SIZEFB', '$FILEF' );"
fi
else
echo ""
echo "Running Backup Full"
echo ""
datefx=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
startbackupfull=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_startfull=$(date +%s)
echo "$datefx --->>> Backup Full for database '$listdb' in $path"
pg_dump $listdb | gzip -c > "$path/FULL_${listdb}_${year}${month}${day}${underscore}${hour}${min}${sec}.sql.gz";
datefy=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
echo "$datefy --->>> Finish Backup Full for database '$listdb' in $path"
finishbackupfull=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_endfull=$(date +%s)
# Menghitung selisih waktu dalam detik
finishfull=$((epoch_endfull - epoch_startfull))
# Menghitung hari, jam, menit, dan detik
daysf=$((finishfull / 86400))
hoursf=$(((finishfull % 86400) / 3600))
minutesf=$(((finishfull % 3600) / 60))
secondsf=$((finishfull % 60))
# Format output
formatted_timefull=$(printf "%d:%02d:%02d:%02d" "$daysf" "$hoursf" "$minutesf" "$secondsf")
# 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)
psql -d dbatools -Atc "INSERT INTO public.backuplogduration (host, backuptime, start, finish, duration,dbname, type, size_mb, size, filename)
VALUES ('$Server', '$backuptime','$startbackupfull','$finishbackupfull','$formatted_timefull','$listdb', 'FULL', CAST(ROUND($FILE_SIZEFM) AS BIGINT), '$FILE_SIZEFB', '$FILEF' );"
fi
# 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 ""
# Tampilkan log backup
psql -d dbatools -Atc "SELECT * FROM public.backuplogduration order by start desc limit 1;"
fi # ? tutup if-else
# Tampilkan log backup
psql -d dbatools -Atc "SELECT * FROM public.backuplogduration order by start 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"
[postgres@teguhth durationtotb1]$
No comments:
Post a Comment