Date: Thu, 11 Aug 2005 15:46:03 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: D Kavan <bitsandbytes88@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: pg_restore
Message-ID: <20050811204603.GA2886@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	D Kavan <bitsandbytes88@hotmail.com>, pgsql-admin@postgresql.org
References: <BAY102-F2E0B7279722C1B5AF3904D1BD0@phx.gbl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <BAY102-F2E0B7279722C1B5AF3904D1BD0@phx.gbl>
User-Agent: Mutt/1.5.6i

On Thu, Aug 11, 2005 at 15:29:22 -0400,
  D Kavan <bitsandbytes88@hotmail.com> wrote:
> Hi all,
> 
> We are running postgres 8.03.
> 
> Getting a dump is no problem on our 6 GB database.  However, the restore is 
> a killer, it takes 10 minutes for the dump pg_dump, but over 5 hours for a 
> restore.  Is there a way to 'turn off transactions' on the restore to speed 
> things up?   I've tried different restore commands, but I don't see 
> anything on transactions logging.  What else could speed it up?   oracle 
> and sybase can restore the same db in just 90 minutes.    Does postgres 
> just not know how to take advantage of the resources unless different users 
> are involved?  In other words to speed it up, should I script different 
> users to each restore some of the tables to a database and then after all 
> the tables have restored, restore keys and then indexes?   Thanks for any 
> help.
> 
> ~DjK
> 
> psql dbname < dump_file
> pg_restore -d test_restore_2 -Fc niehs_dump.backup

Some things you can do are to turn off fsync for the restore and not
restore foreign key contsraints and indexes until after the database
has been loaded.

This topic has been discussed before and you might find some more specific
information in the archives.