Re: [PERFORM] Postgres Dump - Creating index never stops

[Tom Lane]

Hans Braxmeier  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 (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Postgres Dump - Creating index never stops

[Hans Braxmeier]

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