Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a0xLl-0004N0-LF for pgsql-admin@arkaria.postgresql.org; Mon, 23 Nov 2015 20:04:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a0xLl-0008JD-7z for pgsql-admin@arkaria.postgresql.org; Mon, 23 Nov 2015 20:04:33 +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 1Zzrhq-0000PC-KB for pgsql-admin@postgresql.org; Fri, 20 Nov 2015 19:50:50 +0000 Received: from mail-by2on0092.outbound.protection.outlook.com ([207.46.100.92] helo=na01-by2-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 1Zzrhm-0005Wn-6D for pgsql-admin@postgresql.org; Fri, 20 Nov 2015 19:50:49 +0000 Received: from CY1PR0501MB1401.namprd05.prod.outlook.com (10.160.148.143) by CY1PR0501MB1401.namprd05.prod.outlook.com (10.160.148.143) with Microsoft SMTP Server (TLS) id 15.1.325.17; Fri, 20 Nov 2015 19:50:41 +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.0325.019; Fri, 20 Nov 2015 19:50:41 +0000 From: "Williams, Alex" To: "pgsql-admin@postgresql.org" Subject: Re: Question on Hot Standby in PostgreSQL Thread-Topic: Question on Hot Standby in PostgreSQL Thread-Index: AQHRI8y8zBjK47Xr6EiOzEJaz9mcjQ== Date: Fri, 20 Nov 2015 19:50:41 +0000 Message-ID: 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; CY1PR0501MB1401; 5:fKKB56prhLrV0weF5j7TGGR9pa5HFprnaqmHOdrZ32ptd07MjQT2s2HvlsJx2jrzVOA+Kh4wrCafPZ/Rl02O4iOhxglrUAjB7DL0zqmDjThIpaf5S/ntxVyYQXEsM9LpLUVzIjcrb9iH/kj5BBTgoQ==; 24:eWoDbO4HdFx2x1QHeRDrHVb9ImH945uK26Ny9mcFOu+izEgZSWZXbrnibGOoB7WoWW4+q2YjEsTXH/kyaic5V31q2oK4KZ3U63JIJVJhSE4=; 20:9dg9BdTEelgJ2TRF74L3vOT+ldaFuXe/Oe5b69cPMopgcI9U4w4GqV1xhdO/75HioKswhUmFnIAysFI6fq8HVwZcgZu9ehslrklsp4K+w6vxsDBfXYNtTBVR9Z+90ptL4fqtbsi79chcV2RxzbfLMHSAx2GdfF4eJsW7qOhgF50= x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY1PR0501MB1401; x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:; x-exchange-antispam-report-cfa-test: BCL:0; PCL:0; RULEID:(601004)(2401047)(520078)(5005006)(8121501046)(10201501046)(3002001); SRVR:CY1PR0501MB1401; BCL:0; PCL:0; RULEID:; SRVR:CY1PR0501MB1401; x-forefront-prvs: 07665BE9D1 x-forefront-antispam-report: SFV:NSPM; SFS:(10009020)(199003)(11905935001)(189002)(164054003)(106116001)(6116002)(19580405001)(105586002)(102836003)(99286002)(106356001)(54356999)(3846002)(5004730100002)(5008740100001)(19580395003)(5007970100001)(189998001)(66066001)(551934003)(2501003)(92566002)(81156007)(40100003)(36756003)(77096005)(110136002)(19617315012)(586003)(86362001)(15975445007)(101416001)(16236675004)(11100500001)(50986999)(5002640100001)(97736004)(450100001)(107886002)(87936001)(5001960100002)(2351001)(122556002)(5001920100001)(2900100001)(10400500002); DIR:OUT; SFP:1101; SCL:1; SRVR:CY1PR0501MB1401; H:CY1PR0501MB1401.namprd05.prod.outlook.com; FPR:; SPF:None; PTR:InfoNoRecords; A:1; MX:1; LANG:en; received-spf: None (protection.outlook.com: teamdrg.com does not designate permitted sender hosts) spamdiagnosticoutput: 1:23 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_D274E3C11113E8awilliamsdresourcesgroupcom_" MIME-Version: 1.0 X-OriginatorOrg: teamdrg.com X-MS-Exchange-CrossTenant-originalarrivaltime: 20 Nov 2015 19:50:41.5060 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 5d6495b1-5cd4-4a4f-a6dd-1f5f3bf58831 X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR0501MB1401 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: CY1PR0501MB1401.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_D274E3C11113E8awilliamsdresourcesgroupcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I have a question concerning using the Postgres Hot Standby feature. We are currently using that feature to sync any changes from one master to one slave. The slave role is as a reporting db server with queries constantly and concurrently running (some in ms, some in seconds, some in minutes,) We have adjusted these two params to allow long queries on the hot standby: [http://www.postgresql.org/docs/current/static/hot-standby.html] max_standby_archive_delay =3D -1 # max delay before canceling queries max_standby_streaming_delay =3D -1 # max delay before canceling queries And looking at an archived mail question similar to ours: http://www.postgresql.org/message-id/AANLkTinLg+bpzcjzdndsnGGNFC=3DD1OsVh+h= Kb 85A-s=3Dn@mail.gmail.com response: http://www.postgresql.org/message-id/201102272005.00234.jens@wilke.org It looks like WAL updates cannot be applied while queries are running. I've tested that to be true. In our case, the reporting server has a good chance of not being synced for hours or possible longer because of all the queries that are running. Is it possible to run long running queries while having WAL updates applied to the slave? I understand the concept of preventing WAL updates not being applied to slaves while queries are running. However, I thought with the use of MVCC, an active query on the slave (long running, 30 seconds+) can run reading from row xid versions/snapshot, while the WAL update is being applied, so subsequent queries will get the WAL updates when that WAL transaction is committed. I haven't fully digested the MVCC model used in PostgreSQL yet [http://www.onlamp.com/pub/a/onlamp/2001/05/25/postgresql_mvcc.html and https://devcenter.heroku.com/articles/postgresql-concurrency], so this is just my assumption -- that even if a table is dropped / truncated during a WAL update, the current running query should still work as it's using a version/snapshot of the table(s) it's querying? Is there any way (even with a third party extension) we can sync slaves from a master and have those updates from the master be applied to the slave right away while letting queries of any execution time continue to run till they complete on the standby/slave? If Hot Standby can't do that, what would you recommend for this situation? Our scenario is that we are hitting postgres with queries constantly and concurrently running (some in ms, some in seconds, some in minutes,) leaving almost no time for a WAL update to be applied. Any help would be greatly appreciated. Thanks, Alex --_000_D274E3C11113E8awilliamsdresourcesgroupcom_ Content-Type: text/html; charset="us-ascii" Content-ID: <1964D8D0619D9244864C5D7C4D247828@namprd05.prod.outlook.com> Content-Transfer-Encoding: quoted-printable
Hi,<= /div>

I have a question= concerning using the Postgres Hot Standby feature. We
are currently usi= ng that feature to sync any changes from one master to
one slave. The sl= ave role is as a reporting db server with queries 
constantly and co= ncurrently running (some in ms, some in seconds, 
some in minutes,)=

We have adjusted = these two params to allow long queries on the hot standby:


max_standby_archi= ve_delay =3D -1  # max delay before canceling queries
max_standby_strea= ming_delay =3D -1        # max dela= y before canceling
queries

And looking at an= archived mail question similar to ours:

response:

It looks like WAL= updates cannot be applied while queries are running.
I've tested that = to be true. In our case, the reporting server has a 
good chance of no= t being synced for hours or possible longer because 
of all the querie= s that are running. Is it possible to run long running 
queries while hav= ing WAL updates applied to the slave?

I understand the = concept of preventing WAL updates not being applied to
slaves while quer= ies are running. However, I thought with the use of MVCC,
an active query o= n the slave (long running, 30 seconds+) can run reading
from row xid vers= ions/snapshot, while the WAL update is being applied, so
subsequent querie= s will get the WAL updates when that WAL transaction is
committed. I have= n't fully digested the MVCC model used in PostgreSQL yet
just my assumptio= n -- that even if a table is dropped / truncated during a
WAL update, the c= urrent running query should still work as it's using a
version/snapshot = of the table(s) it's querying?

Is there any way = (even with a third party extension) we can sync slaves
from a master and= have those updates from the master be applied to the
slave right away = while letting queries of any execution time continue to
run till they com= plete on the standby/slave? If Hot Standby can't do that,
what would you re= commend for this situation? Our scenario is that we are
hitting postgres = with queries constantly and concurrently running (some in
ms, some in secon= ds, some in minutes,) leaving almost no time for a WAL
update to be appl= ied.

Any help would be= greatly appreciated.

Thanks,

Alex
--_000_D274E3C11113E8awilliamsdresourcesgroupcom_--