最近写的一个postgres数据库的单表恢复备份perl脚本
记录下来方便以后使用
Script name : sqlExec4PG.pl
#!/usr/bin/perl
use strict;
use Carp;
use IPC::Open2;
use IPC::Open3;
$|=1;
my $dbname = $ARGV[0];
my $user = $ARGV[1];
my $table = $ARGV[2];
my $mode = $ARGV[3];
my $PGSQL = "";
if ($dbname eq "" || $user eq "" ) {
croak ("DB name and user not specified.");
}
my $PSQL = `ps ax | grep postmaster | awk '{print \$5}' | sed 's#postmaster#psql#g' | egrep -v 'grep|sed|sh'`;
if($PSQL =~ /^\/.*\/psql$/){
$PSQL =~ s/^\s+//;
$PSQL =~ s/\s+$//;
$PGSQL = $PSQL ." -U ". $user;
print $PGSQL."\n";
}else{
croak("Error : Can not find the command in the system process");
exit 1;
}
my $TAB = " ";
my $rc = "0";
if( $mode eq "" )
{
if($table eq "")
{
croak("table name not specified.");
$rc = 1;
}
else
{
open(CMD, "| $PGSQL -q -d $dbname") or croak ("can't open psql command");
print CMD "set client_encoding TO SJIS;\n";
print CMD "copy $table from stdin;\n";
while (<STDIN>) {
print CMD $_;
}
close(CMD) or croak ("error while sql exec. Error msg=$!");
$rc = $?;
}
}
elsif ( $mode eq "dump" )
{
open(CMD, "| $PGSQL -q -A -F '$TAB' -t -d $dbname") or croak ("can't open psql command. Error msg=$!");
print CMD "set client_encoding TO SJIS;\n";
while (<STDIN>) {
#$_ =~ s/%%FUNC_CAST_INT%%/int4/g;
print CMD $_;cat #DATAFILE# | ./sqlExec.pl #DB NAME# #USER# #TBLNAME#
}
close(CMD) or croak ("error while sql exec. Error msg=$!");
$rc = $?;
}
else{
croak ("use error");
$rc = 1;
}
exit($rc);
useage :
if you load data to postgres database. use command below
cat #DATAFILE# | ./sqlExec4PG.pl #DBNAME# #USER# #TBLNAME#
if you will download tables data and write to file. use command below
echo "SQL" | ./sqlExec4PG.pl #DBNAME# #USER# "" dump > #DATAFILE#