Re: pg_dump with 1100 schemas being a bit slow

On Wed, Oct 7, 2009 at 5:54 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> In response to "Loic d'Anterroches" <diaeresis(at)gmail(dot)com>:
>
>> On Wed, Oct 7, 2009 at 4:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> > "Loic d'Anterroches" <diaeresis(at)gmail(dot)com> writes:
>> >> Each night I am running:
>> >> pg_dump --blobs --schema=%s --no-acl -U postgres indefero | gzip >
>> >> /path/to/backups/%s/%s-%s.sql.gz
>> >> this for each installation, so 1100 times. Substitution strings are to
>> >> timestamp and get the right schema.
>
> Have you tested the speed without the gzip?

This is the first thing I did but in that case I was not able to get
any significant improvement. The data to gzip is very small "per
schema" so this is not the bottleneck.

> We found that compressing the dump takes considerably longer than pg_dump
> does, but pg_dump can't release its locks until gzip has completely
> processed all of the data, because of the pipe.

Good tip, I keep that in mind for the future!

Thanks,
loïc

--
Loïc d'Anterroches - Céondo Ltd - http://www.ceondo.com