pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow backups over VPN
5+ messages / 4 participants
[nested] [flat]
* Slow backups over VPN
@ 2014-04-29 18:13 Rob Richardson <RDRichardson@rad-con.com>
0 siblings, 3 replies; 5+ messages in thread
From: Rob Richardson @ 2014-04-29 18:13 UTC (permalink / raw)
To: pgsql-admin
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?
Thanks very much!
RobR
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow backups over VPN
@ 2014-04-29 18:48 Steve Crawford <scrawford@pinpointresearch.com>
parent: Rob Richardson <RDRichardson@rad-con.com>
2 siblings, 0 replies; 5+ messages in thread
From: Steve Crawford @ 2014-04-29 18:48 UTC (permalink / raw)
To: Rob Richardson <RDRichardson@rad-con.com>; pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow backups over VPN
@ 2014-04-29 21:07 Ray Stell <stellr@vt.edu>
parent: Rob Richardson <RDRichardson@rad-con.com>
2 siblings, 0 replies; 5+ messages in thread
From: Ray Stell @ 2014-04-29 21:07 UTC (permalink / raw)
To: Rob Richardson <RDRichardson@rad-con.com>; pgsql-admin
On 04/29/2014 02:13 PM, Rob Richardson wrote:
>
> Can you suggest any way that I might get backups to be reasonably fast?
>
Probably not enough detail here. You might describe the backup design
so we can pick our lure.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow backups over VPN
@ 2014-04-29 22:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rob Richardson <RDRichardson@rad-con.com>
2 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2014-04-29 22:14 UTC (permalink / raw)
To: Rob Richardson <RDRichardson@rad-con.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow backups over VPN
@ 2014-04-29 22:38 Steve Crawford <scrawford@pinpointresearch.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Steve Crawford @ 2014-04-29 22:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Rob Richardson <RDRichardson@rad-con.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-04-29 22:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-29 18:13 Slow backups over VPN Rob Richardson <RDRichardson@rad-con.com>
2014-04-29 18:48 ` Steve Crawford <scrawford@pinpointresearch.com>
2014-04-29 21:07 ` Ray Stell <stellr@vt.edu>
2014-04-29 22:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-04-29 22:38 ` Steve Crawford <scrawford@pinpointresearch.com>
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