SSH Tunnels Compression speed up PostgreSQL data transport in WAN environment STEP1. create ssh tunnels between remote db and local db
ssh -C -L 8888:127.0.0.1:5432 postgres@remote_ip
STEP2. open another shell window run as below:
postgres@bsmart-server1:~> date +%F%T;psql -h 127.0.0.1 -p 8888 -U postgres BMCV3_Archive -c "copy (select * from tblbmc_bat_activity_info_201105 limit 100000) to stdout" > /home/bsmart01/test1.dump;date +%F%T 2011-06-2720:20:31 2011-06-2720:24:19
cost : 0:03:48
not use the tunnels postgres@bsmart-server1:~> date +%F%T;psql -h remote_ip -p 5432 -U postgres BMCV3_Archive -c "copy (select * from tblbmc_bat_activity_info_201105 limit 100000) to stdout" > /home/bsmart01/test2.dump;date +%F%T 2011-06-2720:36:51 2011-06-2720:47:30
cost : 0:10:39
If you try as above, you will found the tunnel running as foreground, if your terminal quit, the tunnel will close.
another way, you can try tunnel server in background,
Step 1. useradd -m tunnel @ server C & server D
Step 2. create trust in servers Server C: su - tunnel create ssh key : $ ssh-keygen cd ~/.ssh put the id_rsa.pub into Server D : ~/.ssh/authorized_keys
note: ~ -- user "tunnel" home folder
Step 3. create tunnels Server C: ssh -CNf -p 22 -L *:9991:202.9.98.41:5432 tunnel@server_D_IP ssh -CNf -p 22 -L *:9992:202.9.98.42:5432 tunnel@server_D_IP
simple way: server_A --> server_41 ssh -CNf -p 22 -L *:6666:*:5432 tunnel@202.9.98.41
Now, you try it connect server_C_IP:9991 means connect to 202.9.98.41:5432 connect server_C_IP:9992 means connect to 202.9.98.42:5432
About OS & kernel setting: net.ipv4.netfilter.ip_conntrack_tcp_timeout_time_wait=120 net.ipv4.netfilter.ip_conntrack_tcp_timeout_close_wait=60 net.ipv4.netfilter.ip_conntrack_tcp_timeout_fin_wait=120 net.ipv4.tcp_keepalive_intvl=1 net.ipv4.tcp_keepalive_probes=6 net.ipv4.tcp_keepalive_time=1
Reference: man ssh man ssh_config
|