postgres数据库的单表恢复备份

最近写的一个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#