pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddb restore takes to long
5+ messages / 3 participants
[nested] [flat]
* db restore takes to long
@ 2011-05-09 09:35 Viktor Bojović <viktor.bojovic@gmail.com>
2011-05-09 10:19 ` Re: db restore takes to long adrien ducos <aducos@hbs-research.com>
0 siblings, 1 reply; 5+ messages in thread
From: Viktor Bojović @ 2011-05-09 09:35 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: db restore takes to long
2011-05-09 09:35 db restore takes to long Viktor Bojović <viktor.bojovic@gmail.com>
@ 2011-05-09 10:19 ` adrien ducos <aducos@hbs-research.com>
2011-05-09 11:44 ` Re: db restore takes to long Viktor Bojović <viktor.bojovic@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: adrien ducos @ 2011-05-09 10:19 UTC (permalink / raw)
To: Viktor Bojović <viktor.bojovic@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: db restore takes to long
2011-05-09 09:35 db restore takes to long Viktor Bojović <viktor.bojovic@gmail.com>
2011-05-09 10:19 ` Re: db restore takes to long adrien ducos <aducos@hbs-research.com>
@ 2011-05-09 11:44 ` Viktor Bojović <viktor.bojovic@gmail.com>
2011-05-09 12:03 ` Re: db restore takes to long adrien ducos <aducos@hbs-research.com>
2011-05-09 13:11 ` Re: db restore takes to long Tim Lewis <Tim.Lewis@vialect.com>
0 siblings, 2 replies; 5+ messages in thread
From: Viktor Bojović @ 2011-05-09 11:44 UTC (permalink / raw)
To: adrien ducos <aducos@hbs-research.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: db restore takes to long
2011-05-09 09:35 db restore takes to long Viktor Bojović <viktor.bojovic@gmail.com>
2011-05-09 10:19 ` Re: db restore takes to long adrien ducos <aducos@hbs-research.com>
2011-05-09 11:44 ` Re: db restore takes to long Viktor Bojović <viktor.bojovic@gmail.com>
@ 2011-05-09 12:03 ` adrien ducos <aducos@hbs-research.com>
1 sibling, 0 replies; 5+ messages in thread
From: adrien ducos @ 2011-05-09 12:03 UTC (permalink / raw)
To: Viktor Bojović <viktor.bojovic@gmail.com>; +Cc: pgsql-admin
If the indexes are the issue, you can also try to increase the
maintenance_work_mem during the restore and put it back again as normal
afterwards.
That is what the postgres documentation says about it:
maintenance_work_mem (integer)
Specifies the maximum amount of memory to be used in maintenance
operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD
FOREIGN KEY. It defaults to 16 megabytes (16MB). Since only one of
these operations can be executed at a time by a database session,
and an installation normally doesn't have many of them running
concurrently, it's safe to set this value significantly larger than
work_mem. Larger settings might improve performance for vacuuming
and for restoring database dumps.
Note that when autovacuum runs, up to autovacuum_max_workers
<http://www.postgresql.org/docs/8.3/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS;
times this memory may be allocated, so be careful not to set the
default value too high.
Viktor Bojović a écrit :
> 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
> <mailto: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>
> <mailto:aducos@hbs-research.com <mailto:aducos@hbs-research.com>>
> www.hbs-research.com <http://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
--
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: db restore takes to long
2011-05-09 09:35 db restore takes to long Viktor Bojović <viktor.bojovic@gmail.com>
2011-05-09 10:19 ` Re: db restore takes to long adrien ducos <aducos@hbs-research.com>
2011-05-09 11:44 ` Re: db restore takes to long Viktor Bojović <viktor.bojovic@gmail.com>
@ 2011-05-09 13:11 ` Tim Lewis <Tim.Lewis@vialect.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tim Lewis @ 2011-05-09 13:11 UTC (permalink / raw)
To: Viktor Bojović <viktor.bojovic@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-05-09 13:11 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-09 09:35 db restore takes to long Viktor Bojović <viktor.bojovic@gmail.com>
2011-05-09 10:19 ` adrien ducos <aducos@hbs-research.com>
2011-05-09 11:44 ` Viktor Bojović <viktor.bojovic@gmail.com>
2011-05-09 12:03 ` adrien ducos <aducos@hbs-research.com>
2011-05-09 13:11 ` Tim Lewis <Tim.Lewis@vialect.com>
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