Re: pg_dump with 1100 schemas being a bit slow

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.
>
> This seems like a pretty dumb way to go at it.  Why don't you just do
> one -Fc dump for the whole database?  If you ever actually need to
> restore a single schema, there's a pg_restore switch for that.

Thank you for your fast answer.

This is the way I started to do the work, but then I started to have
issues because of the numbers of tables to be soft locked at the same
time increasing each time, I had to push the max_locks_per_transaction
settings up each time. The added benefit of doing a per schema dump is
that I provide it to the users directly, that way they have a full
export of their data. I cannot increase the max_locks_per_transaction
all the time with the increasing number of schemas, no?

What is the problem if I put this settings at a high value (outside of
the memory overhead per connection as far as I understood the doc)?

>> I think that pg_dump, when looking at the objects to dump, also it is
>> limited to a given schema, is scanning the complete database in one
>> those calls:
>
> Yes, it has to examine all database objects in order to trace
> dependencies properly.
>
>> Is there an option: "I know what I am doing, do not look outside of
>> the schema" available which can help in my case?
>
> No.

So it looks like I may need to go a different way. I can setup a WAL
based backup server and dump the content of each schema in an
application specific way (JSON export), this way I can keep the ready
to use backup (at the moment a restore is just a series of import for
each schema) with the slave and it will keep my customers happy with
the JSON dump.

If you know a better solution, I would be pleased to be guided in the
right direction.
loïc

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