.::: Script Daily Backup FULL & Incremental PostgreSQL EDB Using pgdump & pgaudit :::.

 

correlation with https://teguhth.blogspot.com/2025/09/how-to-create-backup-incremental-in.html

1. Backup All FULL
 

 
2. Backup All LOG / Incremental
 

3. Backup combine FULL & log with sample FULL backup delete
 

4. Sample Daily Backup

  

5. Script backup_full_inc_edb.sh

 

[postgres@teguhth fullinc]$ cat backup_full_inc_edb.sh

echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address

dbversion=$(psql -Atc "select version();");

infotgl=$(date)
echo ".::: Backup Daily All database using custom in '$Server' with IP $ip_address :::."
echo ""
echo "PostgreSQL Version : '$Server' $dbversion"
echo ""
#!/bin/bash
#

echo "delete from pgaudit_log;"
psql -d dbatools -Atc "delete from pgaudit_log;"

echo "convert log to table "
psql -d dbatools -Atc "COPY pgaudit_log FROM '/var/lib/pgsql/16/data/log/pgaudit-teguhth.log' DELIMITER ',' CSV;"

echo ""

##dbname=$(psql -Atc "SELECT datname FROM pg_database where datname not in('template0','template1','postgres');");
dbname=$(psql -Atc "SELECT datname FROM pg_database where datname not in('template0','template1','postgres');");
#dbname=$(psql -Atc "SELECT datname FROM pg_database where datname in('dwh');");



#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
echo "Backup begin $infotgl"
echo ""
 backuppath="/var/lib/pgsql/outputdb";

intervalinc="1 hour"

echo "Backup Incremental every $intervalinc"
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"


    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"
    fi   # ✅ tutup if-else

done   # ✅ tutup for

######



echo ""
echo "Backup finish $infotgl"
echo ""

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

[postgres@teguhth fullinc]$