slony_setup

#!/usr/bin/perl
use DBI;
my $cluster;

my $m_host;
my $m_username;
my $m_password;
my $m_conn;

my $s_host;
my $s_username;
my $s_password;
my $s_conn;

my @bases;
my @tables;
my @sequences;

printf "\n STEP 1: Configuring cluster.\n\n";
cluster_cfg();
printf "\n STEP 2: Configuring master.\n\n";
master_cfg();
printf "\n STEP 3: Configuring slave.\n\n";
slave_cfg();
printf "\n STEP 4: Selecting databases.\n\n";
bases_cfg();
printf "\n STEP 5: Adding tables.\n\n";
tables_cfg();
printf "\n STEP 6: Adding sequences.\n\n";
sequences_cfg();
printf "\n STEP 7: Creating DB-structures on slave.\n\n";
dump_to_slave();
printf "\n STEP 8: Generating config.\n\n";
generate_config();
printf "\n STEP 9: Send config to slave config.\n\n";
send_config();
printf "\n STEP 10: Starting daemons.\n\n";
start_daemons();


#Routines
sub cluster_cfg()
{
    do
    {
    printf "Enter cluster name:\n";
        $cluster=<>;chomp($cluster);
    } while ($cluster eq '')

}

sub master_cfg()
{
    printf "Master hostname:\n";
    $m_host=<>;chomp($m_host);

    printf "Username for $m_host:\n";
    $m_username=<>;chomp($m_username);

    printf "Password for $m_username on $m_host:\n";
    $m_password=<>;chomp($m_password);

    $m_conn = DBI->connect("dbi:Pg:dbname=postgres;host=$m_host","$m_username","$m_password",
        {PrintError => 0});

   if (defined($DBI::err) && $DBI::err != 0)
    {
    printf "Error:" . $DBI::errstr . "\nConfigure master again? (Y/n)";
    if (lc(<>) eq 'n') { exit($DBI::err); }
    master_cfg();
    }
}

sub slave_cfg()
{
    printf "Slave hostname:\n";
    $s_host=<>;chomp($s_host);

    printf "Username for $s_host:\n";
    $s_username=<>;chomp($s_username);

    printf "Password for $s_username on $s_host:\n";
    $s_password=<>;chomp($s_password);

    $s_conn = DBI->connect("dbi:Pg:dbname=postgres;host=$s_host","$s_username","$s_password",
        {PrintError => 0});

   if (defined($DBI::err) && $DBI::err != 0)
    {
    printf "Error:" . $DBI::errstr . "\nConfigure slave again? (Y/n)";
    if (lc(<>) eq 'n') { exit($DBI::err); }
    slave_cfg();
    }
    $s_conn->disconnect();
}


sub bases_cfg()
{
    $b=$m_conn->prepare('SELECT datname FROM pg_database;');
    $res=$b->execute();
    if (!defined $res) { printf "FATAL: Can`t get bases list\n";exit; }

    printf "Select base for replication(* for all, blank to continue):\n";

    for($i=0;$str = $b->fetchrow_array();$i++)
    {
      $bases[0][$i]=$str;
      if (!defined($bases[1][$i])) { $bases[1][$i]=''; }
      printf "$i" .$bases[1][$i] . ".$str\n";
    }
    printf ":";

    $ch=<>;chomp($ch);
    if ($ch=~m/\d{1,}/i)
    {
    if ($bases[1][$ch] eq '*') { $bases[1][$ch]=''; }
    else { $bases[1][$ch]='*'; }
    bases_cfg();return;
    }
    if ($ch eq "*")
    {
        printf "Selected all databases from $m_host\n";
        for($i=0;$bases[0][$i];$i++) { $bases[1][$i]='*';}
        bases_cfg();return;
    }


    $b->finish();
    $m_conn->disconnect();

    $b=0;
    for($i=0;$bases[0][$i];$i++)
    {
    if ($bases[1][$i] eq '*')
    {
        `su - postgres -c 'createlang plpgsql $BASES[$b]'`;
        $BASES[$b]=$bases[0][$i];$b++;

    }
    }
}

sub tables_cfg()
{
    $t=0;
    for($b=0;$BASES[$b];$b++)
    {
        $db=$BASES[$b];

        $t_conn = DBI->connect("dbi:Pg:dbname=$db;host=$m_host","$m_username","$m_password",
        {PrintError => 0});

        if (defined($DBI::err) && $DBI::err != 0)
        {
               printf "\nError:" . $DBI::errstr . "\n";
           splice(@BASES,$b,1);
           redo;
        }

        $tabs=$t_conn->prepare("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';");
        $res=$tabs->execute();

        for(;$str = $tabs->fetchrow_array();$t++)
        {
            $tables[0][$t]=$str;
        $tables[1][$t]=$b;

            if (!defined($tables[2][$t])) { $tables[2][$t]=''; }
            printf "$t" .$tables[2][$t] . ".$str\n";
        }

        $tabs->finish();
        $t_conn->disconnect();



    }

}

sub sequences_cfg()
{
    $s=0;
    for($b=0;$BASES[$b];$b++)
    {
        $db=$BASES[$b];

        $s_conn = DBI->connect("dbi:Pg:dbname=$db;host=$m_host","$m_username","$m_password",
        {PrintError => 0});

        if (defined($DBI::err) && $DBI::err != 0)
        {
               printf "\nError:" . $DBI::errstr . "\n";
           splice(@BASES,$b,1);
           redo;

        }

        $seqs=$s_conn->prepare("SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public';");
        $res=$seqs->execute();

        for(;$str = $seqs->fetchrow_array();$s++)
        {
            $sequences[0][$s]=$str;
        $sequences[1][$s]=$b;

            if (!defined($sequences[2][$s])) { $sequences[2][$s]=''; }
            printf "$s" .$sequences[2][$s] . ".$str\n";
        }

        $seqs->finish();
        $s_conn->disconnect();
    }

}

sub dump_to_slave()
{
    printf "Dump selected bases to slave?(Y/n):\n";
    $ch=<>;chomp($ch);
    if (lc($ch) eq 'n') { return; }

    for($b=0;$BASES[$b];$b++)
    {
        $create= `ssh $s_host "su - postgres -c 'createdb $BASES[$b];createlang plpgsql $BASES[$b]'"`;
        printf "Dumping " . $BASES[$b] . " to slave...";
        $dump_out=`su - postgres -c 'pg_dump -s $BASES[$b]'|ssh $s_host "su - postgres -c 'psql $BASES[$b]'"`;
        printf "done\n";
    }
}

sub generate_config()
{
$config='if ($ENV{"SLONYNODES"}) {
require $ENV{"SLONYNODES"};
} else {
$CLUSTER_NAME = \''. $cluster . '\';
$LOGDIR = \'/var/log/slony1\';
$MASTERNODE = 1;
$DEBUGLEVEL = 4;
';
#Nodes cycle
    for($b=0;$BASES[$b];$b++)
    {
    $config.="
add_node(
     node       => " . ($b*2+1) . ",
     host       => '$m_host',
     dbname     => '". $BASES[$b] ."',
     port       => 5432,
     user       => '$m_username',
     password   => '$m_password');

add_node(
     node       => " . ($b*2+2) . ",
     host       => '$s_host',
     dbname     => '".$BASES[$b]."',
     port       => 5432,
     user       => '$s_username',
     password   => '$s_password');
";
    $m_nodes.=($b*2+1) . ' ';
    $s_nodes.=($b*2+2) . ' ';
    }
$config.='}
$SLONY_SETS = {';

    #Sets cycle
for($b=0;$BASES[$b];$b++)
{
$config.="
\"$BASES[$b]\" => {
\"pkeyedtables\" => [\n";

    #Tables cycle
    for($t=0;$tables[0][$t];$t++)
    {
        if ($tables[1][$t] == $b)
        {
            $config.="'$tables[0][$t]'" . ",\n";
        }
    }
$config.='],

"sequences" => [
';
    #Sequences cycle
    for ($s=0;$sequences[0][$s];$s++)
    {
        if ($sequences[1][$s] == $b)
        {
            $config.="'$sequences[0][$s]',\n";
        }

    }
    $config.="]},\n";
    }
    $config.='};
if ($ENV{"SLONYSET"}) {
    require $ENV{"SLONYSET"};
    }
1;';

$c_path='';
chomp($iam=`whoami`);
if ($iam ne 'root')
{
    printf "You`re not root. Saving config to current dir...";
}
else
{
    printf "Write config to /etc/slony1/?(Y/n)";
    $ch=lc(<>);chomp($ch);
    if ($ch eq ''||$ch eq 'y')
    {
        $c_path='/etc/slony1/';
    }
    $m_nodes=~s/\D$//;
    $s_nodes=~s/\D$//;
    `echo 'SLON_TOOLS_START_NODES="$m_nodes"' > /etc/default/slony1;slonik_init_cluster|slonik`;
    printf "Saving config to $c_path...";
}
    open OUT, ">", $c_path . 'slon_tools.conf';
    printf OUT $config;
    close (OUT);
    printf "done\n";

}

sub send_config()
{
    printf "Send config to $c_path on slave?(Y/n)\n";
    $ch=lc(<>);chomp($ch);
    if ($ch eq ''||$ch eq 'y')
    {
    `scp $c_path/slon_tools.conf $s_host:$c_path`;
    `ssh $s_host 'echo SLON_TOOLS_START_NODES=\\"$s_nodes\\" > /etc/default/slony1;slonik_init_cluster|slonik'`;
    }

}

sub start_daemons()
{
    if ($iam ne 'root') {printf "You`re not root. Skipping.\n";return;}

    printf "Start daemons?(Y/n):\n";
    chomp($ch=lc(<>));
    if ($ch eq 'y'|| $ch eq '')
    {
    for($b=0;$BASES[$b];$b++)
    {
        printf "Starting local slon...\n";
        $out=`/etc/init.d/slony1 start`;
        printf "Starting slon on $s_host...\n";
        $out=`ssh $s_host '/etc/init.d/slony1 start'`;
    }
    }
}