Berikut ini adalah script untuk membackup database PostgreSQL secara otomatis yang dapat dibuatkan jadwal untuk membackup database secara harian,mingguan atau bulanan.
Script ini berasal dari http://www.fsf.org dengan beberapa perbaikan yang disesuaikan pada keperluan penulis.
Sebelum menjalankan script backup ini yang harus dilakukan terlebih dulu adalah:
1. Setting IP Address pada pg_hba.conf yang terletak di /etc/postgresql/8.1/main.
Syntaxnya : host all all
Contoh : host all all 192.165.2.100 255.255.255.0 md5
2. Setting user,password pada .pgpass
Syntaxnya :
chmod file .pgpass 600.
Contoh : 192.165.2.100:5432:dataku:admin:admin123
Chmod 600 .pgpass
3. Buat nama file script tersebut dengan extensi .sh, misalnya auto.sh dan letakkan di /usr/local/bin. Chmod file auto.sh 755.
4. Buat entry schedule pada crontab pada folder /etc/.
Caranya :
a. Ketik crontab –e enter.
b. Syntax : m h dom mon dow filename.
m : menit.
h : jam.
dom : tanggal (0-31).
mon : bulan (1-12).
dow : hari (1-7),1=Senin .
filename : nama file dari script yang di buat.
Misalnya untuk menjalankan backup daily setiap jam 10 adalah :
00 10 * * * /usr/local/bin/auto.sh
Script auto.sh adalah sebagai berikut :
# Catatan untuk setting postgrebackup:
# Secara default, settingan postgrebackup adalah tertutup, dalam arti kita mesti menyetel
# metode otentifikasinya terlebih dahulu sebelum dapat digunakan. Kita menyetelnya di file
# /etc/postgresql/8.1/main/pg_hba.conf. Untuk menggunakan program ini, kita dapat menyetelnya seperti ini:
# host all all 192.168.1.100/32 md5
# Dimana 192.168.1.100 itu adalah IP tempat kita melakukan backup.
# Ada pula satu buah trick agar kita dapat melakukan backup otomatis di postgrebackup.
# Postgrebackup menggunakan utility build-innya untuk melakukan backup yaitu pg_dump dan pg_dumpall.
# Karena tingkat securitynya yang tinggi, kita tidak dapat menyebutkan password postgrebackup di dalam
# option pg_dump. Beruntungnya, postgrebackup telah menyediakan mekanisme password ini melalui file
# bername .pgpass. Isi dari file ini adalah: dbhost:5432:dbname:username:password (dbhost adalah
# IP dari server postgrebackup kita. Jadi, sehubungan dengan program ini, file .pgpass ini akan berisi:
# 192.168.1.200:5432:dbname:username:password
# Jangan lupa untuk me-chmod file ini 600.
###############################
# START SCRIPT - MULAI SCRIPT #
###############################
#!/bin/bash
USERNAME=USERNAME # username for the connection. username utk koneksinya
DBHOST=HOSTDB # match your pgbackup server IP. sesuaikan dengan IP server pgbackup kamu
DBNAMES=DATABASE # the database name. nama databasenya
BACKUPDIR=FOLDERBACKUP # the location to put the backup files. tempat backup files
MAILCONTENT="stdout"
MAILADDR="arwind@telkom.net This email address is being protected from spam bots, you need Javascript enabled to view it "
COMP=gzip
OPT="all"
# What do we want to backup.
# Apa yg mau di backup:
# "all" = schema + data
# "schema" = schema
# "data" = data
SEPDIR="yes"
DOWEEKLY=6
# On what day we want the weekly backup to be done. 1 = Monday
# Pada hari apa kita ingin backup mingguan dilakukan, 1 = Senin
DATE=`date +%Y-%m-%d_%Hh%Mm` # Datestamp misalnya 2006-03-21
DOW=`date +%A` # Day of the week, misalnya Sunday
DNOW=`date +%u` # Day number of the week, misalnya Senin = 1
DOM=`date +%d` # Date of the month, misalnya 27
M=`date +%B` # Bulan, misalnya January
W=`date +%V` # Week number, misalnya minggu ke-37
LOGFILE=$BACKUPDIR/$DBHOST-`date +%N`.log # Nama logfilenya
LOGERR=$BACKUPDIR/ERRORS_$DBHOST-`date +%N`.log # Nama logfile utk error
BACKUPFILES=""
OPT=""
# Create necessary directory
# Create direktori yang diperlukan
if [ ! -e "$BACKUPDIR" ] then
mkdir -p "$BACKUPDIR"
fi
if [ ! -e "$BACKUPDIR/daily" ] # Check if Daily Directory exists.
then
mkdir -p "$BACKUPDIR/daily"
fi
if [ ! -e "$BACKUPDIR/weekly" ] # Check if Weekly Directory exists.
then
mkdir -p "$BACKUPDIR/weekly"
fi
if [ ! -e "$BACKUPDIR/monthly" ] # Check if Monthly Directory exists.
then
mkdir -p "$BACKUPDIR/monthly"
fi
# Make logfile
# Membuat logfile
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # Link file descriptor #6 with stdout.
# Saves stdout.
touch $LOGERR
exec 7>&2 # Link file descriptor #7 with stderr.
# Saves stderr.
exec 2> $LOGERR # stderr replaced with file $LOGERR.
if [ "$OPT" = "all" ]; then
OPT = ""
elif [ "OPT" = "data" ]; then
OPT = "-a"
elif [ "OPT" = "schema" ]; then
OPT = "-s"
fi
# Database dump function
dbdump () {
pg_dump -i -h $DBHOST -p 5432 -U $USERNAME -F c -d -v -f $2 $DBNAMES
return 0
}
# Compression function plus latest copy
SUFFIX=""
compression () {
if [ "$COMP" = "gzip" ]; then
gzip -f "$1"
echo
echo Backup Information for "$1"
gzip -l "$1.gz"
SUFFIX=".gz"
elif [ "$COMP" = "bzip2" ]; then
echo Compression information for "$1.bz2"
bzip2 -f -v $1 2>&1
SUFFIX=".bz2"
else
echo "No compression option set, check advanced settings"
fi
return 0
}
# Hostname for LOG information
if [ "$DBHOST" = "localhost" ]; then
HOST=`hostname`
if [ "$SOCKET" ]; then
OPT="$OPT --socket=$SOCKET"
fi
else
HOST=$DBHOST
fi
# Test is seperate DB backups are required
if [ "$SEPDIR" = "yes" ]; then
echo Backup Start Time `date`
echo ======================================================================
# Monthly Full Backup of all Databases
if [ $DOM = "01" ]; then
for MDB in $DBNAMES
do
# Prepare $DB for using
MDB="`echo $MDB | sed 's/%/ /g'`"
if [ ! -e "$BACKUPDIR/monthly/$MDB" ] # Check Monthly DB Directory exists.
then
mkdir -p "$BACKUPDIR/monthly/$MDB"
fi
echo Monthly Backup of $MDB...
dbdump "$MDB" "$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.backup"
compression "$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.backup"
BACKUPFILES="$BACKUPFILES $BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.backup$SUFFIX"
echo ----------------------------------------------------------------------
done
fi
for DB in $DBNAMES
do
# Prepare $DB for using
DB="`echo $DB | sed 's/%/ /g'`"
# Create Seperate directory for each DB
if [ ! -e "$BACKUPDIR/daily/$DB" ] # Check Daily DB Directory exists.
then
mkdir -p "$BACKUPDIR/daily/$DB"
fi
if [ ! -e "$BACKUPDIR/weekly/$DB" ] # Check Weekly DB Directory exists.
then
mkdir -p "$BACKUPDIR/weekly/$DB"
fi
# Weekly Backup
if [ $DNOW = $DOWEEKLY ]; then
echo Weekly Backup of Database \( $DB \)
echo Rotating 5 weeks Backups...
if [ "$W" -le 05 ];then
REMW=`expr 48 + $W`
elif [ "$W" -lt 15 ];then
REMW=0`expr $W - 5`
else
REMW=`expr $W - 5`
fi
eval rm -fv "$BACKUPDIR/weekly/$DB_week.$REMW.*"
echo
dbdump "$DB" "$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.backup"
compression "$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.backup"
BACKUPFILES="$BACKUPFILES $BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.backup$SUFFIX"
echo ----------------------------------------------------------------------
fi
# Daily Backup
# else
echo Daily Backup of Database \( $DB \)
echo Rotating last weeks Backup...
eval rm -fv "$BACKUPDIR/daily/$DB/*.$DOW.backup.*"
echo
dbdump "$DB" "$BACKUPDIR/daily/$DB/${DB}_$DATE.backup"
compression "$BACKUPDIR/daily/$DB/${DB}_$DATE.backup"
BACKUPFILES="$BACKUPFILES $BACKUPDIR/daily/$DB/${DB}_$DATE.backup$SUFFIX"
echo ----------------------------------------------------------------------
#fi
done
echo Backup End `date`
echo ======================================================================
echo Total disk space used for backup storage..
echo Size - Location
echo `du -hs "$BACKUPDIR"`
echo
#Clean up IO redirection
exec 1>&6 6>&- # Restore stdout and close file descriptor #6.
exec 1>&7 7>&- # Restore stdout and close file descriptor #7.
if [ "$MAILCONTENT" = "files" ]
then
if [ -s "$LOGERR" ]
then
# Include error log if is larger than zero.
BACKUPFILES="$BACKUPFILES $LOGERR"
ERRORNOTE="WARNING: Error Reported - "
fi
#Get backup size
ATTSIZE=`du -c $BACKUPFILES | grep "[[:digit:][:space:]]total$" |sed s/\s*total//`
if [ $MAXATTSIZE -ge $ATTSIZE ]
then
BACKUPFILES=`echo "$BACKUPFILES" | sed -e "s# # -a #g"` #enable multiple attachments
mutt -s "$ERRORNOTE Pgbackup Log and backup Files for $HOST - $DATE" $BACKUPFILES $MAILADDR < $LOGFILE #send via mutt
else
cat "$LOGFILE" | mail -s "WARNING! - Pgbackup exceeds set maximum attachment size on $HOST - $DATE" $MAILADDR
fi
elif [ "$MAILCONTENT" = "log" ]
then
cat "$LOGFILE" | mail -s "Pgbackup Log for $HOST - $DATE" $MAILADDR
if [ -s "$LOGERR" ]
then
cat "$LOGERR" | mail -s "ERRORS REPORTED: Pgbackup error Log for $HOST - $DATE" $MAILADDR
fi
elif [ "$MAILCONTENT" = "quiet" ]
then
if [ -s "$LOGERR" ]
then
cat "$LOGERR" | mail -s "ERRORS REPORTED: Pgbackup error Log for $HOST - $DATE" $MAILADDR
cat "$LOGFILE" | mail -s "Pgbackup Log for $HOST - $DATE" $MAILADDR
fi
else
if [ -s "$LOGERR" ]
then
cat "$LOGFILE"
echo
echo "###### WARNING ######"
echo "Errors reported during AutoPgbackup execution.. Backup failed"
echo "Error log below.."
cat "$LOGERR"
else
cat "$LOGFILE"
fi
fi
if [ -s "$LOGERR" ]
then
STATUS=1
else
STATUS=0
fi
fi
# Clean up Logfile
eval rm -f "$LOGFILE"
eval rm -f "$LOGERR"
exit $STATUS
Script ini akan membuat folder backup dan di dalam folder backup akan terdapat folder daily, weekly, dan monthly. Nama dari folder-folder tersebut di atas dapat anda ubah sesuai keinginan anda. Semua database yang telah di backup telah di kompres sehingga datanya tidak akan terlalu besar mengambil space hardisk. Semoga script ini bisa menjadi manfaat.
Tidak ada komentar:
Posting Komentar