Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dVMsW-000199-TS for pgsql-performance@arkaria.postgresql.org; Wed, 12 Jul 2017 19:00:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dVMsW-00057v-BO for pgsql-performance@arkaria.postgresql.org; Wed, 12 Jul 2017 19:00:52 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dVMsV-00057d-KA for pgsql-performance@postgresql.org; Wed, 12 Jul 2017 19:00:51 +0000 Received: from mail-oln040092072108.outbound.protection.outlook.com ([40.92.72.108] helo=EUR03-VE1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dVMsS-0004p6-Ao for pgsql-performance@postgresql.org; Wed, 12 Jul 2017 19:00:50 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outlook.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=bv8g1W/o5QSzLXIJLN4WMKYOv6g4stKsvZtdyoDkEh0=; b=BwS6j50iFQWfUh2e/lZJoi3dS83lyZKZDGLFptNc9qOOmImOr+njntKpJO8mqO+j5GHkHi8kuk2hbmJEM+oaGBPMmFPDneXdMH7DH7ztZ/A6QteLmQ5NMy9Zhww7dBbrVBxDQ7CI8Wx2a0hRW4LZQLcVZXyitHFGdmL31Trqf3ctw/C96bT7VM1B6ipfQurtX7npNkKWjR9/g2B6DrI8si4BjyflECD1c6VzawESFs42U/yLeOAR88B3BmANNq46HOPoZqmMoteLUrYvzKK+HLIa2BkC8cfUMX7Wk6w/zvq7dhlDl+Qj9bfffHe+uYoUKFbsBGqg+odkO0ViRHTHRg== Received: from DB5EUR03FT010.eop-EUR03.prod.protection.outlook.com (10.152.20.56) by DB5EUR03HT215.eop-EUR03.prod.protection.outlook.com (10.152.21.71) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.1240.9; Wed, 12 Jul 2017 19:00:45 +0000 Received: from VI1PR04MB3022.eurprd04.prod.outlook.com (10.152.20.58) by DB5EUR03FT010.mail.protection.outlook.com (10.152.20.96) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256) id 15.1.1240.9 via Frontend Transport; Wed, 12 Jul 2017 19:00:45 +0000 Received: from VI1PR04MB3022.eurprd04.prod.outlook.com ([fe80::6842:bfed:7be4:b308]) by VI1PR04MB3022.eurprd04.prod.outlook.com ([fe80::6842:bfed:7be4:b308%13]) with mapi id 15.01.1240.020; Wed, 12 Jul 2017 19:00:45 +0000 From: Hans Braxmeier To: "pgsql-performance@postgresql.org" Subject: Postgres Dump - Creating index never stops Thread-Topic: Postgres Dump - Creating index never stops Thread-Index: AQHS+y3NIHtxfejzNkCryHcNtZYIdw== Date: Wed, 12 Jul 2017 19:00:45 +0000 Message-ID: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: postgresql.org; dkim=none (message not signed) header.d=none;postgresql.org; dmarc=none action=none header.from=outlook.com; x-incomingtopheadermarker: OriginalChecksum:18D90B2CFFEE50B815DE5068E8184B881AFE545892ACAE8A77DD45151F5D1557;UpperCasedChecksum:96305527B192DA54EA3EAD5D88F88B0FE829264E3D08FEF3FC703CF5262D6A86;SizeAsReceived:7116;Count:43 x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [4F+0DrhVC3GK2Y9EjwQeSYMuOtA0WhCS] x-ms-publictraffictype: Email x-microsoft-exchange-diagnostics: 1;DB5EUR03HT215;7:bF1FGko3pqps7CUbIC4wDFrlV39Riu4rYHhU+P0QeFAJcjGGpMjyJHtSfeNNA6AAvwWD87OFzQZQ8NYUDxE097cavaqq6jVxdGnVgWNEr0e0gfRPc4Vu+h0im7w3vM8MCbyFuWhNAGVtV7JunN4cKf+NzRJ8Xvkt7ItxiQvzZmXoAUs2fte1EkwU0lKow3jH2TzQylIrOh6k/b33ip+WhKO5H1/TKQlAPBz0Wei/Hncr5X+byu7/BY6vXIOxhV1DbLWcOHn41kl9TXkCd87eqOfGHjFp39i+KgqR1O1O7M/sP0SWczEcH9JsxC+PrSFR3oAskHLV0egrv2IcyyZggsxcIoeZ3qOQGvBxBS0d+S2Z6Ol+pQAekix0SQkNn/4rkTyysoUqQvBVZ3IVrTmYoKV8Xx+NfwoPat9/fxFfX/KhWaO8uumNwe5hsxQKLKUG0HuHfIeeQXyBbsoIXCAYvmhRyySY97PA6OMDx6yVe5CLrGFa+VjOzpFOrLmmV1cnwtu7ZdsBgJi2V6W+YJeZzpAAY6YsYejqKvO8AjdUzBY8hSZXm38OQWKTwheDpOPDnzQYkTKBT2igVBzDBQSxl8ztOh/i1SFRAm9Om9lJJ7iRSS1Qpkfq6cnrLN+K/9zzox3gDQW/zRscvAcxpG1+zn1x+CU8uAuma/GOXVSN8fev3PmS+mwrv7eqMEPhzW1CSoEUb9yAPO6ttuC0nSj+wD3oyrwzxclTMaXzYkqvlZbheWNx2EkP3nfhHivm7y1jrv1C6i3YlkYsHHhYmskgWw== x-incomingheadercount: 43 x-eopattributedmessage: 0 x-forefront-antispam-report: EFV:NLI;SFV:NSPM;SFS:(7070007)(98901004);DIR:OUT;SFP:1901;SCL:1;SRVR:DB5EUR03HT215;H:VI1PR04MB3022.eurprd04.prod.outlook.com;FPR:;SPF:None;LANG:en; x-ms-office365-filtering-correlation-id: 496e79ee-0411-450e-a39b-08d4c9584d2e x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:(300000500095)(300135000095)(300000501095)(300135300095)(22001)(300000502095)(300135100095)(300000503095)(300135400095)(201702061074)(5061506573)(5061507331)(1603103135)(2017031320274)(2017031324274)(2017031323274)(2017031322274)(1601125374)(1603101448)(1701031045)(300000504095)(300135200095)(300000505095)(300135600095)(300000506095)(300135500095);SRVR:DB5EUR03HT215; x-ms-traffictypediagnostic: DB5EUR03HT215: x-exchange-antispam-report-test: UriScan:(236129657087228)(148574349560750); x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(100000700101)(100105000095)(100000701101)(100105300095)(100000702101)(100105100095)(444000031);SRVR:DB5EUR03HT215;BCL:0;PCL:0;RULEID:(100000800101)(100110000095)(100000801101)(100110300095)(100000802101)(100110100095)(100000803101)(100110400095)(100000804101)(100110200095)(100000805101)(100110500095);SRVR:DB5EUR03HT215; x-forefront-prvs: 036614DD9C spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_VI1PR04MB3022A9953FCF4847D336A39A99AF0VI1PR04MB3022eurp_" MIME-Version: 1.0 X-OriginatorOrg: outlook.com X-MS-Exchange-CrossTenant-originalarrivaltime: 12 Jul 2017 19:00:45.4178 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Internet X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-Transport-CrossTenantHeadersStamped: DB5EUR03HT215 List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_000_VI1PR04MB3022A9953FCF4847D336A39A99AF0VI1PR04MB3022eurp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 data= base, 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 USI= NG 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 USIN= G 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_editorscho= ice 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 US= ING 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.0= 13 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 m= s 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 --_000_VI1PR04MB3022A9953FCF4847D336A39A99AF0VI1PR04MB3022eurp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hello Experts,


we have created a postgres dump using this command: pg_dump p= ixabay | gzip > pixabay.gz


After restarting postgres (even with a new cluster) and creating a = new database, postgres is hanging while extracting the dump: gun= zip -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 phot= os_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 photo= s_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 ph= otos_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 photo= s_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 O= N 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 pho= tos_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



--_000_VI1PR04MB3022A9953FCF4847D336A39A99AF0VI1PR04MB3022eurp_--