Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 9AFB01337990 for ; Mon, 9 May 2011 11:07:24 -0300 (ADT) 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 23382-07-2 for ; Mon, 9 May 2011 14:07:12 +0000 (UTC) X-Greylist: delayed 00:55:09.329896 by SQLgrey-1.7.6 Received: from cp996.blacksun.ca (69-27-99-6.blacksun.ca [69.27.99.6]) by mail.postgresql.org (Postfix) with ESMTP id 3B2EE1337BD6 for ; Mon, 9 May 2011 11:07:12 -0300 (ADT) Received: from mail-iw0-f174.google.com ([209.85.214.174]) by cp996.blacksun.ca with esmtpsa (TLSv1:RC4-SHA:128) (Exim 4.69) (envelope-from ) id 1QJQFi-0002fq-IM for pgsql-admin@postgresql.org; Mon, 09 May 2011 07:11:58 -0600 Received: by iwn34 with SMTP id 34so4298643iwn.19 for ; Mon, 09 May 2011 06:11:57 -0700 (PDT) MIME-Version: 1.0 Received: by 10.231.179.197 with SMTP id br5mr4641986ibb.146.1304946717555; Mon, 09 May 2011 06:11:57 -0700 (PDT) Received: by 10.231.70.134 with HTTP; Mon, 9 May 2011 06:11:57 -0700 (PDT) In-Reply-To: References: <4DC7BFB2.7080106@hbs-research.com> Date: Mon, 9 May 2011 09:11:57 -0400 Message-ID: Subject: Re: db restore takes to long From: Tim Lewis To: =?UTF-8?Q?Viktor_Bojovi=C4=87?= Cc: "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary=001636b14cb08cfc1c04a2d79584 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - cp996.blacksun.ca X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - vialect.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.917 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RDNS_DYNAMIC=0.982 X-Spam-Level: X-Archive-Number: 201105/77 X-Sequence-Number: 37514 --001636b14cb08cfc1c04a2d79584 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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=C4=87 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 wr= ote: > >> 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=3Dx >> >> 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=3D4 --dbname=3Dyour_db_name >> your_dump_file >> >> Here is if you have a quad core. >> >> Adrien >> >> >> Viktor Bojovi=C4=87 a =C3=A9crit : >> >> 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=C4=87 >>> --------------------------------------- >>> Wherever I go, Murphy goes with me >>> >> >> >> -- >> >> Logo_HBS_mail.jpg >> Adrien DUCOS >> Analyste d=C3=A9veloppeur >> aducos@hbs-research.com >> www.hbs-research.com >> +33 (0)9 70 44 64 10 >> 24 rue de l'Est >> 75020 Paris >> >> >> >> > > > -- > --------------------------------------- > Viktor Bojovi=C4=87 > --------------------------------------- > Wherever I go, Murphy goes with me > --=20 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 --001636b14cb08cfc1c04a2d79584 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable You could try removing/disabling your constraints until after the import/in= dex building.
SET CONSTRAINTS A=
LL DEFERRED


On Mon, May 9, 2011 at 7:44 AM, Viktor B= ojovi=C4=87 <viktor.bojovic@gmail.com> wrote:
Thanx Adrien,
i have tried to skip indexes but constrain= ts rely on them
"there is no unique constraint matching give= n keys for referenced table"
=C2=A0 , so i will try to use pg_restore as you have mentioned below. =C2=A0

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 faste= r with the option --jobs=3Dx

where x is the number of cores you have in your microprocessor. It then div= ides the restore script into different jobs and goes faster using multithre= ading.
example:
pg_restore --username postgres --jobs=3D4 --dbname=3Dyour_db_name your_dump= _file

Here is if you have a quad core.

Adrien


Viktor Bojovi=C4=87 a =C3=A9crit :

Hi,

im trying to restore 250GB database, but indexing takes too long, so restor= e takes more then 12h.
Is it possible to disable indexing while restoring database?

Thanx in advance


--
---------------------------------------
Viktor Bojovi=C4=87
---------------------------------------
Wherever I go, Murphy goes with me


--

Logo_HBS_mail.jpg
=C2=A0Adrien DUCOS
=C2=A0Analyste d=C3=A9veloppeur
=C2=A0aducos@= hbs-research.com <mailto:aducos@hbs-research.com>
=C2=A0www.hbs-re= search.com <http://www.hbs-research.com/>
=C2=A0+33 (0)9 70 44 64 10
=C2=A024 rue de l'Est
=C2=A075020 Paris






--
----------------= -----------------------
Viktor Bojovi=C4=87
-------------------------= --------------
Wherever I go, Murphy goes with me



--
=

Noodle
Connecting=C2=A0People, Content & Capabilities=C2=A0within th= e=C2=A0Enterprise


Toll Free:=C2=A0866-258-6951=C2=A0x 701
T= im.Lewis@vialect.com
http://www.vialect.com

Noodle is a product of Vialect Inc

Follow Noodle on Twitter
<= a href=3D"http://www.twitter.com/noodle_news" style=3D"color:rgb(17, 65, 11= 2)" target=3D"_blank">http://www.twitter.com/noodle_news
 --001636b14cb08cfc1c04a2d79584--