Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h41lK-0008Sv-Ld for pgsql-hackers@arkaria.postgresql.org; Wed, 13 Mar 2019 11:09:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h41lJ-0006nR-9j for pgsql-hackers@arkaria.postgresql.org; Wed, 13 Mar 2019 11:09:29 +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_SHA1:256) (Exim 4.89) (envelope-from ) id 1h41lJ-0006k4-0H for pgsql-hackers@lists.postgresql.org; Wed, 13 Mar 2019 11:09:29 +0000 Received: from gauss.credativ.com ([93.94.130.89]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h41lG-0004lT-HU for pgsql-hackers@lists.postgresql.org; Wed, 13 Mar 2019 11:09:28 +0000 Received: from gauss.credativ.com (localhost [127.0.0.1]) by gauss.credativ.com (Postfix) with ESMTP id 688461E39F7; Wed, 13 Mar 2019 12:09:25 +0100 (CET) Received: from kohn (fw-front.credativ.com [62.154.226.94]) (using TLSv1 with cipher ECDHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: mba@gauss.credativ.com) by gauss.credativ.com (Postfix) with ESMTPSA id 08A901E1BC7; Wed, 13 Mar 2019 12:09:24 +0100 (CET) Message-ID: <1552475364.4947.64.camel@credativ.de> Subject: Re: Offline enabling/disabling of data checksums From: Michael Banck To: Magnus Hagander Cc: Michael Paquier , Fabien COELHO , Sergei Kornilov , PostgreSQL Developers Date: Wed, 13 Mar 2019 12:09:24 +0100 In-Reply-To: References: <20181221201616.GD4974@nighthawk.caipicrew.dd-dns.de> <20190311045323.GE1818@paquier.xyz> <155231347133.16480.11453587097036807558.pgcf@coridan.postgresql.org> <1552385626.4947.50.camel@credativ.de> <20190312124403.GM13812@paquier.xyz> <20190313021621.GP13812@paquier.xyz> <20190313093150.GE2988@paquier.xyz> <1552473675.4947.62.camel@credativ.de> Organization: credativ GmbH Content-Type: multipart/mixed; boundary="=-0m9BO1gsDFKuSm1vzxV0" X-Mailer: Evolution 3.22.6-1+deb9u1 Mime-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=-0m9BO1gsDFKuSm1vzxV0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 8bit Hi, Am Mittwoch, den 13.03.2019, 11:47 +0100 schrieb Magnus Hagander: > On Wed, Mar 13, 2019 at 11:41 AM Michael Banck wrote: > > I propose we re-read the control file for the enable case after we > > finished operating on all files and (i) check the instance is still > > offline and (ii) update the checksums version from there. That should be > > a small but worthwhile change that could be done anyway. > > In (i) you need to also check that is' not offline *again*. Somebody > could start *and* stop the database while pg_checksums is running. But > that should hopefully be enough to check the time field? Good point. > > Also, I suggest to maybe add a notice in verbose mode that we are > > syncing the data directory - otherwise the user might wonder what's > > going on at 100% done, though I haven't seen a large delay in my tests > > so far. > > Seems like a good idea -- there certainly could be a substantial delay > there depending on data size and underlying storage. The attached patch should do the above, on top of Michael's last patchset. Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.banck@credativ.de credativ GmbH, HRB Mönchengladbach 12080 USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz --=-0m9BO1gsDFKuSm1vzxV0 Content-Disposition: attachment; filename*0=0001-Guard-against-concurrent-cluster-changes-when-enabli.pat; filename*1=ch Content-Transfer-Encoding: base64 Content-Type: text/x-patch; name="0001-Guard-against-concurrent-cluster-changes-when-enabli.patch"; charset="UTF-8" RnJvbSA3MzFjNDhkMjFiMGFkZmE3NGU3Y2UxZTA1YjJkOGYxMTQ2YjllM2NmIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBNaWNoYWVsIEJhbmNrIDxtYmFuY2tAZGViaWFuLm9yZz4KRGF0 ZTogV2VkLCAxMyBNYXIgMjAxOSAxMjowNTozNCArMDEwMApTdWJqZWN0OiBbUEFUQ0hdIEd1YXJk IGFnYWluc3QgY29uY3VycmVudCBjbHVzdGVyIGNoYW5nZXMgd2hlbiBlbmFibGluZwogY2hlY2tz dW1zLgoKUmUtcmVhZCB0aGUgY29udHJvbCBmaWxlIGFmdGVyIG9wZXJhdGluZyBvbiBhbGwgZmls ZXMgaW4gb3JkZXIgdG8gY2hlY2sgd2hldGhlcgp0aGUgaW5zdGFuY2UgaXMgc3RpbGwgc2h1dGRv d24gYW5kIHRoZSBjb250cm9sIGZpbGUgc3RpbGwgaGFzIHRoZSBzYW1lCm1vZGlmaWNhdGlvbiB0 aW1lc3RhbXAuCgpJbiBwYXNzaW5nLCBhZGQgYSBub3RlIGFib3V0IHN5bmNpbmcgdGhlIGRhdGEg ZGlyZWN0b3J5IGluIHZlcmJvc2UgbW9kZS4KLS0tCiBzcmMvYmluL3BnX2NoZWNrc3Vtcy9wZ19j aGVja3N1bXMuYyB8IDM4ICsrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKystLS0KIDEg ZmlsZSBjaGFuZ2VkLCAzNSBpbnNlcnRpb25zKCspLCAzIGRlbGV0aW9ucygtKQoKZGlmZiAtLWdp dCBhL3NyYy9iaW4vcGdfY2hlY2tzdW1zL3BnX2NoZWNrc3Vtcy5jIGIvc3JjL2Jpbi9wZ19jaGVj a3N1bXMvcGdfY2hlY2tzdW1zLmMKaW5kZXggMGU0NjQyOTlmMy4uYWZjYTZjZjAyNyAxMDA2NDQK LS0tIGEvc3JjL2Jpbi9wZ19jaGVja3N1bXMvcGdfY2hlY2tzdW1zLmMKKysrIGIvc3JjL2Jpbi9w Z19jaGVja3N1bXMvcGdfY2hlY2tzdW1zLmMKQEAgLTMwOSw2ICszMDksNyBAQCBtYWluKGludCBh cmdjLCBjaGFyICphcmd2W10pCiAJaW50CQkJYzsKIAlpbnQJCQlvcHRpb25faW5kZXg7CiAJYm9v bAkJY3JjX29rOworCXBnX3RpbWVfdAljb250cm9sZmlsZV9sYXN0X3VwZGF0ZWQ7CiAKIAlzZXRf cGdsb2NhbGVfcGdzZXJ2aWNlKGFyZ3ZbMF0sIFBHX1RFWFRET01BSU4oInBnX2NoZWNrc3VtcyIp KTsKIApAQCAtMzk5LDcgKzQwMCw3IEBAIG1haW4oaW50IGFyZ2MsIGNoYXIgKmFyZ3ZbXSkKIAkJ ZXhpdCgxKTsKIAl9CiAKLQkvKiBDaGVjayBpZiBjbHVzdGVyIGlzIHJ1bm5pbmcgKi8KKwkvKiBH ZXQgY29udHJvbCBmaWxlIGRhdGEgKi8KIAlDb250cm9sRmlsZSA9IGdldF9jb250cm9sZmlsZShE YXRhRGlyLCBwcm9nbmFtZSwgJmNyY19vayk7CiAJaWYgKCFjcmNfb2spCiAJewpAQCAtNDE0LDYg KzQxNSw3IEBAIG1haW4oaW50IGFyZ2MsIGNoYXIgKmFyZ3ZbXSkKIAkJZXhpdCgxKTsKIAl9CiAK KwkvKiBDaGVjayBpZiBjbHVzdGVyIGlzIHJ1bm5pbmcgKi8KIAlpZiAoQ29udHJvbEZpbGUtPnN0 YXRlICE9IERCX1NIVVRET1dORUQgJiYKIAkJQ29udHJvbEZpbGUtPnN0YXRlICE9IERCX1NIVVRE T1dORURfSU5fUkVDT1ZFUlkpCiAJewpAQCAtNDQwLDYgKzQ0Miw5IEBAIG1haW4oaW50IGFyZ2Ms IGNoYXIgKmFyZ3ZbXSkKIAkJZXhpdCgxKTsKIAl9CiAKKwkvKiBTYXZlIHRpbWUgb2YgbGFzdCBj b250cm9sIGZpbGUgbW9kaWZpY2F0aW9uICovCisJY29udHJvbGZpbGVfbGFzdF91cGRhdGVkID0g Q29udHJvbEZpbGUtPnRpbWU7CisKIAkvKiBPcGVyYXRlIG9uIGFsbCBmaWxlcyBpZiBjaGVja2lu ZyBvciBlbmFibGluZyBjaGVja3N1bXMgKi8KIAlpZiAobW9kZSA9PSBQR19NT0RFX0NIRUNLIHx8 IG1vZGUgPT0gUEdfTU9ERV9FTkFCTEUpCiAJewpAQCAtNDYxLDE3ICs0NjYsNDQgQEAgbWFpbihp bnQgYXJnYywgY2hhciAqYXJndltdKQogCX0KIAogCS8qCi0JICogRmluYWxseSB1cGRhdGUgdGhl IGNvbnRyb2wgZmlsZSwgZmx1c2hpbmcgdGhlIGRhdGEgZGlyZWN0b3J5IGF0IHRoZQotCSAqIGVu ZC4KKwkgKiBGaW5hbGx5IHVwZGF0ZSB0aGUgY29udHJvbCBmaWxlIGFmdGVyIGNoZWNraW5nIHRo ZSBjbHVzdGVyIGlzIHN0aWxsCisJICogb2ZmbGluZSBhbmQgaXRzIGNvbnRyb2wgZmlsZSBoYXMg bm90IGNoYW5nZWQsIGZsdXNoaW5nIHRoZSBkYXRhCisJICogZGlyZWN0b3J5IGF0IHRoZSBlbmQu CiAJICovCiAJaWYgKG1vZGUgPT0gUEdfTU9ERV9FTkFCTEUgfHwgbW9kZSA9PSBQR19NT0RFX0RJ U0FCTEUpCiAJeworCQlDb250cm9sRmlsZSA9IGdldF9jb250cm9sZmlsZShEYXRhRGlyLCBwcm9n bmFtZSwgJmNyY19vayk7CisJCWlmICghY3JjX29rKQorCQl7CisJCQlmcHJpbnRmKHN0ZGVyciwg XygiJXM6IHBnX2NvbnRyb2wgQ1JDIHZhbHVlIGlzIGluY29ycmVjdFxuIiksIHByb2duYW1lKTsK KwkJCWV4aXQoMSk7CisJCX0KKworCQkvKiBDaGVjayBpZiBjbHVzdGVyIGlzIHJ1bm5pbmcgKi8K KwkJaWYgKENvbnRyb2xGaWxlLT5zdGF0ZSAhPSBEQl9TSFVURE9XTkVEICYmCisJCUNvbnRyb2xG aWxlLT5zdGF0ZSAhPSBEQl9TSFVURE9XTkVEX0lOX1JFQ09WRVJZKQorCQl7CisJCQlmcHJpbnRm KHN0ZGVyciwgXygiJXM6IGNsdXN0ZXIgbm8gbG9uZ2VyIHNodXQgZG93blxuIiksIHByb2duYW1l KTsKKwkJCWV4aXQoMSk7CisJCX0KKworCQkvKiBDaGVjayBpZiBjb250cm9sIGZpbGUgaGFzIGNo YW5nZWQgKi8KKwkJaWYgKGNvbnRyb2xmaWxlX2xhc3RfdXBkYXRlZCAhPSBDb250cm9sRmlsZS0+ dGltZSkKKwkJeworCQkJZnByaW50ZihzdGRlcnIsIF8oIiVzOiBjb250cm9sIGZpbGUgaGFzIGNo YW5nZWQgc2luY2Ugc3RhcnR1cFxuIiksIHByb2duYW1lKTsKKwkJCWV4aXQoMSk7CisJCX0KKwog CQkvKiBVcGRhdGUgY29udHJvbCBmaWxlICovCiAJCUNvbnRyb2xGaWxlLT5kYXRhX2NoZWNrc3Vt X3ZlcnNpb24gPQogCQkJKG1vZGUgPT0gUEdfTU9ERV9FTkFCTEUpID8gUEdfREFUQV9DSEVDS1NV TV9WRVJTSU9OIDogMDsKIAkJdXBkYXRlX2NvbnRyb2xmaWxlKERhdGFEaXIsIHByb2duYW1lLCBD b250cm9sRmlsZSk7CiAJCWlmIChkb19zeW5jKQorCQl7CisJCQlpZiAodmVyYm9zZSAmJiBtb2Rl ID09IFBHX01PREVfRU5BQkxFKQorCQkJCXByaW50ZihfKCJTeW5jaW5nIGRhdGEgZGlyZWN0b3J5 XG4iKSk7CiAJCQlmc3luY19wZ2RhdGEoRGF0YURpciwgcHJvZ25hbWUsIFBHX1ZFUlNJT05fTlVN KTsKKwkJfQogCQlpZiAodmVyYm9zZSkKIAkJCXByaW50ZihfKCJEYXRhIGNoZWNrc3VtIHZlcnNp b246ICVkXG4iKSwgQ29udHJvbEZpbGUtPmRhdGFfY2hlY2tzdW1fdmVyc2lvbik7CiAJCWlmICht b2RlID09IFBHX01PREVfRU5BQkxFKQotLSAKMi4xMS4wCgo= --=-0m9BO1gsDFKuSm1vzxV0--