User Tools

Site Tools


Differences

This shows you the differences between two versions of the page.

Link to this comparison view

perl:migr-pg-db [2020-02-15 00:57] (current)
Line 1: Line 1:
 +
 +<code perl 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
 +</​code>​
 +
 +===out===
 +
 +<​code>​
 +# ./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
 +</​code>​