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 1sH9Gq-003tk4-Ff for pgsql-general@arkaria.postgresql.org; Tue, 11 Jun 2024 21:43:13 +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 1sH9Go-00ASRd-V0 for pgsql-general@arkaria.postgresql.org; Tue, 11 Jun 2024 21:43:11 +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 1sH9Go-00ASRU-FA for pgsql-general@lists.postgresql.org; Tue, 11 Jun 2024 21:43:11 +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 1sH9Gi-000r1n-U2 for pgsql-general@lists.postgresql.org; Tue, 11 Jun 2024 21:43:10 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-a6269885572so47211666b.1 for ; Tue, 11 Jun 2024 14:43:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718142183; x=1718746983; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=u3K76WDBXUwhmd+ggXjTXSAeM49UQddnHjWMrE5I8Oc=; b=edOvvzrToNeDNqtK5W83kQVky6hoXivObEvBUv35zfLwRUQvQUpJCWexihlpffLZwf BH3+TqsluHwMnNgu4PE0KZDTRikHAwtdnup/R0o5tWO23VfjjBPqfN5Vr8jzNTWyOfS5 rnkST6JBvUByWO7MIZVjfks5LvJuCF77EHA1lhFPyTrcpShsHxYlGSdIaNgYMqhiR1fW NlMVQkZvRmxAvP8ozH4MOcaCzleRxHW8b4lIndDYUapWbLZ/Zd8J78MMRcEh1kOrlQHg quSqwVrHbZvp1Oq15iJY5QdS62ftvRf6A5Ju/xMHO8vRpnAAtNy1KLbJlDvo+Fr64V0P wVLQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718142183; x=1718746983; 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=u3K76WDBXUwhmd+ggXjTXSAeM49UQddnHjWMrE5I8Oc=; b=KFGrOVxztyV5eii1QWOtXyIgFpUAXRG/7C9hFqmbf/cT+WvcTO3oSgOdKNrXcOSAvb OkLqXLVkf3Wt3YSZwOiH7Wv2qJqTz1FtjAa40Ki47dJQYkpDmi6DMo4A4GKq7ZE7R7Id SDY4KZZIPhT9696+4/ydBn0Kg0pSoOMVzb9Lv0rf0iXwv1J0c9cbJQ5WdaTOdwngV7QP MSM8qOEAHfOs8ERGxdvGwR3/MaHukMnTJHc0sMIe6VFXnA8n8T9UgLiubh6s4GPYViGv DBjRD0vOQh+VisU6n6+vpO6FMr9YFGcLbRZ6fcqMESnEULOb7ygyKdtAFpruFKs5RV9V m73g== X-Forwarded-Encrypted: i=1; AJvYcCUEaus/kxs94TmRRG0YNQJiJhKD0sK8zUBCvl44U4AI+I6Cxowf2zX08+s6KDDQFeZ12Rla1VHc6Itvv8IdNvjXdG8zeuMRh4/722mTgHWKxuRN X-Gm-Message-State: AOJu0YyOiJ27p5u9DQ1f7JGLkMAAsGO0QxYeDrV74k3I+s4gd+Z6c7uw PioWXe1/omBsR8EnBKqfQQPfwpzjToE/zWdsqcAVd5lg7K+1S7yJvEaNZb4yOnrUAMg2uwGdiBm +j7AGhCDeSOo9d0hX5NiIavX71QA= X-Google-Smtp-Source: AGHT+IHQUYrM4VDJ1V6Yn3PLoZ3Xzfd7z7BgD+8D0SiLiMKivXPtZuYxOHj+sWfOIalIQO0l+R31nHPxPmNtF1WdB2c= X-Received: by 2002:a17:906:a16:b0:a6e:ab8b:aff4 with SMTP id a640c23a62f3a-a6f34c9a403mr265648566b.13.1718142183134; Tue, 11 Jun 2024 14:43:03 -0700 (PDT) MIME-Version: 1.0 References: <1628637f-419f-4f6a-9cb6-07af90cd0bc4@aklaver.com> <1c0273f5-a90a-48f6-b51f-fe15c16fa1c6@aklaver.com> <490e8a5c-f1f5-40fe-8243-f1c8c18d03f2@aklaver.com> In-Reply-To: From: Koen De Groote Date: Tue, 11 Jun 2024 23:42:51 +0200 Message-ID: Subject: Re: Questions on logical replication To: Justin Cc: Adrian Klaver , PostgreSQL General Content-Type: multipart/alternative; boundary="0000000000007fdb96061aa4265f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007fdb96061aa4265f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > If there are any errors during the replay of WAL such as missing indexes for Replica Identities during an Update or Delete this will cause the main subscriber worker slot on the publisher to start backing up WAL files And also if the connection breaks, from what I understand, is that correct? Anything that stops the subscription, including disabling the subscription, is that right? > I suggest confirming all tables have replica identities or primary keys before going any further. Yes, I am aware of this. I made me a small script that prints which tables I have added to the publication and are done syncing, and which are currently not being replicated. > With PG 11 avoid REPLICA IDENTITY FULL as this causes full table scan on the subscriber for PG 15 and earlier. I'm also aware of this. My plan is to create a publication with no tables, and add them 1 by 1, refreshing the subscriber each time. I'm not planning on using "REPLICA IDENTITY FULL" anywhere. On Sat, Jun 8, 2024 at 10:33=E2=80=AFPM Justin wrote= : > > On Sat, Jun 8, 2024 at 1:41=E2=80=AFPM Koen De Groote = wrote: > >> What I'm trying to do is upgrade a PG11 database to PG16, using logical >> replication. >> >> The PG11 has an active and a standby, there are a handful of databases. >> On particular one has a few tables just over 100GB, then a few 100 table= s >> near 1GB. >> >> What I'd do is start a publication with no tables and add them 1 at a >> time, refreshing subscription each time. >> >> This might take a long time, so my main questions relate to potential >> network issues or various situations where the instance receiving the >> logical replication, suddenly stop being able to receive. >> >> Resyncing, and the effects of WAL buildup, are my main concern. >> >> Accidentally sent a mail to only your email, sorry for that. >> >> Regards, >> Koen De Groote >> >>> >>> > This approach does not prevent WAL build up. > > The WAL build up occurs during the initial sync worker once that table is > synced the WAL is replayed and released. The parent worker then become > responsible for replaying the WAL for that table > > The WAL build up is during the initial sync of the data by table NOT > during the entire synce of all the tables that have been published. > > For 1 gb table the initial sync will be very fast so I doubt any > individual table will cause any significant WAL build up to put the > publisher at risk of of crashing > > Once a table becomes synced the main subscriber worker keeps the WAL > replayed. If there are any errors during the replay of WAL such as missi= ng > indexes for Replica Identities during an Update or Delete this will caus= e > the main subscriber worker slot on the publisher to start backing up WAL > files. If there are missing replica identities the affected tables will > have to be dropped from the publication and subscription refreshed. The > WAL file is already written with incorrect information so the table on t= he > subscriber table is most likely not in recoverable state. > > I suggest confirming all tables have replica identities or primary keys > before going any further. With PG 11 avoid REPLICA IDENTITY FULL as th= is > causes full table scan on the subscriber for PG 15 and eariler. PG 16 on > the subsciber can use a different unique index that has NOT NULL for all > participating columns if the publisher is using Replicate Identity FULL = on > the published table > > One must understand the above before deploying logical replication. > > Hope this helps > > --0000000000007fdb96061aa4265f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
>=20 If there are any errors during the replay of WAL such as missing=20 indexes for Replica Identities during an Update or Delete=C2=A0 this will= =20 cause the main subscriber worker slot on the publisher to start backing=20 up WAL files

And also if the connection break= s, from what I understand, is that correct? Anything that stops the subscri= ption, including disabling the subscription, is that right?

<= /div>
>=20 I suggest confirming all tables have replica identities or primary keys bef= ore going any further.

Yes, I am aware of thi= s. I made me a small script that prints which tables I have added to the pu= blication and are done syncing, and which are currently not being replicate= d.

> With PG 11 avoid REPLICA IDENTITY FULL as this causes full table scan on th= e subscriber for PG 15 and earlier.

I'm a= lso aware of this. My plan is to create a publication with no tables, and a= dd them 1 by 1, refreshing the subscriber each time.

I'm not planning on using "REPLICA IDENTITY FULL" anywhere= .


On Sat, Jun 8, 2024 at 10:33=E2=80=AFPM Justin &l= t;zzzzz.graf@gmail.com> wrot= e:

On= Sat, Jun 8, 2024 at 1:41=E2=80=AFPM Koen De Groote <kdg.dev@gmail.com> wrote:
= What I'm trying to do is upgrade a PG11 database to PG16, using logical= replication.

The PG11 has an active and a standby, there are a handful of databases. On=20 particular one has a few tables just over 100GB, then a few 100 tables=20 near 1GB.

What I'd do is start a publication w= ith no tables and add them 1 at a time, refreshing subscription each time.<= /div>

This might take a long time, so my main questions relate to potential=20 network issues or various situations where the instance receiving the=20 logical replication, suddenly stop being able to receive.

Resyncing, and the effects of WAL buildup, are my main concern.

Accidentally sent a mail to only your email, sorry fo= r that.

Regards,
Koen De Groote


Th= is approach does not prevent WAL build up.

The WAL build up occurs d= uring the initial sync worker once that table is synced the WAL is replayed= and released.=C2=A0 =C2=A0The parent worker then become responsible for re= playing the WAL for that table

The WAL build up is during the = initial sync of the data by table NOT during the entire synce=C2=A0of all t= he tables that have been published.

For 1 gb table the initial sync = will be very fast so I doubt any individual table will cause any significan= t WAL build up to put the publisher at risk of of crashing

Once a ta= ble becomes synced the main subscriber worker keeps the WAL replayed.=C2=A0= If there are any errors during the replay of WAL such as missing indexes f= or Replica Identities during an Update or Delete=C2=A0 this will cause the = main subscriber worker slot on the publisher to start backing up WAL files.= If there are missing replica identities the affected tables will have to b= e dropped from the publication and subscription refreshed.=C2=A0 The WAL=C2= =A0 file is already written with incorrect information so the table on the = subscriber table is most likely not in recoverable state.=C2=A0
=C2=A0I suggest confirming all tables have replica identities or primary keys b= efore going any further.=C2=A0 =C2=A0 With PG 11 avoid REPLICA IDENTITY FUL= L as this causes full table scan on the subscriber for PG 15 and eariler.= =C2=A0 PG 16 on the subsciber can use a different unique index that has NOT= NULL for all participating columns if the publisher is using=C2=A0 Replica= te Identity FULL on the published table

One must understand the abov= e before deploying logical replication.=C2=A0

Hope this helps
--0000000000007fdb96061aa4265f--