From: Steve Crawford <scrawford@pinpointresearch.com>
To: Rob Richardson <RDRichardson@rad-con.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Slow backups over VPN
Date: Tue, 29 Apr 2014 11:48:56 -0700
Message-ID: <535FF418.90405@pinpointresearch.com> (raw)
In-Reply-To: <67D108EDFAD3C148A593E6ED7DCB4BBDD844867A@RADCONWIN2K8PDC.radcon.local>
References: <67D108EDFAD3C148A593E6ED7DCB4BBDD844867A@RADCONWIN2K8PDC.radcon.local>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 04/29/2014 11:13 AM, Rob Richardson wrote:
>
> 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?
>
Depends on the values of "tiny", "reasonably fast", the type of dump you
are doing, etc. It's not surprising that your (gigabit?) office network
performs better than a VPN connection from home.
I'd try dumping to a file (your few-second dump) and look at the
file-size compared to your reasonably-fast transferring files to see if
it's simply a data-size issue which I suspect though it is, I suppose,
possible that there is a latency or other issue.
If you are doing a plain pg_dump the output is uncompressed text. You
could use one of the compressed formats or do a dump->compress->transfer.
N.B.: If you use a compressed format the compression happens in the
pg_dump client, *not* in the server. So using a compressed format will
not help if your dump client is at home and the server is at the other
end of the VPN. You must compress the data at the office side of the
connection.
Cheers,
Steve
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, RDRichardson@rad-con.com
Subject: Re: Slow backups over VPN
In-Reply-To: <535FF418.90405@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