.::: Script to Restore Backup Full & all Transaction (Restore Incremental) in one Command for EnterpriseDB PostgreSQL :::.

 

correlation https://teguhth.blogspot.com/2024/02/script-to-restore-backup-full-all.html
correlation https://teguhth.blogspot.com/2025/09/script-daily-backup-full-incremental.html

1. check before restore
 
2. check directory backup & create dblist,txt
[postgres@teguhth dwh]$ pwd
/var/lib/pgsql/16/manual/dwh
[postgres@teguhth dwh]$
[postgres@teguhth dwh]$ cat dblist.txt
FULL_dwh_20250913_000001.sql.gz
INC_dwh_20250913_010002.sql.gz
INC_dwh_20250913_020002.sql.gz
INC_dwh_20250913_030002.sql.gz
INC_dwh_20250913_040002.sql.gz
INC_dwh_20250913_050002.sql.gz
INC_dwh_20250913_060002.sql.gz
INC_dwh_20250913_070001.sql.gz
INC_dwh_20250913_080002.sql.gz
INC_dwh_20250913_090002.sql.gz
INC_dwh_20250913_100001.sql.gz
INC_dwh_20250913_110002.sql.gz
INC_dwh_20250913_120002.sql.gz
INC_dwh_20250913_130001.sql.gz
INC_dwh_20250913_140002.sql.gz
INC_dwh_20250913_150002.sql.gz
INC_dwh_20250913_160001.sql.gz
INC_dwh_20250913_170002.sql.gz
INC_dwh_20250913_180002.sql.gz
INC_dwh_20250913_190001.sql.gz
INC_dwh_20250913_200002.sql.gz
INC_dwh_20250913_210002.sql.gz
INC_dwh_20250913_220002.sql.gz
INC_dwh_20250913_230001.sql.gz
[postgres@teguhth dwh]$
 
3. run script restore using full & incremental without FULL
 

4. run script restore using full & incremental with FULL
 

 

5. create script to restore all file

 

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

infotgl=$(date)
dbversion=$(psql -Atc "select version();");
dbname=dwh
echo ".::: Restore FULl & Incremental PostgreSQL EDB using custom database'$dbname' in'$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
datet=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
listdb="dblist.txt";
path="/var/lib/pgsql/16/manual/dwh"

#echo "Start Restore Full & Transaction Log database '$dbname' from $path at $datet"
#echo ""
echo "List File Backup as: "
#cat $listdb

sed 's/^/* /' dblist.txt > hasil.txt
echo ""
cat hasil.txt
echo ""
echo "Size file backup:"
##ls -lh $path
echo ""

# Validasi keberadaan file FULL
if ! grep -q "FULL_" $listdb; then
  echo "ERROR: File FULL backup tidak ada di $listdb"
  echo "Restore dibatalkan!"
  echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""
  exit 1
fi

echo ""

echo "Start Restore Full & Transaction Log database '$dbname' from $path at $datet"
echo ""

  psql -Atc "drop database $dbname;"
  psql -Atc "create database $dbname;"

for listdbs in  $(cat $listdb | tr -d '\r')

do
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
echo "$datex --->>> Restore FULL & Incremental database in '$dbname' from $path/$listdbs"

  gunzip -c $path"/"$listdbs  | psql --set ON_ERROR_STOP=off -d $dbname

done;
datez=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
echo ""
echo "$datez --->>> Finished Restore FULL & Incremental in '$dbname' from $path/$listdbs"
echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""

[postgres@teguhth dwh]$