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 1scl9q-00AGpX-UI for pgsql-hackers@arkaria.postgresql.org; Sat, 10 Aug 2024 12:25:19 +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 1scl9p-008VDh-I3 for pgsql-hackers@arkaria.postgresql.org; Sat, 10 Aug 2024 12:25:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1scl9o-008VDY-RF for pgsql-hackers@lists.postgresql.org; Sat, 10 Aug 2024 12:25:17 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1scl9h-0042GZ-7B for pgsql-hackers@lists.postgresql.org; Sat, 10 Aug 2024 12:25:15 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-a7de4364ca8so336597066b.2 for ; Sat, 10 Aug 2024 05:25:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=garret-ru.20230601.gappssmtp.com; s=20230601; t=1723292707; x=1723897507; darn=lists.postgresql.org; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=jKEv2jLx6KevM8c6M4SwWcDRQS/1wDuEGUdY4RFghnc=; b=uM5J3ZcgMw425KhyIhjM/OtMuKSg0gXGCMjM+i8CUao+eE3Nlfop5z0gYazhqEe9b4 PFA2q4bIwfFM1wV+r8/thy1iCRn/PlGf9U5pstGVtWDWp07nWprplIthQklwk2LMqYg8 sIR8DNc+DXLS95bXycnvG8YlINTJXA2N0/0hBo08dbMNB5NnueaYFRtcC23VT2mPbSa7 nahQwRc7rTj4J/PkC8XFqxe1s6PUEYFx6c1k/DLyDQiszL65wdORsA0Z18/x0FOfWfNl sp2SkivQDCaNY6YrIdkjwTcc/s243UJ+boLK6f4sqt0l1PEQywPUO1YNCeKbWaVWRKVe +DSw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723292707; x=1723897507; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=jKEv2jLx6KevM8c6M4SwWcDRQS/1wDuEGUdY4RFghnc=; b=rIwS0XU/sJNJNWq/azVlNW8Y+T7oZCWqJHPG+Po3FWXrtz8eoW09QwqxCSM20Z6Qvq BjNS5bo6LcW54AsW8votLT/awGJoRNdgWXSIQI53XKXXQCk41uF99Ftu4oiacZArA8Nz Wy9S+cgelgSamH04jHC+D2MdaQgzwe7japoxrHoeDNW7l8rJXOf7OgkLxsSdio2Y2nCd mVbUPeUbN4icIhWl2DIvsiGZtw1Ds9qrRwwC6qMYEDQyKLJ/G+U/M37WhkoVmQ7vExnw ibZzEeMpZ6/Cjnn2EseMwiWCc8mvzqQoUJMOpKT/TsgVrjyleFACPaUb1pNQQSL88Ci/ ZpEA== X-Gm-Message-State: AOJu0Yz86BSLzlHsp5Z3zGer07wHH1Q8iKAfiTQbMbTTctk+Ko6dgStU 7aLhiUatxMcuLKeBbJtWvcYRFqpehm16H5VXI5mFQenR5NAXSuKsLSckibRKwwQx4R4xcwn7gos = X-Google-Smtp-Source: AGHT+IGYcl0ThCI44F6kgOBxQRsD68syxJGu6laTxoQF0UlD9ICL/FNfh7a/POGZ10EfZlEbg1FhKg== X-Received: by 2002:a17:907:d862:b0:a7a:af5d:f313 with SMTP id a640c23a62f3a-a80aa598659mr318293266b.22.1723292706485; Sat, 10 Aug 2024 05:25:06 -0700 (PDT) Received: from [192.168.2.14] ([217.175.216.56]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-a80bb243d78sm64825566b.224.2024.08.10.05.25.05 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 10 Aug 2024 05:25:05 -0700 (PDT) Content-Type: multipart/mixed; boundary="------------LiLI7YKs2Kcvs8Z90KDt2Joz" Message-ID: <09c4ab4c-fcfb-4236-a8f3-62c726586bef@garret.ru> Date: Sat, 10 Aug 2024 15:25:04 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: en-US To: PostgreSQL Hackers From: Konstantin Knizhnik Subject: asynchronous commit&synchronous replication 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. --------------LiLI7YKs2Kcvs8Z90KDt2Joz Content-Type: multipart/alternative; boundary="------------Hp00o19XGtHjOdrCDrOG5KpH" --------------Hp00o19XGtHjOdrCDrOG5KpH Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi hackers, Logical replication apply worker by default switches off asynchronous commit. Cite from documentation of subscription parameters: ``` |synchronous_commit|(|enum|) The value of this parameter overrides thesynchronous_commit setting within this subscription's apply worker processes. The default value is|off|. It is safe to use|off|for logical replication: If the subscriber loses transactions because of missing synchronization, the data will be sent again from the publisher. ``` So subscriber can confirm transaction which are not persisted. But consider a PostgreSQL HA setup with: * primary node * (cold) standby node streaming WAL from the primary * synchronous replication enabled, so that you get zero data loss if the primary dies * the primary/standby cluster is a subscriber to a remote PostgreSQL server It can happen that: * the primary streams some transactions from the remote PostgreSQL, with logical replication * the primary crashes. Failover to the standby happens * the standby tries to stream the transactions from the subscriber. But some transactions are missed, because the primary had already reported a higher flush LSN. I wonder if such scenario is considered as an "expected behavior" or "bug" by community? It seems to be quite easily fixed (see attached patch). So should we take in account sync replication in LR apply worker or not? Thanks to Heikki Linnakangas for describing this scenario and Arseny Sher for providing the patch. --------------Hp00o19XGtHjOdrCDrOG5KpH Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Hi hackers,

Logical replication apply worker by default switches off asynchronous commit. Cite from documentation of subscription parameters:

```

synchronous_commit (enum) 

The value of this parameter overrides the synchronous_commit setting within this subscription's apply worker processes. The default value is off.
It is safe to use off for logical replication: If the subscriber loses transactions because of missing synchronization, the data will be sent again from the publisher.

```

So subscriber can confirm transaction which are not persisted. But consider a PostgreSQL HA setup with:

  • primary node
  • (cold) standby node streaming WAL from the primary
  • synchronous replication enabled, so that you get zero data loss if the primary dies
  • the primary/standby cluster is a subscriber to a remote PostgreSQL server
It can happen that:
  • the primary streams some transactions from the remote PostgreSQL, with logical replication
  • the primary crashes. Failover to the standby happens
  • the standby tries to stream the transactions from the subscriber. But some transactions are missed, because the primary had already reported a higher flush LSN.


I wonder if such scenario is considered as an "expected behavior" or "bug" by community?
It seems to be quite easily fixed (see attached patch).

So should we take in account sync replication in LR apply worker or not?

Thanks to Heikki Linnakangas <hlinnaka@iki.fi> for describing this scenario and Arseny Sher <ars@neon.tech> for providing the patch.

--------------Hp00o19XGtHjOdrCDrOG5KpH-- --------------LiLI7YKs2Kcvs8Z90KDt2Joz Content-Type: text/plain; charset=UTF-8; name="sync_replication.patch" Content-Disposition: attachment; filename="sync_replication.patch" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL2xvZ2ljYWwvd29ya2VyLmMg Yi9zcmMvYmFja2VuZC9yZXBsaWNhdGlvbi9sb2dpY2FsL3dvcmtlci5jCmluZGV4IDI0NWU5 YmU2ZjIuLmU4NzhkODgyYWUgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9u L2xvZ2ljYWwvd29ya2VyLmMKKysrIGIvc3JjL2JhY2tlbmQvcmVwbGljYXRpb24vbG9naWNh bC93b3JrZXIuYwpAQCAtMTczLDYgKzE3Myw3IEBACiAjaW5jbHVkZSAicmVwbGljYXRpb24v bG9naWNhbHdvcmtlci5oIgogI2luY2x1ZGUgInJlcGxpY2F0aW9uL29yaWdpbi5oIgogI2lu Y2x1ZGUgInJlcGxpY2F0aW9uL3dhbHJlY2VpdmVyLmgiCisjaW5jbHVkZSAicmVwbGljYXRp b24vd2Fsc2VuZGVyX3ByaXZhdGUuaCIKICNpbmNsdWRlICJyZXBsaWNhdGlvbi93b3JrZXJf aW50ZXJuYWwuaCIKICNpbmNsdWRlICJyZXdyaXRlL3Jld3JpdGVIYW5kbGVyLmgiCiAjaW5j bHVkZSAic3RvcmFnZS9idWZmaWxlLmgiCkBAIC0zNDE1LDYgKzM0MTYsMTcgQEAgZ2V0X2Zs dXNoX3Bvc2l0aW9uKFhMb2dSZWNQdHIgKndyaXRlLCBYTG9nUmVjUHRyICpmbHVzaCwKIAlk bGlzdF9tdXRhYmxlX2l0ZXIgaXRlcjsKIAlYTG9nUmVjUHRyCWxvY2FsX2ZsdXNoID0gR2V0 Rmx1c2hSZWNQdHIoTlVMTCk7CiAKKwkvKgorCSAqIElmIHN5bmNocm9ub3VzIHJlcGxpY2F0 aW9uIGlzIGNvbmZpZ3VyZWQsIHRha2UgaW50byBhY2NvdW50IGl0cyBwb3NpdGlvbi4KKwkg Ki8KKwlpZiAoU3luY1JlcFN0YW5kYnlOYW1lcyAhPSBOVUxMICYmIFN5bmNSZXBTdGFuZGJ5 TmFtZXNbMF0gIT0gJ1wwJykKKwl7CisJCS8qIGFzc3VtZXMgdTY0IHJlYWQgaXMgYXRvbWlj ICovCisJCVhMb2dSZWNQdHIgIHN5bmNfcmVwX2ZsdXNoID0gV2FsU25kQ3RsLT5sc25bU1lO Q19SRVBfV0FJVF9GTFVTSF07CisKKwkJbG9jYWxfZmx1c2ggPSBNaW4obG9jYWxfZmx1c2gs IHN5bmNfcmVwX2ZsdXNoKTsKKwl9CisKIAkqd3JpdGUgPSBJbnZhbGlkWExvZ1JlY1B0cjsK IAkqZmx1c2ggPSBJbnZhbGlkWExvZ1JlY1B0cjsKIAo= --------------LiLI7YKs2Kcvs8Z90KDt2Joz--