Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 29AC21337B8A for ; Wed, 8 Dec 2010 23:58:19 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 65854-10 for ; Thu, 9 Dec 2010 03:58:11 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.dc.baikal.ru (smtp.dc.baikal.ru [87.103.128.133]) by mail.postgresql.org (Postfix) with ESMTP id 5C9EE1337B5A for ; Wed, 8 Dec 2010 23:58:11 -0400 (AST) Received: from it319k5.dc-local (it319k5.dc-local [10.0.4.75]) (authenticated bits=0) by smtp.dc.baikal.ru (8.13.8/8.13.8) with ESMTP id oB93w7dC011292; Thu, 9 Dec 2010 11:58:08 +0800 Message-ID: <4D0053D0.4090200@dc.baikal.ru> Date: Thu, 09 Dec 2010 11:58:08 +0800 From: Vlad Arkhipov User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4 MIME-Version: 1.0 To: Tom Lane CC: pgsql-performance@postgresql.org Subject: Re: Slow BLOBs restoring References: <4CFDF22B.7060209@dc.baikal.ru> <4CFF46BD.8070004@dc.baikal.ru> <14439.1291819581@sss.pgh.pa.us> In-Reply-To: <14439.1291819581@sss.pgh.pa.us> Content-Type: text/plain; charset=windows-1251; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201012/133 X-Sequence-Number: 41585 08.12.2010 22:46, Tom Lane writes: > Are you by any chance restoring from an 8.3 or older pg_dump file made > on Windows? If so, it's a known issue. > No, I tried Linux only. > Not without a complete reproducible example ... and not at all if it's > the known problem. The fix for that is to update pg_dump to 8.4 or > later. > I think you can reproduce it. First I created a database full of many BLOBs on Postres 8.4.5. Then I created a dump: pg_dump -F c test > test.backup8 It took about 15 minutes. Then I tried to restore it on Postgres 8. pg_restore -v -d test2 -j 2 test.backup8 It restored in 18 minutes. Then I restored it to Postgres 9.0.1, it took 20 minutes. Then I created a dump there: /usr/pgsql-9.0/bin/pg_dump -F c test > test.backup9 It took 25 minutes. Finally I tried to restore it and got what I've already described: /usr/pgsql-9.0/bin/pg_restore -v -d test2 -j 2 test.backup9 However if I remove the option '-j', the database restores in 45 minutes.