public inbox for [email protected]
help / color / mirror / Atom feedPostgres Dump - Creating index never stops
2+ messages / 2 participants
[nested] [flat]
* Postgres Dump - Creating index never stops
@ 2017-07-12 19:00 Hans Braxmeier <[email protected]>
2017-07-12 19:41 ` Re: Postgres Dump - Creating index never stops Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Hans Braxmeier @ 2017-07-12 19:00 UTC (permalink / raw)
To: pgsql-performance
Hello Experts,
we have created a postgres dump using this command: pg_dump pixabay | gzip > pixabay.gz
After restarting postgres (even with a new cluster) and creating a new database, postgres is hanging while extracting the dump: gunzip -c pixabay.gz | psql pixabay
The log file shows that the autovacuum task is running (almost) endless...
2017-07-12 18:05:50.822 CEST [19586] hans@pixabay LOG: duration: 1594.319 ms statement: CREATE INDEX photos_download_photo_id ON photos_download USING btree (photo_id);
2017-07-12 18:05:52.340 CEST [19586] hans@pixabay LOG: duration: 1517.955 ms statement: CREATE INDEX photos_download_user_id ON photos_download USING btree (user_id);
2017-07-12 18:05:52.355 CEST [19586] hans@pixabay LOG: duration: 14.954 ms statement: CREATE INDEX photos_editorschoice_created ON photos_editorschoice USING btree (created);
2017-07-12 18:05:52.367 CEST [19586] hans@pixabay LOG: duration: 11.609 ms statement: CREATE INDEX photos_indexphoto_created ON photos_indexphoto USING btree (created);
2017-07-12 20:34:58.943 CEST [19626] ERROR: canceling autovacuum task
2017-07-12 20:34:58.943 CEST [19626] CONTEXT: automatic analyze of table "pixabay.public.photos_photo"
2017-07-12 20:34:59.942 CEST [19586] hans@pixabay LOG: duration: 8947575.013 ms statement: CREATE INDEX photos_photo_approved_by_id ON photos_photo USING btree (approved_by_id);
2017-07-12 20:35:00.471 CEST [19586] hans@pixabay LOG: duration: 528.777 ms statement: CREATE INDEX photos_photo_approved_date ON photos_photo USING btree (approved_date);
What could cause this problem or how can we debug it?
We are running Postgres 9.4 / Ubuntu 16.04
Thanks, Hans
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Postgres Dump - Creating index never stops
2017-07-12 19:00 Postgres Dump - Creating index never stops Hans Braxmeier <[email protected]>
@ 2017-07-12 19:41 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2017-07-12 19:41 UTC (permalink / raw)
To: Hans Braxmeier <[email protected]>; +Cc: pgsql-performance
Hans Braxmeier <[email protected]> writes:
> After restarting postgres (even with a new cluster) and creating a new database, postgres is hanging while extracting the dump: gunzip -c pixabay.gz | psql pixabay
> The log file shows that the autovacuum task is running (almost) endless...
> 2017-07-12 18:05:52.367 CEST [19586] hans@pixabay LOG: duration: 11.609 ms statement: CREATE INDEX photos_indexphoto_created ON photos_indexphoto USING btree (created);
> 2017-07-12 20:34:58.943 CEST [19626] ERROR: canceling autovacuum task
> 2017-07-12 20:34:58.943 CEST [19626] CONTEXT: automatic analyze of table "pixabay.public.photos_photo"
> 2017-07-12 20:34:59.942 CEST [19586] hans@pixabay LOG: duration: 8947575.013 ms statement: CREATE INDEX photos_photo_approved_by_id ON photos_photo USING btree (approved_by_id);
What that looks like is it took the system an unusually long time to
notice that it needed to cancel the autovacuum to avoid a deadlock
with the CREATE INDEX. Was either process consuming a noticeable
amount of CPU during that interval? Do you have deadlock_timeout
set higher than the default 1s?
regards, tom lane
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-07-12 19:41 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-07-12 19:00 Postgres Dump - Creating index never stops Hans Braxmeier <[email protected]>
2017-07-12 19:41 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox