Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 6E4DF9FA0B7 for ; Wed, 20 Dec 2006 16:36:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 24120-04 for ; Wed, 20 Dec 2006 16:36:15 -0400 (AST) X-Greylist: delayed 00:05:00.887734 by SQLgrey-1.7.4 Received: from mout.perfora.net (mout.perfora.net [217.160.230.40]) by postgresql.org (Postfix) with ESMTP id 073C89F9F46 for ; Wed, 20 Dec 2006 16:36:18 -0400 (AST) Received: from [12.192.126.13] (helo=hawman.anticsoffline.com) by mrelay.perfora.net (node=mrelayus0) with ESMTP (Nemesis), id 0MKoyl-1Gx85q2jNu-0005H1; Wed, 20 Dec 2006 15:31:16 -0500 Subject: Very Very Slow Database Restore From: Joseph McClintock Reply-To: joe.mcclintock@antics.com To: pgsql-admin@postgresql.org Content-Type: text/plain Organization: Antice On-Line Date: Wed, 20 Dec 2006 12:34:20 -0800 Message-Id: <1166646860.3941.4.camel@mcclintock.anticsoffline.com> Mime-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: 7bit X-Provags-ID: perfora.net abuse@perfora.net login:e767c07d8b8e9b65c1eb585a36287271 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50 X-Spam-Level: X-Archive-Number: 200612/281 X-Sequence-Number: 24073 I have inherited the on going management of our company's PostgreSQL 8.0 database. I'm having a very serious problem with the restore process. I've been running periodic pg_dumps from our production database and then restoring the data on our development system. That has been working find until now. Previously when I ran: pg_dump -U username database > db.backup I would get me a 800+ MB file and I could restore with psql -U username -d database < db.backup which took 15 minutes or so. Now our database has grown and the pg_dump give me a 2.1 GB file which is taking 12 hours or more to restore, Yickes! I've tried compressing the dump file: pg_dump -F c -U username database > db.backup and then run pg_restore -U username -d database db.backup but no difference and probably its slower because the restore has to decompress the dump. The database configuration is pretty much out of the box. The only thing that's been changed is the number of allowed db connections which I don't think has much barring on this problem. Any thoughts about this problem would be appreciated. Thanks