SSH Tunnels speed up PG data transport in WAN

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