Content-Type: multipart/alternative;
 boundary="------------3PU0Gm5w5r0lJRXwmPI0Cogz"
Message-ID: <42306ea2-74c2-a95e-551d-e1d16946c083@gmail.com>
Date: Tue, 1 Nov 2022 08:49:52 -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: 
 <CAKgGyB-SMT0RxeoifAfjkRx6RHEOgpn7sf8F_zD1DPsKydTu=w@mail.gmail.com>
 <88faef33e966796226022d6e7f3e32ad6c59fcfd.camel@cybertec.at>
 <MW4PR20MB4525ED0222DDE9FED0519D8FF3369@MW4PR20MB4525.namprd20.prod.outlook.com>
 <c1b6b89d6b89706acaeca9b4d45958f4739c4f28.camel@cybertec.at>
 <8f9e6c92-4daa-a543-6825-05a3503f4296@gmail.com>
 <f78c361061449be8402922cd5ed7f5782f65aab1.camel@cybertec.at>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: <f78c361061449be8402922cd5ed7f5782f65aab1.camel@cybertec.at>
Archived-At: 
 <https://www.postgresql.org/message-id/42306ea2-74c2-a95e-551d-e1d16946c083%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------3PU0Gm5w5r0lJRXwmPI0Cogz
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 11/1/22 08:34, Laurenz Albe wrote:
> On Tue, 2022-11-01 at 02:43 -0500, Ron wrote:
>> On 11/1/22 01:53, Laurenz Albe wrote:
>>> On Tue, 2022-11-01 at 06:44 +0000, Sacheen Birhade wrote:
>>>> If replication is in asynchronous then there will be data loss, right Laurenz?
>>> Why?  The data will perhaps show up on the standby a little later, but why is
>>> that data loss?  Remember that the question was about replication, and there
>>> was no mention of failover.
>>   No, the question was about a crash during replication: OP (not Sacheen, unless that person
>> is using two email addresses) explicitly asked "When the primary crashes  due an unforeseen
>> reason (what happens)?"
>>   
>>   If the two database systems are really busy, and especially if the network connection
>> isn't fast enough, async replication means there might be some transactions committed
>> on Primary which were queued for transmission, but hadn't yet made it to the Secondary, right?
> Right.  And how does that constitute data loss?  If you start the primary again, the transaction
> will be replicated just fine.  Now if you call it *data delay*, I would agree.

Primary has /crashed/, according to OP; it's not coming back any time soon.  
And thus you promote Secondary to be New Primary, and go about  your work.  
When the Old Primary comes back up (hours or days later), you do a 
pg_basebackup to make it the New Secondary.

-- 
Angular momentum makes the world go 'round.
--------------3PU0Gm5w5r0lJRXwmPI0Cogz
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 11/1/22 08:34, Laurenz Albe wrote:<br>
    <blockquote type="cite"
      cite="mid:f78c361061449be8402922cd5ed7f5782f65aab1.camel@cybertec.at">
      <pre class="moz-quote-pre" wrap="">On Tue, 2022-11-01 at 02:43 -0500, Ron wrote:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">On 11/1/22 01:53, Laurenz Albe wrote:
</pre>
        <blockquote type="cite">
          <pre class="moz-quote-pre" wrap="">On Tue, 2022-11-01 at 06:44 +0000, Sacheen Birhade wrote:
</pre>
          <blockquote type="cite">
            <pre class="moz-quote-pre" wrap="">If replication is in asynchronous then there will be data loss, right Laurenz?
</pre>
          </blockquote>
          <pre class="moz-quote-pre" wrap="">
Why?  The data will perhaps show up on the standby a little later, but why is
that data loss?  Remember that the question was about replication, and there
was no mention of failover.
</pre>
        </blockquote>
        <pre class="moz-quote-pre" wrap="">
 No, the question was about a crash during replication: OP (not Sacheen, unless that person
is using two email addresses) explicitly asked "When the primary crashes  due an unforeseen
reason (what happens)?"
 
 If the two database systems are really busy, and especially if the network connection
isn't fast enough, async replication means there might be some transactions committed
on Primary which were queued for transmission, but hadn't yet made it to the Secondary, right?
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Right.  And how does that constitute data loss?  If you start the primary again, the transaction
will be replicated just fine.  Now if you call it *data delay*, I would agree.</pre>
    </blockquote>
    <br>
    Primary has <i>crashed</i>, according to OP; it's not coming back
    any time soon.  And thus you promote Secondary to be New Primary,
    and go about  your work.  When the Old Primary comes back up (hours
    or days later), you do a pg_basebackup to make it the New Secondary.<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------3PU0Gm5w5r0lJRXwmPI0Cogz--