Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jTK3V-0001iI-PT for pgsql-admin@arkaria.postgresql.org; Tue, 28 Apr 2020 06:49:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jTK3U-0007rx-8x for pgsql-admin@arkaria.postgresql.org; Tue, 28 Apr 2020 06:49:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jTK3T-0007rp-V8 for pgsql-admin@lists.postgresql.org; Tue, 28 Apr 2020 06:49:20 +0000 Received: from mail-vi1eur05on2105.outbound.protection.outlook.com ([40.107.21.105] helo=EUR05-VI1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jTK3Q-0005rL-9p for pgsql-admin@postgresql.org; Tue, 28 Apr 2020 06:49:19 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=jMkXcyDJaOE3bkt4r0hs9cfgwkJQ3QaXq9ewDw4+rDwj3UjPTxskZ7Bj9WJC8YRiNMsV1JDXAr1ym9Wnyq8Z9uNlPVVLc5l48GwIwqEadTNtlddmNPxiJI+RpNuBDFUGv0W4sLV9AbKFEP40d8ckgGaoU2bhS6ye0sqxNG4ugiIrombX2uJ6bQIZDlZYHuHYq1oO8Pn3zWQprwfud8oXhsb+HB1cTSgRINGrtCcPDZ9EhYe5NFiIcM/FdSJnN2EWKmanQo9n0is6z+iL9hezsmThNms9RCC0D6/UFt7C+UKBy9a7RBtO5CML6Y1KdYPZkWfkF3fFIn5tI89TgP3abg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=leh82rx4ydpaMfbmPnXTs9M78bKr48TVO5PgSk7Oz7c=; b=Te92ahuER9/9Rlcb+M3jq/8YG3BtXgQIotvyKAP5zQLEGBJn2ukeGGUfXWJBuo1hjoE+ROw+N+23IOxyIxjEP4otxtRTaM4aK5kDda0esiumQ4N7sGCJ90snO9O0+x0YNs//7idmB3YTlu9KLE6S2mH2m5/nkG1Oa32I/9RK4gm3jQWyo1jNIY1mk/8TUYL+1EUReH22kxdF80WVr5RGzZq0DVbU0sdee3RKt00IiY7HvC8W5SY837uYoBc2YWkRXqA/nPssO6PCeJ9f838niMPbdjP7MVwix6JVVYJwW/SSWuz80iN8kOjMRCVpoFY+9K51+4pQ5aJDekNwmtkpFg== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=accelaero.com; dmarc=pass action=none header.from=accelaero.com; dkim=pass header.d=accelaero.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=accelaero.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=leh82rx4ydpaMfbmPnXTs9M78bKr48TVO5PgSk7Oz7c=; b=bwRNsCnBpjhgdbM3FIEUrsA2jfyKRJAiZq1ycmVO7fsqjAWYfQ/fggnHSuDfCCmKt8BUeRSNQ899Z5tYXTrs2IbYnHkh+tKMjsDx/zGbaw5Ljh2xagKHxIxy77VKrF+uqA5zxHwTpxixiPBOW1euqfwxW/BN+NoJUuZR4BpiSaQ= Received: from AM0P191MB0708.EURP191.PROD.OUTLOOK.COM (2603:10a6:20b:153::8) by AM0P191MB0433.EURP191.PROD.OUTLOOK.COM (2603:10a6:208:4d::21) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.2937.22; Tue, 28 Apr 2020 06:49:12 +0000 Received: from AM0P191MB0708.EURP191.PROD.OUTLOOK.COM ([fe80::543:76b5:fb40:eda9]) by AM0P191MB0708.EURP191.PROD.OUTLOOK.COM ([fe80::543:76b5:fb40:eda9%2]) with mapi id 15.20.2937.023; Tue, 28 Apr 2020 06:49:12 +0000 From: Ashok kumar Mani To: Rui DeSousa CC: "pgsql-admin@postgresql.org" , "pgsql-admin@lists.postgresql.org" Subject: RE: PostgreSQL DB checkpoint error! Thread-Topic: PostgreSQL DB checkpoint error! Thread-Index: AdYdI9nqBdKKuZSNRFSFEjQBqx6VDQABCdKAAAA2Z8A= Date: Tue, 28 Apr 2020 06:49:11 +0000 Message-ID: References: <913378B8-0A7F-44A6-BF34-32BF58FC0876@crazybean.net> In-Reply-To: <913378B8-0A7F-44A6-BF34-32BF58FC0876@crazybean.net> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: MSIP_Label_916ea167-67b1-4080-9c86-674eefeb4f23_Enabled=True; MSIP_Label_916ea167-67b1-4080-9c86-674eefeb4f23_SiteId=7bbb8a4d-7f1b-4a58-9b3a-c250fb8023e3; MSIP_Label_916ea167-67b1-4080-9c86-674eefeb4f23_Owner=amani@accelaero.com; MSIP_Label_916ea167-67b1-4080-9c86-674eefeb4f23_SetDate=2020-04-28T06:49:25.4498523Z; MSIP_Label_916ea167-67b1-4080-9c86-674eefeb4f23_Name=External - General; MSIP_Label_916ea167-67b1-4080-9c86-674eefeb4f23_Application=Microsoft Azure Information Protection; MSIP_Label_916ea167-67b1-4080-9c86-674eefeb4f23_ActionId=f6ec3140-2bb1-4562-b9d8-a0ca82a92ea8; MSIP_Label_916ea167-67b1-4080-9c86-674eefeb4f23_Extended_MSFT_Method=Manual authentication-results: crazybean.net; dkim=none (message not signed) header.d=none;crazybean.net; dmarc=none action=none header.from=accelaero.com; x-originating-ip: [2001:8f8:1423:5930:bc14:19f:ca38:129e] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: c82b60ce-e0f8-4329-7d62-08d7eb404262 x-ms-traffictypediagnostic: AM0P191MB0433: x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:7691; x-forefront-prvs: 0387D64A71 x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AM0P191MB0708.EURP191.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFTY:;SFS:(4636009)(136003)(346002)(366004)(376002)(396003)(39850400004)(3480700007)(8676002)(81156014)(478600001)(6916009)(4326008)(55016002)(316002)(9686003)(86362001)(8936002)(71200400001)(52536014)(2906002)(66946007)(53546011)(6506007)(33656002)(54906003)(64756008)(7696005)(186003)(66446008)(76116006)(66556008)(66476007)(5660300002);DIR:OUT;SFP:1102; x-ms-exchange-senderadcheck: 1 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: f42AeGpYS1qBExWjHcrsvYSTXKUFlTbeSc9QzxyfRygDMYOPUDFoZ0OcEncqqwPZAQ/2QPm+bD9zIlSjieFEQx1/yZeVMWku8/ZcHWSGDdlljbfiaiRkzSHHlAedo2Dq9e0MkAxnKtvoUBdm1hf0pFeil7+lFuyEsid1z5yOziyuffWQzCsMgNZ7uFwbZ9C+J91lsXjRpQw+UOAbrWESen5odlgdWaH8UMZapFZlri0cMcFhF8czFhhxlzr9Qf2lGn5OzABwa3M9F4HtqMHgD/fSQhWcblm+xrQreX0LoFahDp2C+sA0W5X5Mjjl3atw1EqpKU1XiDlZXPxFnPT9cGnFW1YcOpIxW/51SoGCd9N2mKz7TeBiFi02vcjucwV3oS9QxVikQSJRGVGfSUoDvu/pnDuxCvXIcuf5g8AfoGTcwhZqGlGYALxwUtfZnvvO x-ms-exchange-antispam-messagedata: Bb/yw4fUqFNa8Fb5Dbyfdfab0ADY7yvLJEj0xhjaSN9fCGxyo6RZocKjVrqLZct9C0j8xCkVMzabfLSxCPdeXkj9ibBQnmEroLHZ+rn+fhZnnoFb25xbrwbcrrZ1kcJ+OxsqwZXJxUzbgkUa2fzi6zOpfoy4yXsCeDsHcFIRGXc+2A5/zZ0ICA8AsjVMlUofSMDR/ArF259mJoBByIX1umHWrwGvtPu7GRkAFYhebAMaAMHkjrXqh7sOQPrIbudHU93ohDpVDm13sxf4l/xCy0t2vCDSXAOktyxpq5xMIKQEt6BCiPQ5GPMYsVl2ADC8oKr70IG2XP8FASUoiePaO4QxSonDBewr7zSOxP/sgoF8PpCvggz7fbKMYqst3wHg8tp55bFmhD2xYxszGwHmsSuRsUa0YLlCeHUXpG5uyh71BhjbIdAyvtMroX9yWHMMFjo+J6XnuxCArb97VppnHP1UGswD8vq0tEMVwK7mQY9VNEfEWmFYoalaoOSARs47oCC0zs14nNHbOZ7NdsuwIZGwuo1m0KLe9yahHjxuz6gY6Sb/1xHZIurz3fTwfPtjU0AqbnYYqmOk2xHLAVvBfZkd8KtOCKBsR3LgVyRgQ3KijYbI03jo157Z0Bz3OEQJcbtSS7mLoOtPjIr8yClWbQ7dotS2GFIxP6t7HnpaG63MdCDQTWVx1cVlTWeQK0JnpbZlPUkQer3CBJQpquySyVlf1GKReZo3/KXIvvY5C7gVRsl7M3PrOL74e3PwBvjL0Oj8rJnRJFd2uEgBB6i9iHAfZ+GuQUgzp4WamSu4j13OWD/m258ttdOZo9kyRJB6gi9UXVoIyLt8hvcPC4OLEkv1q1N/5X+IIxBS+7MM9ZQ= x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_AM0P191MB07086CDC55A348BF86934E5BAEAC0AM0P191MB0708EURP_" MIME-Version: 1.0 X-OriginatorOrg: accelaero.com X-MS-Exchange-CrossTenant-Network-Message-Id: c82b60ce-e0f8-4329-7d62-08d7eb404262 X-MS-Exchange-CrossTenant-originalarrivaltime: 28 Apr 2020 06:49:11.8975 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 7bbb8a4d-7f1b-4a58-9b3a-c250fb8023e3 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: s+TyQ4oI+pfDxWoQc9P9AlbVU1azv6WdLBjXJlXUrF16G4iD88M9XSeVNxPpN/4FZfhYNHqHlwEC+Sc6McH0zQ== X-MS-Exchange-Transport-CrossTenantHeadersStamped: AM0P191MB0433 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_AM0P191MB07086CDC55A348BF86934E5BAEAC0AM0P191MB0708EURP_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Classification: External Hi Rui DeSousa Do I need to restart the postgresql service for log_checkpoints to make it = active . I would like to enable this parameter temporarily and check it? postgres=3D# show log_checkpoints; log_checkpoints ----------------- off (1 row) postgres=3D# show shared_buffers; shared_buffers ---------------- 60GB (1 row) postgres=3D# show max_wal_size; max_wal_size -------------- 2GB (1 row) postgres=3D# show wal_buffers; wal_buffers ------------- 16MB (1 row) postgres=3D# Best Wishes, Ashokkumar Mani Database Architect\DBA OCP | AWSCSA | M103 From: Rui DeSousa Sent: Tuesday, April 28, 2020 10:41 AM To: Ashok kumar Mani Cc: pgsql-admin@postgresql.org; pgsql-admin@lists.postgresql.org Subject: Re: PostgreSQL DB checkpoint error! Information Security Email Alert: This email is from an EXTERNAL source. Pl= ease use caution when clicking on links or opening attachments from an unkn= own or suspicious sender. To report a suspected phishing email, Send us an = Email on Servicedesk@accelaero.com On Apr 28, 2020, at 2:13 AM, Ashok kumar Mani > wrote: Database is getting hung every day at 5AM. I found the below message from l= og file , Could you please help me , should I need to increase the max_wal_= size value ? Most likely. I would recommend enabling checkpoint logging to get a better= understanding how often checkpoint are occurring, duration, and size in re= lation to buffer pool. Check for 'checkpoint starting' and 'checkpoint com= plete' in the PostgreSQL log files after enabling it. log_checkpoints =3D on What is shared_buffers, max_wal_size, and wal_buffers set to? Why it say "incomplete startup packet" in the log. This should be benign. --_000_AM0P191MB07086CDC55A348BF86934E5BAEAC0AM0P191MB0708EURP_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

= Classification: External

 

Hi Rui DeSousa

Do I need to restart the postgresql servic= e for log_checkpoints to make it active . I would like to enable this param= eter temporarily and check it?

 

 

postgres=3D# show log_checkpoints;

log_checkpoints

-----------------

off

(1 row)

 

postgres=3D# show shared_buffers;

shared_buffers

----------------

60GB

(1 row)

 

postgres=3D# show max_wal_size;=

max_wal_size

--------------

2GB

(1 row)

 

postgres=3D# show wal_buffers;<= /span>

wal_buffers

-------------

16MB

(1 row)

 

postgres=3D#

 

 

Best Wishes,

 

Ashokkumar Mani

Database Architect\DBA=

OCP | AWSCSA | M103

 

From: Rui DeSousa <rui@crazybean.= net>
Sent: Tuesday, April 28, 2020 10:41 AM
To: Ashok kumar Mani <amani@accelaero.com>
Cc: pgsql-admin@postgresql.org; pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL DB checkpoint error!

 

Information Security Email Alert: This email is from an EXTERNAL source. Please use caution when clicking on lin= ks or opening attachments from an unknown or suspicious sender. To report a= suspected phishing email, Send us an Email on Servicedesk@accelaero.com<= /span>

 

 

 

On Apr 28, 2020, at 2:13 AM, Ashok kumar Mani <amani@accelaero.com> wrote:

 

Database is getting hung every day at 5AM. I found the b= elow message from log file , Could you please help me , should I need to in= crease the max_wal_size value ?

 

Most likely.  I would recommend enabling checkp= oint logging to get a better understanding how often checkpoint are occurri= ng, duration, and size in relation to buffer pool.  Check for 'ch= eckpoint starting’ and 'checkpoint complete’  in the PostgreSQL log files after enabling it.

 

log_checkpoints =3D on

 

What is shared_buffers, max_wal_size, and wal_buffer= s set to?

 

Why it say “incomplete startup packet” in th= e log.

 

This should be benign.

--_000_AM0P191MB07086CDC55A348BF86934E5BAEAC0AM0P191MB0708EURP_--