Re: pg_dump and thousands of schemas

On Fri, May 25, 2012 at 8:18 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Fri, May 25, 2012 at 10:41:23AM -0400, Tom Lane wrote:
>> "Hugo <Nabble>" <hugo(dot)tech(at)gmail(dot)com> writes:
>> > If anyone has more suggestions, I would like to hear them. Thank you!
>>
>> Provide a test case?
>>
>> We recently fixed a couple of O(N^2) loops in pg_dump, but those covered
>> extremely specific cases that might or might not have anything to do
>> with what you're seeing.  The complainant was extremely helpful about
>> tracking down the problems:
>> http://archives.postgresql.org/pgsql-general/2012-03/msg00957.php
>> http://archives.postgresql.org/pgsql-committers/2012-03/msg00225.php
>> http://archives.postgresql.org/pgsql-committers/2012-03/msg00230.php
>
> Yes, please help us improve this!  At this point pg_upgrade is limited
> by the time to dump/restore the database schema, but I can't get users
> to give me any way to debug the speed problems.

For dumping one small schema from a large database, look at the time
progression of this:

dropdb foo; createdb foo;

for f in `seq 0 10000 1000000`; do
perl -le 'print "create schema foo$_; create table foo$_.foo (k
integer, v integer);"
foreach $ARGV[0]..$ARGV[0]+9999' $f | psql -d foo > /dev/null ;
time pg_dump foo -Fc -n foo1 | wc -c;
done >& dump_one_schema_timing

To show the overall dump speed problem, drop the "-n foo1", and change
the step size from 10000/9999 down to 1000/999

Cheers,

Jeff