wi12.unix7.org

migrate.pl

#!/usr/bin/env perl
 
use strict;
use warnings;
use Net::SSH2;
 
my $db = $ARGV[0];
 
do { print 'you must specificity database\n'; exit; }  unless $db;
 
sub system_comm {
    my $comm = shift;
    return undef unless $comm;
    open HR, "$comm |" or return undef;
    my $out; 
    while (my $str = <HR>) {
        $out .= $str;
    };
    return $out;
}
 
my $host = "pgdbxxxx.xxxxx.com";
my $user = 'xxxx';
my $password = 'xxxxxxx';
 
my $ssh2 = Net::SSH2->new;
 
$ssh2->connect($host)
    or ssh2_die("connect failed");;
$ssh2->auth(username => $user, password => $password)
    or ssh2_die('auth failed');
 
my $channel = $ssh2->channel;
print "connection to $host established\n";
 
print "begin to dump $db on $host\n";
$channel->exec("PGPASSWORD=xxxxxxxxx pg_dump -h localhost -U postgres -Fc -f /data/$db.sqlz $db 2>&1");
while (my $line = readline $channel) {
    print $line;
}
$channel->close;
print "dump of $db done\n";
 
$channel = $ssh2->channel;
$channel->exec("ls -l /data/$db.sqlz");
while (my $line = readline $channel) {
    print $line;
}
$channel->close;
 
print "begin download /data/$db.sqlz\n";
$ssh2->scp_get("/data/$db.sqlz", "/data/$db.sqlz");
print "end download /data/$db.sqlz\n";
 
my $ret;
 
print "drop old local version of $db\n";
$ret = system_comm("PGPASSWORD=xxxxxxxx psql -h localhost -U postgres -c 'drop database if exists $db;'");
print $ret;
 
$ret = system_comm("PGPASSWORD=xxxxxxxx psql -h localhost -U postgres -c 'create database $db;'");
print $ret;
 
print "restore dump to local $db\n";
$ret = system_comm("PGPASSWORD=xxxxxxxxx pg_restore -j 6 -h localhost -U postgres -d $db /data/$db.sqlz");
print "restore end\n";
 
#EOF

out

# ./mgrate.pl some_database
connection to pdgb-xxxx.xxxxx.com established
begin to dump some_database on pdgb-xxxx.xxxxx.com
dump of some_database done
-rw-r--r-- 1 root root 169594959 Dec 27 09:16 /data/some_database.sqlz
begin download /data/some_database.sqlz
end download /data/some_database.sqlz
drop old local version of some_database
DROP DATABASE
CREATE DATABASE
restore dump to local some_database
restore end