Re: How to increace nightly backup speed

On Nov 28, 2006, at 11:11 AM, Andrus wrote:

> 1. My database size seems to be appox 1 GB and download speed is
> approx 600
> kb/s. Your solution requires 4.5 hours download time
> since 1 GB of data must be downloaded.

If you're running pg_dump on a remote host, you're transferring the
data over the pipe and compressing locally, since the pg wire
protocol is not compressed. The compression time is probably not
causing any slowness unless your local CPU is incredibly slow and
can't keep up with the data streaming in at that low speed.

I don't see how you can improve your download speed without doing
compression at the other end to reduce the number of bits you have to
push through your network.

SSH seems to be a resonable solution to this (run dump + compress on
remote host, then copy data over), but if you rule out anything that
doesn't go over port 5432 then I think you're out of luck...

Well, one thing... is there another host on the remote LAN to which
you can ssh? If so, then use SSH port-forwarding and enable
compression on the ssh connection to that host, then connect to
postgres via the forwarded port to do your dump locally. The data
will be compressed on the wire.