From: Tim Lewis <Tim.Lewis@vialect.com>
To: Viktor Bojović <viktor.bojovic@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: db restore takes to long
Date: Mon, 9 May 2011 09:11:57 -0400
Message-ID: <BANLkTinPfazvmXxVkwY3Ste-a4VpvCOSzQ@mail.gmail.com> (raw)
In-Reply-To: <BANLkTik2Ni8NDotj7KgsXvBwEZGEXFTSEA@mail.gmail.com>
References: <BANLkTimSYrirVHhMCpqMkEhxjKBz4Cyo+Q@mail.gmail.com>
<4DC7BFB2.7080106@hbs-research.com>
<BANLkTik2Ni8NDotj7KgsXvBwEZGEXFTSEA@mail.gmail.com>
You could try removing/disabling your constraints until after the
import/index building.
SET CONSTRAINTS ALL DEFERRED
On Mon, May 9, 2011 at 7:44 AM, Viktor Bojović <viktor.bojovic@gmail.com>wrote:
> Thanx Adrien,
> i have tried to skip indexes but constraints rely on them
> "there is no unique constraint matching given keys for referenced table"
> , so i will try to use pg_restore as you have mentioned below.
>
> On Mon, May 9, 2011 at 12:19 PM, adrien ducos <aducos@hbs-research.com>wrote:
>
>> It seams there is no option like that in pg_dump or pg_restore. But You
>> can manualy do it in the dump or with a sed command finding the lines
>> begining by CREATE INDEX and removing them.
>>
>> We also have a big database and since postgres 8.4 pg_restore is much
>> faster with the option --jobs=x
>>
>> where x is the number of cores you have in your microprocessor. It then
>> divides the restore script into different jobs and goes faster using
>> multithreading.
>> example:
>> pg_restore --username postgres --jobs=4 --dbname=your_db_name
>> your_dump_file
>>
>> Here is if you have a quad core.
>>
>> Adrien
>>
>>
>> Viktor Bojović a écrit :
>>
>> Hi,
>>>
>>> im trying to restore 250GB database, but indexing takes too long, so
>>> restore takes more then 12h.
>>> Is it possible to disable indexing while restoring database?
>>>
>>> Thanx in advance
>>>
>>>
>>> --
>>> ---------------------------------------
>>> Viktor Bojović
>>> ---------------------------------------
>>> Wherever I go, Murphy goes with me
>>>
>>
>>
>> --
>>
>> Logo_HBS_mail.jpg
>> Adrien DUCOS
>> Analyste développeur
>> aducos@hbs-research.com <mailto:aducos@hbs-research.com>
>> www.hbs-research.com <http://www.hbs-research.com/;
>> +33 (0)9 70 44 64 10
>> 24 rue de l'Est
>> 75020 Paris
>>
>>
>>
>>
>
>
> --
> ---------------------------------------
> Viktor Bojović
> ---------------------------------------
> Wherever I go, Murphy goes with me
>
--
Noodle
Connecting People, Content & Capabilities within the Enterprise
Toll Free: 866-258-6951 x 701
Tim.Lewis@vialect.com
http://www.vialect.com
Noodle is a product of Vialect Inc
Follow Noodle on Twitter
http://www.twitter.com/noodle_news
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: Tim.Lewis@vialect.com, viktor.bojovic@gmail.com
Subject: Re: db restore takes to long
In-Reply-To: <BANLkTinPfazvmXxVkwY3Ste-a4VpvCOSzQ@mail.gmail.com>
* 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