Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qwS6r-00Blme-Qv for pgsql-general@arkaria.postgresql.org; Fri, 27 Oct 2023 19:03:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qwS6o-000bae-U6 for pgsql-general@arkaria.postgresql.org; Fri, 27 Oct 2023 19:03:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qwS6o-000baV-J6 for pgsql-general@lists.postgresql.org; Fri, 27 Oct 2023 19:03:02 +0000 Received: from mail-yb1-xb2f.google.com ([2607:f8b0:4864:20::b2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qwS6m-003CUR-08 for pgsql-general@lists.postgresql.org; Fri, 27 Oct 2023 19:03:02 +0000 Received: by mail-yb1-xb2f.google.com with SMTP id 3f1490d57ef6-d9a58aa4983so1779219276.0 for ; Fri, 27 Oct 2023 12:02:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1698433378; x=1699038178; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=D+BgdSefDg+SCaBK5NpgFqC980veZ4bC7aN4WFLicbE=; b=LgqnXU6BnLs2aDdOkCIsNMFFaYUiT6WjzbKXmtD+R+qlSMgilKPpZixIQodrXcJgkl +Zyv96VFXMlCHYKcBWsL3kFW+NenQ0+S22BWF+Ja4P4tjKXcU3D86bT4bVruID/AAAhx fDOi1MrWpsTt0v6E3d+SS9wsFb236CTuhaLoY//54muFko3ZPskhmVyzQO9PMI6Cy+LO izboEP3pz/swwDRyEmlkXVCaVXRjQApOTnpbB7fVlRJjJHS+ItPgWWnFWCRnTvH6qvUN A+hnUUYGE18LbSBCt7loA+utdsem4UNf3JqyZEFoEFUHwUYragaqn+IUaiKc6Jw/y42Q YpkA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1698433378; x=1699038178; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=D+BgdSefDg+SCaBK5NpgFqC980veZ4bC7aN4WFLicbE=; b=KqcmuCNTE8GnljEcuQYqNUkj75PH1+IkhgxH7mJUuoWBTnMMB8EbTnkucUZyOAaFUN KLJzovsu5c6bfsF9D8Lx0ICLXXeaARtJ9SOsh2mxPMFy1jScFNACcLPeyzsbJvLB5npD UToYQvBWB/AqQesS55abwC3aA55VgrTZHFdqVr8vr2QwQJNPqfeU+Xek6bcV6x+943il zrciaHdBpPmzL71VbtcKbf30wnMOV4wYukFguNdiPNWfax04JIZSiP0Sf9+kSCQBFPN5 P+bbIvIQFIVNVx9xVsDcz9mAAnVVUcT4PHkt56mO2VNADIdiLywx7pZVPSrxpuqj33TA BKzg== X-Gm-Message-State: AOJu0Yyo6gPXGAYUGQnfrRh7tZQLVIRd0RXAuOm59+D/OewEPqPuHIhf 6qgWpmr4hyc0uZfsziAe2LtJdTKSZtE= X-Google-Smtp-Source: AGHT+IFoAk7oJFg7jmzH+YOFGZ948OkurniK3niV5H1megrBjlever/cmXzmahF3MzL+0nfET6sjIQ== X-Received: by 2002:a81:ae22:0:b0:5ad:7fc2:4787 with SMTP id m34-20020a81ae22000000b005ad7fc24787mr4263308ywh.15.1698433377883; Fri, 27 Oct 2023 12:02:57 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:e0e7:f6ad:239e:5da2? ([2600:1700:bd2c:b810:e0e7:f6ad:239e:5da2]) by smtp.googlemail.com with ESMTPSA id z195-20020a0dd7cc000000b005a7b785f66bsm939340ywd.39.2023.10.27.12.02.57 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 27 Oct 2023 12:02:57 -0700 (PDT) Message-ID: Date: Fri, 27 Oct 2023 14:02:57 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: pg_checksums? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <22272207-E6E5-4DD5-9BD4-5F6B647E2C3B@gmail.com> From: Ron In-Reply-To: <22272207-E6E5-4DD5-9BD4-5F6B647E2C3B@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 10/27/23 13:34, Paul Förster wrote: > Hi, > > I have a few questions about pg_checksums. > > Long story short, checksums were disabled in our environment but are getting enabled now (again!) because of block corruptions which destroyed a few databases in a database cluster. And before you say "told you so", the decision to disable checksums was not mine. Some people just have to learn the hard way. > > Anyway, re-enabling data checksums creates a few questions: > > a) why isn't it possible to enable checksumming while a database cluster is up? Data might be changing. > b) why isn't it possible to check whether checksums are enabled or not? (This is my tiny test instance.) $ pg_controldata | grep checksum Data page checksum version:           0 postgres=# show data_checksums;  data_checksums ----------------  off (1 row) > c) in a Patroni cluster consisting of a primary and a sync standby, is it safe to enable checksumming in the replica, then switch over and enable it in the ex-primary, i.e. now new replica, without any penalty? Or do I have to perform a reinit to really get them in sync again, though paronictl happily reports them to be in sync? > > d) how long does it take to enable checksums in general? Minimizing down-time is crucial. Does it depend on the database cluster size, or the number of files it uses, or what can be taken as a criterion to estimate then necessary down-time. > > Thanks in advance for your insights. > > Cheers > Paul > -- Born in Arizona, moved to Babylonia.