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 1qFMRa-0003gT-4P for pgsql-hackers@arkaria.postgresql.org; Fri, 30 Jun 2023 22:18:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qFMRX-0002WY-V1 for pgsql-hackers@arkaria.postgresql.org; Fri, 30 Jun 2023 22:18:19 +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 1qFMRX-0002WO-GP for pgsql-hackers@lists.postgresql.org; Fri, 30 Jun 2023 22:18:19 +0000 Received: from mail-wr1-x432.google.com ([2a00:1450:4864:20::432]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qFMRU-001Ltr-MD for pgsql-hackers@postgresql.org; Fri, 30 Jun 2023 22:18:18 +0000 Received: by mail-wr1-x432.google.com with SMTP id ffacd0b85a97d-3094910b150so2730596f8f.0 for ; Fri, 30 Jun 2023 15:18:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=postgres.ai; s=google; t=1688163495; x=1690755495; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=CtBddhwnIwIagxi+ATrvCcbx+B6pPpe+7JvfcR0sKAs=; b=Fvsh31j9/kRhDfkpH4Lv6T8a8BtvqiMWnBvXIJPgUZCUl6n1BCKox4lh7eqWhemxKs R3/Tl8aIdPAsSzCwHl1VrnCp+dx+jGZNpLXBchEKQLdSmO1DItKW+shJQF+SdHu8sfYl 8Fcd5ZF6/2JUH2ESZRW9th0avOQFoIfzxgcDu77gwcKM1V8N/T7u6nannxOhcRq1yKv3 zI8tZf/KOxTlhuatYEW98sGhV/27OjyulboYT7bv1sUlhZMpzzKCIQFbJq3U4EWSs+aF 1ejarVexxUYvUqL7oHCbqCbT3vkOwAe2MKMVEJAZHSNoMMk6HbQmou88MJEe8L1ywywO hTng== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1688163495; x=1690755495; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=CtBddhwnIwIagxi+ATrvCcbx+B6pPpe+7JvfcR0sKAs=; b=Y925H2c8svZotEQWA4DP3Xwd1Bgoi4KYxQ9W80cbSaAlToLxi4A+GeOY7w2zu6jrXK zm/juUyx2+DxqVRKCRN04b62vMZKk+Z7UjqjN66FJ/cKz47tT6ApNR4QGroMpHeDW87l Al+TS1/xaLdyt1mamvKQ3Uh/s7nNknn/uyc1cLMh4ljoXXFg9D7nORPInA+aXiPUJHXA pPNLhsy+S/kC+qKcoFRD4a1sT8XUgO0WxKMaCeYvqJIc3L7yjv7UaYD4q1AZ+493eR0g tuPDzKu0Y68s8LaN953It7Uo9a10Keb6ZZqR7sttOta29hYV+GMOyUCxYczWnl8MQetX QY8Q== X-Gm-Message-State: ABy/qLabjLIdvFg9m40OwHRFn1EoMhrOa/riThgfaYBY5KPXs1zXWNM+ to6Fc/q+O5I3Z4wQQwm1H39B8aJwb+S5Go8I/Ttq8g== X-Google-Smtp-Source: APBJJlF2498/EoShgu32Y9RFEF2euBK3mYLFIuTtnkAQ1V8Lp7Y1DH8IKRZtMrxwPpGu7NPK029EV7uMOr6WZQd66eo= X-Received: by 2002:adf:e68d:0:b0:314:1582:6eb4 with SMTP id r13-20020adfe68d000000b0031415826eb4mr2730761wrm.46.1688163494487; Fri, 30 Jun 2023 15:18:14 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Nikolay Samokhvalov Date: Fri, 30 Jun 2023 15:18:03 -0700 Message-ID: Subject: Re: pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption? To: Bruce Momjian Cc: Robert Haas , Stephen Frost , pgsql-hackers@postgresql.org Content-Type: multipart/alternative; boundary="000000000000697be905ff603196" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000697be905ff603196 Content-Type: text/plain; charset="UTF-8" On Fri, Jun 30, 2023 at 14:33 Bruce Momjian wrote: > On Fri, Jun 30, 2023 at 04:16:31PM -0400, Robert Haas wrote: > > I'm not quite clear on how Nikolay got into trouble here. I don't > > think I understand under exactly what conditions the procedure is > > reliable and under what conditions it isn't. But there is no way in > > heck I would ever advise anyone to use this procedure on a database > > they actually care about. This is a great party trick or something to > > show off in a lightning talk at PGCon, not something you ought to be > > doing with valuable data that you actually care about. > > Well, it does get used, and if we remove it perhaps we can have it on > our wiki and point to it from our docs. In my case, we performed some additional writes on the primary before running "pg_upgrade -k" and we did it *after* we shut down all the standbys. So those changes were not replicated and then "rsync --size-only" ignored them. (By the way, that cluster has wal_log_hints=on to allow Patroni run pg_rewind when needed.) But this can happen with anyone who follows the procedure from the docs as is and doesn't do any additional steps, because in step 9 "Prepare for standby server upgrades": 1) there is no requirement to follow specific order to shut down the nodes - "Streaming replication and log-shipping standby servers can remain running until a later step" should probably be changed to a requirement-like "keep them running" 2) checking the latest checkpoint position with pg_controldata now looks like a thing that is good to do, but with uncertainty purpose -- it does not seem to be used to support any decision - "There will be a mismatch if old standby servers were shut down before the old primary or if the old standby servers are still running" should probably be rephrased saying that if there is mismatch, it's a big problem So following the steps as is, if some writes on the primary are not replicated (due to whatever reason) before execution of pg_upgrade -k + rsync --size-only, then those writes are going to be silently lost on standbys. I wonder, if we ensure that standbys are fully caught up before upgrading the primary, if we check the latest checkpoint positions, are we good to use "rsync --size-only", or there are still some concerns? It seems so to me, but maybe I'm missing something. > -- Thanks, Nikolay Samokhvalov Founder, Postgres.ai --000000000000697be905ff603196 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Fri, Jun 30, 2023 at 14:33 Bruce Momjian <bruce@momjian.us> wrote:
On Fri, Jun 30, 2023 at 04:16:31PM -0400, Robert Haas wro= te:=C2=A0=C2=A0=C2=A0
> I'm not quite clear on how Nikolay got into trouble here. I don= 9;t
> think I understand under exactly what conditions the procedure is
> reliable and under what conditions it isn't. But there is no way i= n
> heck I would ever advise anyone to use this procedure on a database > they actually care about. This is a great party trick or something to<= br> > show off in a lightning talk at PGCon, not something you ought to be > doing with valuable data that you actually care about.

Well, it does get used, and if we remove it perhaps we can have it on
our wiki and point to it from our docs.

<= /div>
In my case, we performed some additional writes on t= he primary before running "pg_upgrade -k" and we did it *after* w= e shut down all the standbys. So those changes were not replicated and then= "rsync --size-only" ignored them. (By the way, that cluster has = wal_log_hints=3Don to allow Patroni run pg_rewind when needed.)

But this can happen with anyone who= follows the procedure from the docs as is and doesn't do any additiona= l steps, because in step 9 "Prepare for standby server upgrades":=

1) there is no requirem= ent to follow specific order to shut down the nodes
= =C2=A0 =C2=A0- "Streaming replication and log-shipping standby servers= can remain running until a later step" should probably be changed to = a requirement-like "keep them running"
2) checking the latest checkpoint position with pg= _controldata now looks like a thing that is good to do, but with uncertaint= y purpose -- it does not seem to be used to support any decision
=C2=A0 - "There will be a mismatch if old standby servers= were shut down before the old primary or if the old standby servers are st= ill running" should probably be rephrased saying that if there is mism= atch, it's a big problem

So following the steps as i= s, if some writes on the primary are not replicated (due to whatever reason= ) before execution of pg_upgrade -k + rsync --size-only, then those writes = are going to be silently lost on standbys.

I wonder,= if we ensure that standbys are fully caught up before upgrading the primar= y, if we check the latest checkpoint positions, are we good to use "rs= ync --size-only", or there are still some concerns? It seems so to me,= but maybe I'm missing something.
--

Thanks,
Nikolay Samokhvalov
Founder, Postgres.ai
--000000000000697be905ff603196--