postgreSQLのDBを別PCにバックアップ

Rev.3を表示中。最新版はこちら。

訳あってperlとshの組み合わせになってます。

・まず、perlでDBバックアップと圧縮するスクリプトファイルを作る。(db_dump.pl)

#!/usr/bin/perl

($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime(time());
$date = sprintf("%d%02d%02d", (1900 + $year), ($mon + 1), $mday);
$time = sprintf(" %02d:%02d:%02d", $hour, $min, $sec);

$filedir = "/home/postgres/sbin/";
$ftp_sh_fullpath = "/home/postgres/sbin/ftpcom.sh";
$logfile = "./dbdump_log";
$db_name = "test_db";
$out_name = $filedir . $db_name . $date;
$zip_name = $filedir . $db_name . $date . ".zip";
$only_zip_name = $db_name . $date . ".zip";
$log_str = $date . $time . " Start : Database Buckup.\n";
$err = 0;

if (system("pg_dump -d $db_name > $out_name")) {
    &date_renew;
    $err = 1;
    $log_str .= $date . $time . " Error : It failed in database " . $db_name . " dump.\n";
    &write_log_exit;
} else {
    &date_renew;
    $log_str .= $date . $time . " Notice : It succeeded in database " . $db_name . " dump.\n";
}

if ( -e $out_name ) {
    if (system("zip -jq $zip_name $out_name")) {
    &date_renew;
    $err = 1;
    $log_str .= $date . $time . " Error : It seems to have failed in the " . $db_name . $date . " file compression.\n";
    &write_log_exit;
    } else {
        &date_renew;
        $log_str .= $date . $time . " Notice : " . $db_name . $date . " file was compressed, and ". $only_zip_name . " file was made.\n";
        system("rm -f $out_name");
    }
} else {
    &date_renew;
    $err = 1;
    $log_str .= $date . $time . " Error : It lost sight of dumped data " . $db_name . $date . ".\n";
    &write_log_exit;
}

if ( -e $zip_name ){
    if (system("/bin/sh $ftp_sh_fullpath $only_zip_name")) {
    $err = 1;
    &date_renew;
    $log_str .= $date . $time . " Error : It failed in forwarding " . $zip_name . ".\n";
    &write_log_exit;
    } else {
        &date_renew;
        $log_str .= $date . $time . " Notice : " . $only_zip_name . " was forwarded according to " . $ftp_sh_fullpath . ".\n";
    system("rm -f $zip_name");
    }
} else {
    &date_renew;
    $err = 1;
    $log_str .= $date . $time . " Error : It lost sight of ziped data " . $only_zip_name . ".\n";
    &write_log_exit;
}
&write_log_exit;
exit;

sub date_renew {
    ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime(time());
    $date = sprintf("%d%02d%02d", (1900 + $year), ($mon + 1), $mday);
    $time = sprintf(" %02d:%02d:%02d", $hour, $min, $sec);
}

sub write_log_exit {
    if ($err == 1) {
        $log_str .= $date . $time . " End : Abnormal termination.\n";
    } else {
    $log_str .= $date . $time . " End : Normal completion.\n";
    }

    open(OUT , ">> $logfile");
    print OUT $log_str;
    close(OUT);
    exit;
}

※ログを出力してるけど、FTPアップロードに失敗しても、「Normal completion.」と出てしまうので注意。

次に、shでFTPでアップロードするスクリプトを作る。(ftpcom.sh)

HOST_IP="192.168.11.4"
USERNAME="octuser"
PASSWORD="octuser123"
HOST_DIR="/home/octuser/nmb_db_backup"
LOCAL_DIR="/home/postgres/sbin/"
#=======================================
ftp -n << EOF >& /dev/null
open $HOST_IP
user $USERNAME $PASSWORD
lcd $LOCAL_DIR
cd $HOST_DIR
binary
put $1
bye
EOF