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