From: Bruno Wolff III <bruno@wolff.to>
To: D Kavan <bitsandbytes88@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: pg_restore
Date: Thu, 11 Aug 2005 15:46:03 -0500
Message-ID: <20050811204603.GA2886@wolff.to> (raw)
In-Reply-To: <BAY102-F2E0B7279722C1B5AF3904D1BD0@phx.gbl>
References: <BAY102-F2E0B7279722C1B5AF3904D1BD0@phx.gbl>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bruno@wolff.to, bitsandbytes88@hotmail.com
Subject: Re: pg_restore
In-Reply-To: <20050811204603.GA2886@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox