PostgreSQL - Agente de backup para tabelas particionadas

 Com este agente escrito em Perl é possível fazer o backup de tabelas particionadas, desde que as mesmas estejam no padrão de documentação de particionamento de tabelas do PostgreSQL. Com este agente serão gerados dumps anuais em formato custom que serão validados pelo pg_restore através do argumento --list.

   Depois de feito o backup você pode retirar as tabelas dumpeadas da tabela pai e mandar um bom DROP TABLE nelas.


#!/usr/bin/perl
#dump-get
#
#Description: Script to generate dump table
#
#Author:
#        Gabriel Prestes
#
#02-09-2009 : Created

use strict;
use Getopt::Long;
use POSIX;
use File::Basename;

#--------------------------------------------------
# Setting environment
#--------------------------------------------------
$ENV{"USER"}="root";
$ENV{"HOME"}="/root";

#--------------------------------------------------
# Global variables
#--------------------------------------------------
our $name = basename($0);
our $version = "0.1a";
our $path = "/opt";
our $temp_log = "$path/$name.log";
our ($opt_help, $opt_version, $opt_db, $opt_host, $opt_table, $opt_user, $opt_year, $opt_port);

sub main {

        # --- Get Options --- #
        getoption();

# --- Set default port and user --- #
if(!$opt_user){$opt_user="postgres";}
if(!$opt_port){$opt_port=5432;}

# --- Init proc --- #
logger("INIT DUMP process");

# --- My vars --- #
my @cmd=();
my $counter;
my $flagcontrol=0;

# --- Create directory --- #
@cmd=`\$\(which mkdir\) $path/$opt_year-dumps >> $temp_log 2>&1`;
$flagcontrol=+$?;

# --- If directory problem --- #
if($flagcontrol!=0){logger("ERROR - Directory $path/$opt_year-dumps can not be created"); exit;}

# --- Generate dump --- #
for($counter=1;$counter<=12;$counter++){

         $counter = sprintf("%02d", $counter);

@cmd=`\$\(which pg_dump\) -h $opt_host -p $opt_port -U $opt_user -F c -v -f $path/$opt_year-dumps/$opt_table\_$opt_year\_$counter.dmp -t $opt_table\_$opt_year\_$counter $opt_db >> $temp_log 2>&1`;
$flagcontrol=+$?;

@cmd=`\$\(which pg_restore\) -l $path/$opt_year-dumps/$opt_table\_$opt_year\_$counter.dmp >> $temp_log 2>&1`;
$flagcontrol=+$?;

}

# --- Thresholds --- #
if($flagcontrol!=0){

logger("ERROR - Agent run with problem");
exit;

}

if($flagcontrol==0){

                logger("OK - Agent run without problem");
                exit;

        }

logger("UNKNOWN - Unexpected return");
        exit;

}

#--------------------------------------------------------------------------------------
sub getoption {
     Getopt::Long::Configure('bundling');
     GetOptions(

         'D|database=s'              => \$opt_db,
         'H|host=s'                  => \$opt_host,
             'T|table=s'                 => \$opt_table,
             'U|user=s'                  => \$opt_user,
             'V|version'                 => \$opt_version,
             'Y|year=i'                  => \$opt_year,
             'h|help'                    => \$opt_help,
             'p|port=i'                  => \$opt_port,
        );

     if($opt_help){

             printHelp();
             exit;

     }

     if($opt_version){

             print "$name - '$version'\n";
             exit;

     }

     if((!$opt_host) or (!$opt_db) or (!$opt_year) or (!$opt_table)){

printUsage();
exit;

     }

}
#--------------------------------------------------------------------------------------
sub logger {

        my $msg = shift (@_);

        my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
        $wday++;
        $yday++;
        $mon++;
        $year+=1900;
        $isdst++;

           open(LOG, ">>$temp_log");
           printf LOG ("%02i/%02i/%i - %02i:%02i:%02i => %s\n",$mday,$mon,$year,$hour,$min,$sec,$msg);
           close(LOG);

}
#--------------------------------------------------------------------------------------

sub printUsage {

       print <<EOB

Usage: $name.pl [OPTION]...

        -D, --database     Set database
        -H, --host         Set IP database
        -T, --table        Set table prefix
        -U, --user         Set user database
        -V, --version      Show version
        -Y, --year         Set year of partitioned table
        -h, --help         Show help
        -p, --port         Set port database


EOB

}
#--------------------------------------------------------------------------------------
sub printHelp {

                my $help = <<'HELP';


                Thanks for use DUMP-GET.

                How do?

You need use -T parameter to specify table and -Y to specify year. Other parameters are the string connection.

This agent only work if used trust method.

                API required:

                strict;
                Getopt::Long;
                POSIX;
                File::Basename;      

                Words of God: "Cordeiro de Deus, retirai os pecados do mundo, tende piedade de nos..."







HELP

                system("clear");
                print $help;

}
#--------------------------------------------------------------------------------------
&main