Re: Slow backups over VPN

On 04/29/2014 03:14 PM, Tom Lane wrote:
> Rob Richardson <RDRichardson@rad-con.com> writes:
>> I have a tiny database that I need to take frequent backups of.  The database is on a test server in our office.
WhenI'm at the office, backups take only a few seconds.  But when I am working from home, connecting to the office
througha SonicWall VPN, backups take a few minutes.  Other data transfer operations, like file copies, are reasonably
fast. I've seen this behavior for databases using PostgreSQL8.4, 9.0 and 9.3.  Can you suggest any way that I might get
backupsto be reasonably fast? 
> Does your "tiny" database contain lots of small objects (functions,
> perhaps)?
>
> I'm just guessing here, but I wonder if you aren't getting killed by
> round-trip latency.  pg_dump issues quite a lot of per-object queries,
> so if there are lots of objects, high latency would pose a problem even
> if the one-way throughput is reasonably fast.  It would be interesting
> to find out the round trip time through your VPN as measured by, say,
> ping.
>
> If this theory is correct, there's probably no easy fix :-(.
>
>             regards, tom lane
>
>

It seems like pushing the latency and compression issues into the office
LAN should work well. For instance:

ssh user@machine_in_office "pg_dump -Fc [...standard options...] |
buffer" > my_local_file

The use of "buffer" may not even be necessary or beneficial but we don't
have enough detail to know. This is just one way - plenty of options
exist for achieving the same goal.

Cheers,
Steve