From: Steve Crawford <scrawford@pinpointresearch.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Rob Richardson <RDRichardson@rad-con.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Slow backups over VPN
Date: Tue, 29 Apr 2014 15:38:50 -0700
Message-ID: <536029FA.3@pinpointresearch.com> (raw)
In-Reply-To: <28744.1398809657@sss.pgh.pa.us>
References: <67D108EDFAD3C148A593E6ED7DCB4BBDD844867A@RADCONWIN2K8PDC.radcon.local>
<28744.1398809657@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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. 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@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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, tgl@sss.pgh.pa.us, RDRichardson@rad-con.com
Subject: Re: Slow backups over VPN
In-Reply-To: <536029FA.3@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox