Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a24gs-0006cP-1B for pgsql-admin@arkaria.postgresql.org; Thu, 26 Nov 2015 22:06:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a24gr-0002J6-77 for pgsql-admin@arkaria.postgresql.org; Thu, 26 Nov 2015 22:06:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a1hMk-0000Gi-4N for pgsql-admin@postgresql.org; Wed, 25 Nov 2015 21:12:38 +0000 Received: from mail-bl2on0096.outbound.protection.outlook.com ([65.55.169.96] helo=na01-bl2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a1hMg-0001hW-60 for pgsql-admin@postgresql.org; Wed, 25 Nov 2015 21:12:37 +0000 Received: from CY1PR0501MB1401.namprd05.prod.outlook.com (10.160.148.143) by CY1PR0501MB1402.namprd05.prod.outlook.com (10.160.148.144) with Microsoft SMTP Server (TLS) id 15.1.331.20; Wed, 25 Nov 2015 21:12:30 +0000 Received: from CY1PR0501MB1401.namprd05.prod.outlook.com ([10.160.148.143]) by CY1PR0501MB1401.namprd05.prod.outlook.com ([10.160.148.143]) with mapi id 15.01.0331.023; Wed, 25 Nov 2015 21:12:30 +0000 From: "Williams, Alex" To: Yaser Raja , Albe Laurenz CC: "Williams, Alex" , Scott Ribe , "pgsql-admin@postgresql.org" Subject: Re: Question on Hot Standby in PostgreSQL Thread-Topic: [ADMIN] Question on Hot Standby in PostgreSQL Thread-Index: AQHRI8y8zBjK47Xr6EiOzEJaz9mcjZ6qEjSAgAAMXQCAAPBIAIABYurAgADOVQD//7B3AA== Date: Wed, 25 Nov 2015 21:12:30 +0000 Message-ID: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=awilliams@teamdrg.com; x-ms-exchange-messagesentrepresentingtype: 1 x-originating-ip: [209.64.158.208] x-microsoft-exchange-diagnostics: 1; CY1PR0501MB1402; 5:HQ3uzS9jTPB9/6iwwKNnkXkqhFj2HdtY+KSLZLy7H4Mh9QWIrm0s4NkmQgcIni7duDLFHy2iuAp3bSynAFVj41aVIBSCwNk+z4mmlpWs1k6mYcCMMbsUg79zyREd/j5L9RceFvQOo8iZAZy0jykqrw==; 24:qmFbAK0fPF3jbEuurVFdMaKLKLVrwWeNdfqylkDvUE7BlkHp3NnB7FEVFH6f48XRIBu9UiYaRVwE1a3ltcg7FDcCfEbi6UTPXeRpTFchk8M=; 20:wIMkI5744wiEkwsO4l1xWhpNLMa4wDSLlLZAHn2jHYVNNKvl/e1dXwcg15GfLgWnneFw5rnTYOqwy7WlqLHaRzh4NgURJjbFFwtWJngWURsHJm1IQIYyuoht6gy65V4C58Fq2ZpIzpUZ2Hi8t+tM6HtLtRBhB4H36cj393wXUBc= x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY1PR0501MB1402; x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:(173375146687380); x-exchange-antispam-report-cfa-test: BCL:0; PCL:0; RULEID:(601004)(2401047)(520078)(5005006)(8121501046)(10201501046)(3002001); SRVR:CY1PR0501MB1402; BCL:0; PCL:0; RULEID:; SRVR:CY1PR0501MB1402; x-forefront-prvs: 0771670921 x-forefront-antispam-report: SFV:NSPM; SFS:(10009020)(189002)(377454003)(24454002)(199003)(102836003)(16236675004)(92566002)(5002640100001)(101416001)(36756003)(6116002)(122556002)(77096005)(2950100001)(3846002)(86362001)(586003)(5001920100001)(97736004)(54356999)(189998001)(1220700001)(50986999)(66066001)(5001960100002)(10400500002)(81156007)(5001770100001)(2900100001)(19580405001)(5004730100002)(19580395003)(105586002)(87936001)(40100003)(5008740100001)(106116001)(106356001)(99286002)(11100500001)(1096002); DIR:OUT; SFP:1101; SCL:1; SRVR:CY1PR0501MB1402; H:CY1PR0501MB1401.namprd05.prod.outlook.com; FPR:; SPF:None; PTR:InfoNoRecords; MX:1; A:1; LANG:en; received-spf: None (protection.outlook.com: teamdrg.com does not designate permitted sender hosts) Content-Type: multipart/alternative; boundary="_000_D27B8CCE111FF9awilliamsdresourcesgroupcom_" MIME-Version: 1.0 X-OriginatorOrg: teamdrg.com X-MS-Exchange-CrossTenant-originalarrivaltime: 25 Nov 2015 21:12:30.3735 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 5d6495b1-5cd4-4a4f-a6dd-1f5f3bf58831 X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR0501MB1402 X-MS-Exchange-CrossPremises-AuthAs: Internal X-MS-Exchange-CrossPremises-AuthMechanism: 04 X-MS-Exchange-CrossPremises-AuthSource: CY1PR0501MB1401.namprd05.prod.outlook.com X-MS-Exchange-CrossPremises-SCL: 1 X-MS-Exchange-CrossPremises-messagesource: StoreDriver X-MS-Exchange-CrossPremises-BCC: X-MS-Exchange-CrossPremises-originalclientipaddress: 209.64.158.208 X-MS-Exchange-CrossPremises-avstamp-service: 1.0 X-MS-Exchange-CrossPremises-antispam-scancontext: DIR:Originating; SFV:NSPM; SKIP:0; X-MS-Exchange-CrossPremises-journaled-to-recipients: local-dresources365@journalloma.liveoffice.com X-MS-Exchange-CrossPremises-processed-by-journaling: Journal Agent X-OrganizationHeadersPreserved: CY1PR0501MB1402.namprd05.prod.outlook.com X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_000_D27B8CCE111FF9awilliamsdresourcesgroupcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Laurenz and Raja, Thank you both for your feedback and insight, I really appreciate it. I thi= nk I'll leave it with -1 (since we may add columns in the future and other = possible DDL changes,) so we'll schedule downtime on the standby at least 1= x a week for cleaning up the master. Again, I thank everyone for your help, this really helped my situation. Happy Thanksgiving and Best Regards, Alex P.S. If anyone is ever in NJ, let me know, lunch is on me :) From: Yaser Raja > Date: Wednesday, November 25, 2015 3:57 PM To: Albe Laurenz > Cc: "Williams, Alex" >,= Scott Ribe >, "pgsql-admin@postgresql.org" > Subject: Re: [ADMIN] Question on Hot Standby in PostgreSQL On Wed, Nov 25, 2015 at 3:43 AM, Albe Laurenz > wrote: Alex Williams wrote: > Concerning these two params below, when using hot_standby_feedback, what= would you set them if you > had the situation where you had queries constantly running on the standby= , some quick and some long, > practically all day/night, everyday? > > max_standby_archive_delay (integer) > max_standby_streaming_delay (integer) > Should I still keep them as -1 (infinite)? I'd say set them both to 0. If you have hot_standby_feedback=3Don, the primary will not remove any rows= still needed on the standby, so there should never be a need to delay WAL applica= tion. hot_standby_feedback will significantly reduce the conflicts but it will no= t totally eliminate them. For example if a new column is added to a table, = any query using this table on slave will be cancelled to apply this change. IF query cancellation is totally not acceptable then you will have to set t= hese delays to -1. The drawback is that you may get considerable bloat on the primary if many DELETE/UPDATE operation are going on and queries on the standby take a long= time. Yours, Laurenz Albe --_000_D27B8CCE111FF9awilliamsdresourcesgroupcom_ Content-Type: text/html; charset="us-ascii" Content-ID: <8AEC68EE33E0DC4FBB2FFB75E105757A@namprd05.prod.outlook.com> Content-Transfer-Encoding: quoted-printable
Hi Laurenz and Raja,

Thank you both for your feedback and insight, I really appreciate it. = I think I'll leave it with -1 (since we may add columns in the future and o= ther possible DDL changes,) so we'll schedule downtime on the standby at le= ast 1x a week for cleaning up the master.

Again, I thank everyone for your help, this really helped my situation= .

Happy Thanksgiving and Best Regards,

Alex

P.S. If anyone is ever in NJ, let me know, lunch is on me :)

From: Yaser Raja <yrraja@gmail.com>
Date: Wednesday, November 25, 2015 = 3:57 PM
To: Albe Laurenz <laurenz.albe@wien.gv.at>
Cc: "Williams, Alex" <= awilliams@teamdrg.com>, Sco= tt Ribe <scott_ribe@eleva= ted-dev.com>, "pg= sql-admin@postgresql.org" <pgsql-admin@postgresql.o= rg>
Subject: Re: [ADMIN] Question on Ho= t Standby in PostgreSQL

On Wed, Nov 25, 2015 at 3:43 AM, Albe Laurenz <laurenz.al= be@wien.gv.at> wrote:
Alex Williams wrote:
> Concerning these two params below, when using hot_standby_feedback,&nb= sp; what would you set them if you
> had the situation where you had queries constantly running on the stan= dby, some quick and some long,
> practically all day/night, everyday?
>
> max_standby_archive_delay (integer)
> max_standby_streaming_delay (integer)

> Should I still keep them as -1 (infinite)?

I'd say set them both to 0.

If you have hot_standby_feedback=3Don, the primary will not remove any rows= still
needed on the standby, so there should never be a need to delay WAL applica= tion.

hot_standby_feedback will significantly reduce the conflicts but it wi= ll not totally eliminate them. For example if a new column is added to a ta= ble, any query using this table on slave will be cancelled to apply this ch= ange.

IF query cancellation is totally not acceptable then you will have to = set these delays to -1.

 

The drawback is that you may get considerable bloat on the primary if many<= br> DELETE/UPDATE operation are going on and queries on the standby take a long= time.

Yours,
Laurenz Albe

 --_000_D27B8CCE111FF9awilliamsdresourcesgroupcom_--