correlation with https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
correlation with https://teguhth.blogspot.com/2025/01/script-backup-full-all-database-using.html
1. create table
-- Table: public.backuplogs
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_inc_daily_with_record.sh
3. run sh backup_full_inc_daily_with_record.sh
4. run sh backup_full_inc_daily_with_record.sh after delee backup full
5. check record
SELECT * FROM backuplogs ORDER BY DATE desc ;
6. script
[postgres@teguhth recordtotbl]$ pwd
/var/lib/pgsql/script/fullinc/recordtotbl
[postgres@teguhth recordtotbl]$
[postgres@teguhth recordtotbl]$ cat backup_full_inc_daily_with_record.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"
# 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" ");
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;
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.backuplogs (host, date, dbname, backup, size_mb, size, filename)
VALUES ('$Server', NOW(), '$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" ");
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"
# 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.backuplogs (host, date, dbname, backup, size_mb, size, filename)
VALUES ('$Server', NOW(), '$listdb', 'FULL', CAST(ROUND($FILE_SIZEFM) AS BIGINT), '$FILE_SIZEFB', '$FILEF');"
fi
# Tampilkan log backup
psql -d dbatools -Atc "SELECT * FROM public.backuplogs order by date desc limit 1;"
fi # ✅ tutup if-else
# Tampilkan log backup
psql -d dbatools -Atc "SELECT * FROM public.backuplogs order by date 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 recordtotbl]$
/var/lib/pgsql/script/fullinc/recordtotbl
[postgres@teguhth recordtotbl]$
[postgres@teguhth recordtotbl]$ cat backup_full_inc_daily_with_record.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"
# 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" ");
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;
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.backuplogs (host, date, dbname, backup, size_mb, size, filename)
VALUES ('$Server', NOW(), '$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" ");
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"
# 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.backuplogs (host, date, dbname, backup, size_mb, size, filename)
VALUES ('$Server', NOW(), '$listdb', 'FULL', CAST(ROUND($FILE_SIZEFM) AS BIGINT), '$FILE_SIZEFB', '$FILEF');"
fi
# Tampilkan log backup
psql -d dbatools -Atc "SELECT * FROM public.backuplogs order by date desc limit 1;"
fi # ✅ tutup if-else
# Tampilkan log backup
psql -d dbatools -Atc "SELECT * FROM public.backuplogs order by date 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 recordtotbl]$
No comments:
Post a Comment