User Tools

Site Tools
#!/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 = "";
my $user = 'xxxx';
my $password = 'xxxxxxx';
my $ssh2 = Net::SSH2->new;
    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;
print "dump of $db done\n";
$channel = $ssh2->channel;
$channel->exec("ls -l /data/$db.sqlz");
while (my $line = readline $channel) {
    print $line;
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";


# ./ some_database
connection to established
begin to dump some_database on
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
restore dump to local some_database
restore end