Message-ID: <536029FA.3@pinpointresearch.com>
Date: Tue, 29 Apr 2014 15:38:50 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>,
 Rob Richardson <RDRichardson@rad-con.com>
CC: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Slow backups over VPN
References: 
 <67D108EDFAD3C148A593E6ED7DCB4BBDD844867A@RADCONWIN2K8PDC.radcon.local>
 <28744.1398809657@sss.pgh.pa.us>
In-Reply-To: <28744.1398809657@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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