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 1nK5Ft-0001mf-7j for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Feb 2022 21:21:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nK5Fs-0005uv-37 for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Feb 2022 21:21:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nK5Fr-0005uj-Ip for pgsql-hackers@lists.postgresql.org; Tue, 15 Feb 2022 21:20:59 +0000 Received: from lahtoruutu.iki.fi ([185.185.170.37]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nK5Fn-0003ox-Ko for pgsql-hackers@postgresql.org; Tue, 15 Feb 2022 21:20:58 +0000 Received: from [192.168.1.113] (dsl-hkibng22-54faa4-119.dhcp.inet.fi [84.250.164.119]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) (Authenticated sender: hlinnaka) by lahtoruutu.iki.fi (Postfix) with ESMTPSA id CA73E1B001B4; Tue, 15 Feb 2022 23:20:50 +0200 (EET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iki.fi; s=lahtoruutu; t=1644960051; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=HLdd+rlv4MpFCW3fI4c0KeprNxQbnvZRbxXGM4WkK94=; b=fIa1QsA4UUFG9uN53LYXEELK//4Rb1XXGsoevmtH1B1B1uCd1BRkrKyiuDdScRCdbEnUBe T8Mo+qPIfsofKf5CaL+2JpEqfurZ9RuA/qbN/O8UbxR20UvG8p6KwRJ6rdiqXfJ9oO35Za Gx30fUWjkAE3nTVyvXwhrjXz754qWgoMgz8TH4L8Vke4uLwhk8/Stt3Sco075TNZmpeEtM XcaSo9AjoQrH9FE0uwRzbgygro5937eusIHzEqBpOqeAyoAe8ySH/VFTLpt+iFHc5rTj/1 u7yySeHeecZN3UkgpHMQ5Rit3dR10Sua87pVifVSZ2w2F0axulThZKdSZdYDgQ== Content-Type: multipart/mixed; boundary="------------lvWR2nre2AwUvshQ0vw1Zu00" Message-ID: <831b6ab9-45d6-7ab0-ea9c-6efd4383afb8@iki.fi> Date: Tue, 15 Feb 2022 23:20:50 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.5.0 Subject: last_archived_wal is not necessary the latest WAL file (was Re: pgsql: Add test case for an archive recovery corner case.) Content-Language: en-US From: Heikki Linnakangas To: pgsql-hackers Cc: Tom Lane References: <2842251.1644849700@sss.pgh.pa.us> <8f15e602-337e-fbb2-4b2e-e83a01ef9088@iki.fi> In-Reply-To: <8f15e602-337e-fbb2-4b2e-e83a01ef9088@iki.fi> ARC-Authentication-Results: i=1; ORIGINATING; auth=pass smtp.auth=hlinnaka smtp.mailfrom=hlinnaka@iki.fi ARC-Seal: i=1; s=lahtoruutu; d=iki.fi; t=1644960051; a=rsa-sha256; cv=none; b=iB2JOjytOyienAGqxnC91xM/XDoyDJJyXTlehJM78YAjxT6mO0sDXs3PkpHWObOAPt5wxO U2gpGIt0eHDzNPslXc9+PT3Y6z9T9pjiScWvdWOQs+oP4cVrKcUbOEFd9VI9+pgbVf7R/t nb2iAvNVdUJwMrkYRtpQicZ9l2mVZ1Fyqt9sV+eoGH0KoHD3KwSMhF01izmUfMNmBMy3ca UDtp4uYv5xH3eNihT+ZqMRVzNSjTMBOj8+X76EVulJNNdAw3MEVsUkWESYBNhSQY+yKRLs SeCFPeldCRvg6jJB1wW6HLpKo5fgWaf6qFNfc/MG0eNLmaq0Grks5SrxBBAELQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=iki.fi; s=lahtoruutu; t=1644960051; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=HLdd+rlv4MpFCW3fI4c0KeprNxQbnvZRbxXGM4WkK94=; b=hKq46owGgSjH2q/dgTOfUlrRdXwCefu1P1SVSj6B92nZiLaop17Wvjc2dOHW7fOxun0XGB NJ6E/XhYN4mV2XrAA77eB0ndmoqdkzciQHmZP/TaGq0uTE8QJHXFCL7UEBcr11CfzQU5xU oYPvQXj6RTzyyp5KRt7+OL5TieyHgMTphMMsWFhgJRiQoY3mDbenNyJH01NSK6BI44JP8M vm9yzxlylma//8AYQarVnV+HMiPEg5mrcGAqVzdMG+gP8qC/qIE4EqRH1bA96fzuwzr3jr 12oA1kYX2dJudrH0aHio6CovI6jMnyYF1LGvP5kmtZNo3YkE4ggh/hld8LY5bA== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------lvWR2nre2AwUvshQ0vw1Zu00 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 14/02/2022 22:43, Heikki Linnakangas wrote: > On 14/02/2022 16:41, Tom Lane wrote: >> Heikki Linnakangas writes: >>> Add test case for an archive recovery corner case. >> >> hoverfly seems not to like this: >> >> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hoverfly&dt=2022-02-14%2012%3A36%3A12 > > Hmm, only hoverfly - and even that succeeded on next run. Some kind of a > flakyness I guess. I'll try to run the test in a loop and see if I can > reproduce it. That was interesting: the order that WAL segments are archived when a standby is promoted is not fully deterministic. The test polled pg_stat_archiver.last_archived_wal to wait until a particular WAL segment was archived, but it could happen that a lower-numbered WAL segment was archived *after* the waited-for segment, and pg_stat_archiver.last_archived_wal therefore displayed the lower-numbered WAL segment. So the test incorrectly thought that the higher-numbered segment that it waits for hadn't been archived yet. I realized that this test doesn't really need to wait for the segment to be archived, because it will stop the standby server immediately after that, and stopping a server implicitly waits for all the WAL to be archived before the archiver process exits. So I just removed it. During normal operations the WAL segments are archived in order. But I'm not sure if there are some other corner cases, aside from promoting a standby server, when this could happen. After crash restart, maybe, if some .ready/done files are lost. I find it a bit surprising that pg_stat_archiver.last_archived_wal is not necessarily the highest-numbered segment that was archived. I propose that we mention that in the docs, as in the attached patch. I'll commit this soon, to silence the occasional failures on the buildfarm, but let me know if you have any better suggestions or thoughts. - Heikki --------------lvWR2nre2AwUvshQ0vw1Zu00 Content-Type: text/x-patch; charset=UTF-8; name="0001-Fix-race-condition-in-028_pitr_timelines.pl-test-add.patch" Content-Disposition: attachment; filename*0="0001-Fix-race-condition-in-028_pitr_timelines.pl-test-add.pa"; filename*1="tch" Content-Transfer-Encoding: base64 RnJvbSAyNzFkNGVjOGNhZWMwMTgxODZhYjAzMTQyOTVlZTc2MWUyODgyNDg4IE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBIZWlra2kgTGlubmFrYW5nYXMgPGhlaWtraS5saW5u YWthbmdhc0Bpa2kuZmk+CkRhdGU6IFR1ZSwgMTUgRmViIDIwMjIgMjM6MTQ6NTkgKzAyMDAK U3ViamVjdDogW1BBVENIIDEvMV0gRml4IHJhY2UgY29uZGl0aW9uIGluIDAyOF9waXRyX3Rp bWVsaW5lcy5wbCB0ZXN0LCBhZGQKIG5vdGUgdG8gZG9jcy4KClRoZSAwMjhfcGl0cl90aW1l bGluZXMucGwgdGVzdCB3b3VsZCBzb21ldGltZXMgaGFuZywgd2FpdGluZyBmb3IgYSBXQUwK c2VnbWVudCB0aGF0IHdhcyBqdXN0IGZpbGxlZCB1cCB0byBiZSBhcmNoaXZlZC4gSXQgd2Fz IGJlY2F1c2UgdGhlCnRlc3QgdXNlZCAncGdfc3RhdF9hcmNoaXZlci5sYXN0X2FyY2hpdmVk X3dhbCcgdG8gY2hlY2sgaWYgYSBmaWxlIHdhcwphcmNoaXZlZCwgYnV0IHRoZSBvcmRlciB0 aGF0IFdBTCBmaWxlcyBhcmUgYXJjaGl2ZWQgd2hlbiBhIHN0YW5kYnkgaXMKcHJvbW90ZWQg aXMgbm90IGZ1bGx5IGRldGVybWluaXN0aWMsIGFuZCAnbGFzdF9hcmNoaXZlZF93YWwnIHRy YWNrcwp0aGUgbGFzdCBzZWdtZW50IHRoYXQgd2FzIGFyY2hpdmVkLCBub3QgdGhlIGhpZ2hl c3QtbnVtYmVyZWQgV0FMCnNlZ21lbnQuIEJlY2F1c2Ugb2YgdGhhdCwgaWYgdGhlIGFyY2hp dmVyIGFyY2hpdmVkIHNlZ21lbnQgMywgYW5kIHRoZW4KMiwgJ2xhc3RfYXJjaGl2ZWRfd2Fs JyBzYXkgMiwgYW5kIHRoZSB0ZXN0IHF1ZXJ5IHdvdWxkIHRoaW5rIHRoYXQgMwpoYXMgbm90 IGJlZW4gYXJjaGl2ZWQgeWV0LgoKTm9ybWFsbHksIFdBTCBmaWxlcyBhcmUgbWFya2VkIHJl YWR5IGZvciBhcmNoaXZhbCBpbiBvcmRlciwgYW5kIHRoZQphcmNoaXZlciBwcm9jZXNzIHdp bGwgcHJvY2VzcyB0aGVtIGluIG9yZGVyLCBzbyB0aGF0IGlzc3VlIGRvZXNuJ3QKYXJpc2Uu ICBXZSBoYXZlIHVzZWQgdGhlIHNhbWUgcXVlcnkgb24gJ2xhc3RfYXJjaGl2ZWRfd2FsJyBp biBhIGZldwpvdGhlciB0ZXN0cyB3aXRoIG5vIHByb2JsZW0uIEJ1dCB3aGVuIGEgc3RhbmRi eSBpcyBwcm9tb3RlZCwgdGhpbmdzCmFyZSBhIGJpdCBjaGFvdGljLiBBZnRlciBwcm9tb3Rp b24sIHRoZSBzZXJ2ZXIgd2lsbCB0cnkgdG8gYXJjaGl2ZSBhbGwKdGhlIFdBTCBzZWdtZW50 cyBmcm9tIHRoZSBvbGQgdGltZWxpbmUgdGhhdCBhcmUgaW4gcGdfd2FsLCBhcyB3ZWxsIGFz CnRoZSBoaXN0b3J5IGZpbGUgYW5kIGFueSBuZXcgV0FMIHNlZ21lbnRzIG9uIHRoZSBuZXcg dGltZWxpbmUuIFRoZQplbmQtb2YtcmVjb3ZlcnkgY2hlY2twb2ludCB3aWxsIGNyZWF0ZSB0 aGUgLnJlYWR5IGZpbGVzIGZvciBhbGwgdGhlCldBTCBmaWxlcyBvbiB0aGUgb2xkIHRpbWVs aW5lLCBidXQgYXQgdGhlIHNhbWUgdGltZSwgdGhlIG5ldyB0aW1lbGluZQppcyBvcGVuZWQg dXAgZm9yIGJ1c2luZXNzLiBBIGZpbGUgZnJvbSB0aGUgbmV3IHRpbWVsaW5lIGNhbiB0aGVy ZWZvcmUKYmUgYXJjaGl2ZWQgYmVmb3JlIHRoZSBmaWxlcyBmcm9tIHRoZSBvbGQgdGltZWxp bmUgaGF2ZSBiZWVuIG1hcmtlZCBhcwpyZWFkeSBmb3IgYXJjaGl2YWwuCgpJdCB0dXJucyBv dXQgdGhhdCB3ZSBkb24ndCByZWFsbHkgbmVlZCB0byB3YWl0IGZvciB0aGUgYXJjaGl2YWwg aW4KdGhpcyBwYXJ0aWN1bGFyIHRlc3QsIGJlY2F1c2UgdGhlIHN0YW5kYnkgc2VydmVyIGlz IGFib3V0IHRvIGJlCnN0b3BwZWQsIGFuZCBzdG9wcGluZyBhIHNlcnZlciB3aWxsIHdhaXQg Zm9yIHRoZSBlbmQtb2YtcmVjb3ZlcnkKY2hlY2twb2ludCBhbmQgYWxsIFdBTCBhcmNoaXZh bHMgdG8gZmluaXNoLCBhbnl3YXkuIFNvIHdlIGNhbiBqdXN0CnJlbW92ZSBpdCBmcm9tIHRo ZSB0ZXN0LgoKQWRkIGEgbm90ZSB0byB0aGUgZG9jcyBvbiAncGdfc3RhdF9hcmNoaXZlcicg dmlldyB0aGF0IGZpbGVzIGNhbiBiZQphcmNoaXZlZCBvdXQgb2Ygb3JkZXIuCgpEaXNjdXNz aW9uOiBodHRwczovL3d3dy5wb3N0Z3Jlc3FsLm9yZy9tZXNzYWdlLWlkLzI4NDIyNTEuMTY0 NDg0OTcwMEBzc3MucGdoLnBhLnVzCi0tLQogZG9jL3NyYy9zZ21sL21vbml0b3Jpbmcuc2dt bCAgICAgICAgICAgICAgfCAgOSArKysrKysrKysKIHNyYy90ZXN0L3JlY292ZXJ5L3QvMDI4 X3BpdHJfdGltZWxpbmVzLnBsIHwgMTMgKystLS0tLS0tLS0tLQogMiBmaWxlcyBjaGFuZ2Vk LCAxMSBpbnNlcnRpb25zKCspLCAxMSBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9kb2Mv c3JjL3NnbWwvbW9uaXRvcmluZy5zZ21sIGIvZG9jL3NyYy9zZ21sL21vbml0b3Jpbmcuc2dt bAppbmRleCA2MmYyYTMzMzJiLi40NGUwODIyNWE4IDEwMDY0NAotLS0gYS9kb2Mvc3JjL3Nn bWwvbW9uaXRvcmluZy5zZ21sCisrKyBiL2RvYy9zcmMvc2dtbC9tb25pdG9yaW5nLnNnbWwK QEAgLTM0NzQsNiArMzQ3NCwxNSBAQCBTRUxFQ1QgcGlkLCB3YWl0X2V2ZW50X3R5cGUsIHdh aXRfZXZlbnQgRlJPTSBwZ19zdGF0X2FjdGl2aXR5IFdIRVJFIHdhaXRfZXZlbnQgaQogICAg PC90Z3JvdXA+CiAgIDwvdGFibGU+CiAKKyAgPHBhcmE+CisgICAgTm9ybWFsbHksIFdBTCBm aWxlcyBhcmUgYXJjaGl2ZWQgaW4gb3JkZXIsIG9sZGVzdCB0byBuZXdlc3QsIGJ1dCB0aGF0 IGlzCisgICAgbm90IGd1YXJhbnRlZWQsIGFuZCBkb2VzIG5vdCBob2xkIHVuZGVyIHNwZWNp YWwgY2lyY3Vtc3RhbmNlcyBsaWtlIHdoZW4KKyAgICBwcm9tb3RpbmcgYSBzdGFuZGJ5IG9y IGFmdGVyIGNyYXNoIHJlY292ZXJ5LiBUaGVyZWZvcmUgaXQgaXMgbm90IHNhZmUgdG8KKyAg ICBhc3N1bWUgdGhhdCBhbGwgZmlsZXMgb2xkZXIgdGhhbgorICAgIDxzdHJ1Y3RmaWVsZD5s YXN0X2FyY2hpdmVkX3dhbDwvc3RydWN0ZmllbGQ+IGhhdmUgYWxzbyBiZWVuIHN1Y2Nlc3Nm dWxseQorICAgIGFyY2hpdmVkLgorICA8L3BhcmE+CisKICA8L3NlY3QyPgogCiAgPHNlY3Qy IGlkPSJtb25pdG9yaW5nLXBnLXN0YXQtYmd3cml0ZXItdmlldyI+CmRpZmYgLS1naXQgYS9z cmMvdGVzdC9yZWNvdmVyeS90LzAyOF9waXRyX3RpbWVsaW5lcy5wbCBiL3NyYy90ZXN0L3Jl Y292ZXJ5L3QvMDI4X3BpdHJfdGltZWxpbmVzLnBsCmluZGV4IGMwYjc2ZmUzN2IuLmE4YjEy ZDlhZjYgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlY292ZXJ5L3QvMDI4X3BpdHJfdGltZWxp bmVzLnBsCisrKyBiL3NyYy90ZXN0L3JlY292ZXJ5L3QvMDI4X3BpdHJfdGltZWxpbmVzLnBs CkBAIC0zNiw3ICszNiw2IEBAIHVzZSBGaWxlOjpDb21wYXJlOwogIyBJbml0aWFsaXplIGFu ZCBzdGFydCBwcmltYXJ5IG5vZGUgd2l0aCBXQUwgYXJjaGl2aW5nCiBteSAkbm9kZV9wcmlt YXJ5ID0gUG9zdGdyZVNRTDo6VGVzdDo6Q2x1c3Rlci0+bmV3KCdwcmltYXJ5Jyk7CiAkbm9k ZV9wcmltYXJ5LT5pbml0KGhhc19hcmNoaXZpbmcgPT4gMSwgYWxsb3dzX3N0cmVhbWluZyA9 PiAxKTsKLSRub2RlX3ByaW1hcnktPmFwcGVuZF9jb25mKCdwb3N0Z3Jlc3FsLmNvbmYnLCAn bG9nX21pbl9tZXNzYWdlcz1kZWJ1ZzEnKTsKICRub2RlX3ByaW1hcnktPnN0YXJ0OwogCiAj IFRha2UgYSBiYWNrdXAuCkBAIC03MCw3ICs2OSw2IEBAICRub2RlX3N0YW5kYnktPmluaXRf ZnJvbV9iYWNrdXAoCiAJaGFzX2FyY2hpdmluZyA9PiAxLAogCWhhc19yZXN0b3JpbmcgPT4g MCk7CiAkbm9kZV9zdGFuZGJ5LT5hcHBlbmRfY29uZigncG9zdGdyZXNxbC5jb25mJywgJ2Fy Y2hpdmVfbW9kZSA9IGFsd2F5cycpOwotJG5vZGVfc3RhbmRieS0+YXBwZW5kX2NvbmYoJ3Bv c3RncmVzcWwuY29uZicsICdsb2dfbWluX21lc3NhZ2VzPWRlYnVnMScpOwogJG5vZGVfc3Rh bmRieS0+c3RhcnQ7CiAkbm9kZV9wcmltYXJ5LT53YWl0X2Zvcl9jYXRjaHVwKCRub2RlX3N0 YW5kYnkpOwogCkBAIC05MywxNSArOTEsOCBAQCBteSAkd2FsZmlsZV90b19iZV9hcmNoaXZl ZCA9ICRub2RlX3N0YW5kYnktPnNhZmVfcHNxbCgncG9zdGdyZXMnLAogIyBNYWtlIFdBTCBz ZWdtZW50IGVsaWdpYmxlIGZvciBhcmNoaXZhbAogJG5vZGVfc3RhbmRieS0+c2FmZV9wc3Fs KCdwb3N0Z3JlcycsICdTRUxFQ1QgcGdfc3dpdGNoX3dhbCgpJyk7CiAKLSMgV2FpdCB1bnRp bCB0aGUgV0FMIHNlZ21lbnQgaGFzIGJlZW4gYXJjaGl2ZWQuCi1teSAkYXJjaGl2ZV93YWl0 X3F1ZXJ5ID0KLSAgIlNFTEVDVCAnJHdhbGZpbGVfdG9fYmVfYXJjaGl2ZWQnIDw9IGxhc3Rf YXJjaGl2ZWRfd2FsIEZST00gcGdfc3RhdF9hcmNoaXZlcjsiOwotJG5vZGVfc3RhbmRieS0+ cG9sbF9xdWVyeV91bnRpbCgncG9zdGdyZXMnLCAkYXJjaGl2ZV93YWl0X3F1ZXJ5KQotICBv ciBkaWUgIlRpbWVkIG91dCB3aGlsZSB3YWl0aW5nIGZvciBXQUwgc2VnbWVudCB0byBiZSBh cmNoaXZlZCI7Ci1teSAkbGFzdF9hcmNoaXZlZF93YWxfZmlsZSA9ICR3YWxmaWxlX3RvX2Jl X2FyY2hpdmVkOwotCi0jIE9rLCB0aGUgc3RhbmRieSBoYXMgbm93IGFyY2hpdmVkIHRoZSBX QUwgb24gdGltZWxpbmUgMi4gIFdlIGRvbid0Ci0jIG5lZWQgdGhlIHN0YW5kYnkgYW55bW9y ZS4KKyMgV2UgZG9uJ3QgbmVlZCB0aGUgc3RhbmRieSBhbnltb3JlLCByZXF1ZXN0IHNodXRk b3duLiBUaGUgc2VydmVyIHdpbGwKKyMgZmluaXNoIGFyY2hpdmluZyBhbGwgdGhlIFdBTCBv biB0aW1lbGluZSAyIGJlZm9yZSBpdCBleGl0cy4KICRub2RlX3N0YW5kYnktPnN0b3A7CiAK ICMgQ29udGVudHMgb2YgdGhlIFdBTCBhcmNoaXZlIGF0IHRoaXMgcG9pbnQ6Ci0tIAoyLjMw LjIKCg== --------------lvWR2nre2AwUvshQ0vw1Zu00--