Re: pg_dump with 1100 schemas being a bit slow

Hi Josua,

On Wed, Oct 7, 2009 at 6:29 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> On Wed, 2009-10-07 at 12:51 +0200, Loic d'Anterroches wrote:
>> Hello,
>
>> My problem is that the dump increased steadily with the number of
>> schemas (now about 20s from about 12s with 850 schemas) and pg_dump is
>> now ballooning at 120MB of memory usage when running the dump.
>>
>
> And it will continue to. The number of locks that are needing to be
> acquired will consistently increase the amount of time it takes to
> backup the database as you add schemas and objects. This applies to
> whether or not you are running a single dump per schema or a global dump
> with -Fc.
>
> I agree with the other participants in this thread that it makes more
> sense for you to use -Fc but your speed isn't going to change all that
> much overall.

If the speed of a full dump against a series of schema dump is not
going to be dramatically different, time to change to a more long term
efficient way to do the job. Anyway, the benefits of having a WAL
powered slave are interesting as I can use it as failover in another
datacenter in case of problems on the main DB server. It will also add
no load on the main server, which is a good thing.

Thanks all of you for the detailed answers, I feel good using
PostgreSQL for all my production deployments. Being able to go such
good answers in a very short amount of time when nothing is available
on the net is really nice.

loïc

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