Re: How to increace nightly backup speed

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: How to increace nightly backup speed
Date: 2006-11-28 02:23:52
Message-ID: 456B9DB8.8050302@cox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/27/06 19:26, Glen Parker wrote:
> Jeff Davis wrote:
>> On Mon, 2006-11-27 at 19:13 +0200, Andrus wrote:
>>> I need to create nightly backups to separate computer over internet
>>> from 8.1 server
>>>
>>> I tried to run pg_dump to make backup using command
>>>
>>> "..\pg_dump\pg_dump.exe" -i -Z9 -b -v -f "C:\061127 mybackup.backup"
>>> -F c -h
>>> 81.50.12.18 -U myuser -p 5431 mydb
>>>
>>> but this takes 4.5 hours to complete.
>>>
>>> How to increace backup speed ?
>
>
> I agree to use online backups. But, I bet the majority of your backup
> time is spent compressing your data (-Z9).
>
> The solution I'm currently using (both with online backups and pg_dump)
> is to copy all the data to an alternate volume uncompressed, then
> compress it (nice gzip -1). This allows the backup itself to happen as
> quickly as possible, and then the compression can run for awhile without
> having a large IO impact on the main data volumes. I worry about how
> long the initial copy/dump takes, but not so much after that.

If you've got excess CPU capacity at night, I wonder if -Z1 or -Z2
would speed the backup since it reduces the amount of data written
to disk.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFa524S9HxQb37XmcRAhbgAKCCE0FMnE8X9zcRHQ5PIj0EWU5+vACgqjmm
KhhZVmjhRvMadZdP7rHNX6o=
=R8fi
-----END PGP SIGNATURE-----

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Uwe C. Schroeder 2006-11-28 03:07:27 Re: Connecting via ssh tunnel
Previous Message Michael Glaesemann 2006-11-28 01:29:00 Re: IS it a good practice to use SERIAL as Primary Key?