Re: Slow backups over VPN

On 04/29/2014 03:14 PM, Tom Lane wrote:
> Rob Richardson <RDRichardson(at)rad-con(dot)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. When I'm at the office, backups take only a few seconds. But when I am working from home, connecting to the office through a 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 backups to 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(at)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