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 1oq6hb-00078W-9G for pgsql-admin@arkaria.postgresql.org; Wed, 02 Nov 2022 05:54:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oq6ha-0002KI-1V for pgsql-admin@arkaria.postgresql.org; Wed, 02 Nov 2022 05:54:14 +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 1oq6hZ-0002K9-LF for pgsql-admin@lists.postgresql.org; Wed, 02 Nov 2022 05:54:13 +0000 Received: from mail-ot1-x334.google.com ([2607:f8b0:4864:20::334]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oq6hV-0004mu-Ni for pgsql-admin@lists.postgresql.org; Wed, 02 Nov 2022 05:54:12 +0000 Received: by mail-ot1-x334.google.com with SMTP id cn2-20020a056830658200b0066c74617e3dso800597otb.2 for ; Tue, 01 Nov 2022 22:54:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=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=mMFw8Dmst7t0UyakHBA8ROFJs1efEqLUqwDzG7CEP/8=; b=KYi1+PY2YYh/FOkPduVZ6C/fge6irKlnqqpAmO43cUsaQo0Zy7SpIkVaDUoUH3SNO+ s0EKZgmj9qaffEbjNSm6fSnRkMP931uDSTVc6PU7ahXk9Ygu7jWYAgzFmLBcvG9pYUXu HviAstaT+1keUwBTZP2u39ezPx70FOrHnMYTG6tIkaZAl3RnhaM137Rp2q93ImKPWEFE 3AWGkNO3WPhYR2nu8Fpsl5OVRs2v9apXN4QGXNSCju0ECSUs6Lo3tNYt1hPFIzMkY++1 dx/citgR21dArRhDJbKCwPJyZFVL8s0PrM9FI4ULGfNKXGylZsyVMc4Z1OfjPk3ZdtPg o2Xw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=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=mMFw8Dmst7t0UyakHBA8ROFJs1efEqLUqwDzG7CEP/8=; b=yL8TqSydhlbsiVAIZ4DINfVgah6j9DXOzhCXymUoe1D1VP64EsE/ydheYi4dWoW1lc mLH3vTrdWDMCYs9AunyAT+7/Ua4jpv34NQNXjaJHIrTlg+tgEMeW7abGuFlBUCv4Ip44 TvGnPutFZKqIXvMSHDRLmGAtkqK8uq+WzdN2zJiMebRj8vKUAKEy1CAJ0NshvQar1RiK bPb1OBK5xYXYPNlxUNsYjVrXHRlQOy9vbgrUgbyC1ZST4xRSPNtCHKjFFTPkhi2tBR9v iMo4y3J0kYQrbcnJpwINTE4bsR/b/7BZCjy0kPkUXopHxisdMcxKwo/gIdKUP08puRO+ liwg== X-Gm-Message-State: ACrzQf2AeIWuryYC7DXe7J52sX6HKOBhiWc3e8D0IfroFhZHYUEghbCr RAoDP9zpfVHfL7wan/5sHAe/b1lxawE= X-Google-Smtp-Source: AMsMyM4E1VExMAIQMsfUyWKwXPfNtKkyugYQ9r0H8HmZjkVnY4dTojliu8egcay4+NtTK7xMsTMKnA== X-Received: by 2002:a9d:7995:0:b0:66c:5375:4001 with SMTP id h21-20020a9d7995000000b0066c53754001mr7174730otm.351.1667368448706; Tue, 01 Nov 2022 22:54:08 -0700 (PDT) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id g13-20020a056870d20d00b0012c21a64a76sm5550888oac.24.2022.11.01.22.54.07 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 01 Nov 2022 22:54:08 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------J00y0m5a0wHiCECeAmSXZlI0" Message-ID: <54ba769f-7361-5df9-75fa-43669faf701e@gmail.com> Date: Wed, 2 Nov 2022 00:54:06 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: [External] Re: WAL Replication query Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: <88faef33e966796226022d6e7f3e32ad6c59fcfd.camel@cybertec.at> <8f9e6c92-4daa-a543-6825-05a3503f4296@gmail.com> <42306ea2-74c2-a95e-551d-e1d16946c083@gmail.com> From: Ron In-Reply-To: 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. --------------J00y0m5a0wHiCECeAmSXZlI0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/1/22 23:00, KK CHN wrote: [snip] > So there will be an inconsistency in the data  between the current  > Primary  ( promoted standby)  and my recovered /repaired old server ( > which was primary before the crash). > >    How to handle this data inconsistency or what best mechanism needs to > be implemented if you were me in this use case scenario? Before promoting Secondary to New Primary, I'd record it's current pg_stat_replication.replay_lsn value somewhere and then compare it to the relevant LSN on Old Primary when it comes back up. If they're the same, then there's no data inconsistency. If there is, then you've got to manually check tables, since every application and database structure is different -- Angular momentum makes the world go 'round. --------------J00y0m5a0wHiCECeAmSXZlI0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 11/1/22 23:00, KK CHN wrote:
[snip]
So there will be an inconsistency in the data  between the current  Primary  ( promoted standby)  and my recovered /repaired old server ( which was primary before the crash).  

   How to handle this data inconsistency or what best mechanism needs to be implemented if you were me in this  use case scenario?  

Before promoting Secondary to New Primary, I'd record it's current pg_stat_replication.replay_lsn value somewhere and then compare it to the relevant LSN on Old Primary when it comes back up.

If they're the same, then there's no data inconsistency.

If there is, then you've got to manually check tables, since every application and database structure is different

--
Angular momentum makes the world go 'round.
--------------J00y0m5a0wHiCECeAmSXZlI0--