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 1h4SFb-000624-3K for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Mar 2019 15:26:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h4SFZ-00013R-DG for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Mar 2019 15:26:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h4SFZ-00013E-1m for pgsql-hackers@lists.postgresql.org; Thu, 14 Mar 2019 15:26:29 +0000 Received: from gauss.credativ.com ([93.94.130.89]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h4SFV-0002wA-E7 for pgsql-hackers@lists.postgresql.org; Thu, 14 Mar 2019 15:26:27 +0000 Received: from gauss.credativ.com (localhost [127.0.0.1]) by gauss.credativ.com (Postfix) with ESMTP id 4F1451E298D; Thu, 14 Mar 2019 16:26:22 +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 D33CA1E1DEE; Thu, 14 Mar 2019 16:26:21 +0100 (CET) Message-ID: <1552577180.9697.4.camel@credativ.de> Subject: Re: Offline enabling/disabling of data checksums From: Michael Banck To: Magnus Hagander , Michael Paquier Cc: Fabien COELHO , Sergei Kornilov , PostgreSQL Developers Date: Thu, 14 Mar 2019 16:26:20 +0100 In-Reply-To: References: <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> <1552475364.4947.64.camel@credativ.de> <20190313115633.GA1878@paquier.xyz> <20190314043936.GH3493@paquier.xyz> Organization: credativ GmbH Content-Type: multipart/mixed; boundary="=-kblYo35aefjqgrQ2tUtK" 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 --=-kblYo35aefjqgrQ2tUtK Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 8bit Hi, Am Donnerstag, den 14.03.2019, 15:26 +0100 schrieb Magnus Hagander: > Given that the failure is data corruption, I don't think big fat > warning is enough. We should really make it impossible to start up the > postmaster by mistake during the checksum generation. People don't > read the documentation until it's too late. And it might not even be > under their control - some automated tool might go in and try to start > postgres, and boom, corruption. I guess you're right. > One big-hammer method could be similar to what pg_upgrade does -- > temporarily rename away the controlfile so postgresql can't start, and > when done, put it back. That sounds like a good solution to me. I've made PoC patch for that, see attached. The only question is whether pg_checksums should try to move pg_control back (i) on failure (ii) when interrupted? 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 --=-kblYo35aefjqgrQ2tUtK Content-Disposition: attachment; filename="0001-Rename-away-pg_control-while-enabling-checksums.patch" Content-Transfer-Encoding: base64 Content-Type: text/x-patch; name="0001-Rename-away-pg_control-while-enabling-checksums.patch"; charset="UTF-8" RnJvbSA5YjE0NzFhZmVlNDg1YTk2YzMyZTM5Mzk4OWNhNGViMGFlZDUyZjg4IE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBNaWNoYWVsIEJhbmNrIDxtYmFuY2tAZGViaWFuLm9yZz4KRGF0 ZTogVGh1LCAxNCBNYXIgMjAxOSAxNjowMjo0OSArMDEwMApTdWJqZWN0OiBbUEFUQ0hdIFJlbmFt ZSBhd2F5IHBnX2NvbnRyb2wgd2hpbGUgZW5hYmxpbmcgY2hlY2tzdW1zLgoKSW4gb3JkZXIgdG8g cHJldmVudCB0aGF0IHRoZSBjbHVzdGVyIGlzIGFjY2lkZW50bHkgc3RhcnRlZCBkdXJpbmcgdGhl Cm9wZXJhdGlvbiwgdGhlIHBnX2NvbnRyb2wgZmlsZSBpcyByZW5hbWVkIHRvCnBnX2NvbnRyb2wu cGdfY2hlY2tzdW1zX2luX3Byb2dyZXNzIGR1cmluZyB0aGUgb3BlcmF0aW9uIGFuZCByZW5hbWVk IGJhY2sgYXQKdGhlIGVuZC4KLS0tCiBkb2Mvc3JjL3NnbWwvcmVmL3BnX2NoZWNrc3Vtcy5zZ21s ICB8ICA1ICsrKy0tCiBzcmMvYmluL3BnX2NoZWNrc3Vtcy9wZ19jaGVja3N1bXMuYyB8IDM2ICsr KysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKwogMiBmaWxlcyBjaGFuZ2VkLCAzOSBp bnNlcnRpb25zKCspLCAyIGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9y ZWYvcGdfY2hlY2tzdW1zLnNnbWwgYi9kb2Mvc3JjL3NnbWwvcmVmL3BnX2NoZWNrc3Vtcy5zZ21s CmluZGV4IGMzY2NiZjRlYjcuLjkxZGZkMzUxNWEgMTAwNjQ0Ci0tLSBhL2RvYy9zcmMvc2dtbC9y ZWYvcGdfY2hlY2tzdW1zLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL3JlZi9wZ19jaGVja3N1bXMu c2dtbApAQCAtNDgsOCArNDgsOSBAQCBQb3N0Z3JlU1FMIGRvY3VtZW50YXRpb24KICAgPHBhcmE+ CiAgICBXaGlsZSBjaGVja2luZyBvciBlbmFibGluZyBjaGVja3N1bXMgbmVlZHMgdG8gc2NhbiBv ciB3cml0ZSBldmVyeSBmaWxlIGluCiAgICB0aGUgY2x1c3RlciwgZGlzYWJsaW5nIHdpbGwgb25s eSB1cGRhdGUgdGhlIGZpbGUKLSAgIDxmaWxlbmFtZT5wZ19jb250cm9sPC9maWxlbmFtZT4uCi0g IDwvcGFyYT4KKyAgIDxmaWxlbmFtZT5wZ19jb250cm9sPC9maWxlbmFtZT4uIER1cmluZyBlbmFi bGluZywKKyAgIHRoZSA8ZmlsZW5hbWU+cGdfY29udHJvbDwvZmlsZW5hbWU+IDwvcGFyYT4gZmls ZSBpcyByZW5hbWVkIGluIG9yZGVyIHRvCisgICBwcmV2ZW50IGFjY2lkZW50YWxseSBzdGFydGlu ZyB0aGUgY2x1c3Rlci4KICA8L3JlZnNlY3QxPgogCiAgPHJlZnNlY3QxPgpkaWZmIC0tZ2l0IGEv c3JjL2Jpbi9wZ19jaGVja3N1bXMvcGdfY2hlY2tzdW1zLmMgYi9zcmMvYmluL3BnX2NoZWNrc3Vt cy9wZ19jaGVja3N1bXMuYwppbmRleCA4NTJkZGFmYzk0Li41OTBmYjkyNWU4IDEwMDY0NAotLS0g YS9zcmMvYmluL3BnX2NoZWNrc3Vtcy9wZ19jaGVja3N1bXMuYworKysgYi9zcmMvYmluL3BnX2No ZWNrc3Vtcy9wZ19jaGVja3N1bXMuYwpAQCAtNTUsNiArNTUsMTIgQEAgdHlwZWRlZiBlbnVtCiAj ZGVmaW5lIFBHX1RFTVBfRklMRVNfRElSICJwZ3NxbF90bXAiCiAjZGVmaW5lIFBHX1RFTVBfRklM RV9QUkVGSVggInBnc3FsX3RtcCIKIAorI2lmbmRlZiBXSU4zMgorI2RlZmluZSBwZ19tdl9maWxl CQkJcmVuYW1lCisjZWxzZQorI2RlZmluZSBwZ19tdl9maWxlCQkJcGdyZW5hbWUKKyNlbmRpZgor CiBzdGF0aWMgUGdDaGVja3N1bU1vZGUgbW9kZSA9IFBHX01PREVfQ0hFQ0s7CiAKIHN0YXRpYyBj b25zdCBjaGFyICpwcm9nbmFtZTsKQEAgLTg4LDYgKzk0LDcgQEAgdXNhZ2Uodm9pZCkKICAqLwog c3RhdGljIGNvbnN0IGNoYXIgKmNvbnN0IHNraXBbXSA9IHsKIAkicGdfY29udHJvbCIsCisJInBn X2NvbnRyb2wucGdfY2hlY2tzdW1zX2luX3Byb2dyZXNzIiwKIAkicGdfZmlsZW5vZGUubWFwIiwK IAkicGdfaW50ZXJuYWwuaW5pdCIsCiAJIlBHX1ZFUlNJT04iLApAQCAtMzA2LDYgKzMxMyw4IEBA IG1haW4oaW50IGFyZ2MsIGNoYXIgKmFyZ3ZbXSkKIAl9OwogCiAJY2hhcgkgICAqRGF0YURpciA9 IE5VTEw7CisJY2hhcgkJb2xkX2NvbnRyb2xmaWxlX3BhdGhbTUFYUEdQQVRIXTsKKwljaGFyCQlu ZXdfY29udHJvbGZpbGVfcGF0aFtNQVhQR1BBVEhdOwogCWludAkJCWM7CiAJaW50CQkJb3B0aW9u X2luZGV4OwogCWJvb2wJCWNyY19vazsKQEAgLTQ0MCw2ICs0NDksMjMgQEAgbWFpbihpbnQgYXJn YywgY2hhciAqYXJndltdKQogCQlleGl0KDEpOwogCX0KIAorCS8qCisJICogRGlzYWJsZSBjbHVz dGVyIGJ5IHJlbmFtaW5nIHBnX2NvbnRyb2wgd2hlbiBlbmFibGluZyBjaGVja3N1bXMgc28KKwkg KiB0aGF0IGl0IGNhbm5vdCBiZSBzdGFydGVkIGJ5IGFjY2lkZW50IGR1cmluZyB0aGUgb3BlcmF0 aW9uCisJICovCisJaWYgKG1vZGUgPT0gUEdfTU9ERV9FTkFCTEUpCisJeworCQlzbnByaW50Zihv bGRfY29udHJvbGZpbGVfcGF0aCwgc2l6ZW9mKG9sZF9jb250cm9sZmlsZV9wYXRoKSwKKwkJCQkg IiVzL2dsb2JhbC9wZ19jb250cm9sIiwgRGF0YURpcik7CisJCXNucHJpbnRmKG5ld19jb250cm9s ZmlsZV9wYXRoLCBzaXplb2YobmV3X2NvbnRyb2xmaWxlX3BhdGgpLAorCQkJCSAiJXMvZ2xvYmFs L3BnX2NvbnRyb2wucGdfY2hlY2tzdW1zX2luX3Byb2dyZXNzIiwgRGF0YURpcik7CisJCWlmIChw Z19tdl9maWxlKG9sZF9jb250cm9sZmlsZV9wYXRoLCBuZXdfY29udHJvbGZpbGVfcGF0aCkgIT0g MCkKKwkJeworCQkJZnByaW50ZihzdGRlcnIsIF8oIiVzOiB1bmFibGUgdG8gcmVuYW1lICVzIHRv ICVzLlxuIiksIHByb2duYW1lLCBvbGRfY29udHJvbGZpbGVfcGF0aCwgbmV3X2NvbnRyb2xmaWxl X3BhdGgpOworCQkJZXhpdCgxKTsKKwkJfQorCX0KKwogCS8qIE9wZXJhdGUgb24gYWxsIGZpbGVz IGlmIGNoZWNraW5nIG9yIGVuYWJsaW5nIGNoZWNrc3VtcyAqLwogCWlmIChtb2RlID09IFBHX01P REVfQ0hFQ0sgfHwgbW9kZSA9PSBQR19NT0RFX0VOQUJMRSkKIAl7CkBAIC00NjYsNiArNDkyLDE2 IEBAIG1haW4oaW50IGFyZ2MsIGNoYXIgKmFyZ3ZbXSkKIAkgKi8KIAlpZiAobW9kZSA9PSBQR19N T0RFX0VOQUJMRSB8fCBtb2RlID09IFBHX01PREVfRElTQUJMRSkKIAl7CisKKwkJLyogTW92ZSBi YWNrIHBnX2NvbnRyb2wgKi8KKwkJaWYgKG1vZGUgPT0gUEdfTU9ERV9FTkFCTEUpCisJCXsKKwkJ CWlmIChwZ19tdl9maWxlKG5ld19jb250cm9sZmlsZV9wYXRoLCBvbGRfY29udHJvbGZpbGVfcGF0 aCkgIT0gMCkKKwkJCXsKKwkJCQlmcHJpbnRmKHN0ZGVyciwgXygiJXM6IHVuYWJsZSB0byByZW5h bWUgJXMgdG8gJXMuXG4iKSwgcHJvZ25hbWUsIG5ld19jb250cm9sZmlsZV9wYXRoLCBvbGRfY29u dHJvbGZpbGVfcGF0aCk7CisJCQkJZXhpdCgxKTsKKwkJCX0KKwkJfQogCQkvKiBVcGRhdGUgY29u dHJvbCBmaWxlICovCiAJCUNvbnRyb2xGaWxlLT5kYXRhX2NoZWNrc3VtX3ZlcnNpb24gPQogCQkJ KG1vZGUgPT0gUEdfTU9ERV9FTkFCTEUpID8gUEdfREFUQV9DSEVDS1NVTV9WRVJTSU9OIDogMDsK LS0gCjIuMTEuMAoK --=-kblYo35aefjqgrQ2tUtK--