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 1sHhHJ-00AAbL-Kl for pgsql-general@arkaria.postgresql.org; Thu, 13 Jun 2024 10:01:57 +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 1sHhHG-00GcqY-O1 for pgsql-general@arkaria.postgresql.org; Thu, 13 Jun 2024 10:01:55 +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 1sHhHG-00Gcp5-4I for pgsql-general@lists.postgresql.org; Thu, 13 Jun 2024 10:01:55 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sHhHE-0016LA-1L for pgsql-general@lists.postgresql.org; Thu, 13 Jun 2024 10:01:53 +0000 Received: by mail-ej1-x62d.google.com with SMTP id a640c23a62f3a-a6f1cf00b3aso133463166b.0 for ; Thu, 13 Jun 2024 03:01:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718272909; x=1718877709; 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=OjvQbkhjqv+bBTN7/H8MNC1NxmMzWh+hA8+ZeH8c99E=; b=GFflq5/sd90Saolavs0AiPN75B6Rf1Cs8SquTA/mgs+7GnbNQ2yy7FkfQP70dtE9OU 47WyNqO7XgDbp1e3KmexwaktIHEcgneAhE8EfMByzwTpri34Ehee47ODbnBKG1mAY0VB o7RU8HbIalPQq+UBlVqwn+0AKUHjZUNKMky6V11ZzuoTnP5GD++VBvcxVW/Zit/qjg/a x7gHLVmfKTby5HkH0CB8QC25bjfmFW4sD8277B0SYwYlBLSD30Sp1EnQjoe5+sy85Jqh Kjpc+4ndrehOyUlSjYar7rBLcq/m4MNovd/uLADQctPObpo4DSrmP3+gCLdYIRA1IjE3 FA5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718272909; x=1718877709; 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=OjvQbkhjqv+bBTN7/H8MNC1NxmMzWh+hA8+ZeH8c99E=; b=a1/sfyRvFA8u78xytEJ6uyKTV/B2K15Ayxs8O08LSKz/SBLwZUGA/5NrvPL1zSVTuw IYcnvo2dAo7MPm0y6u9DebHjclTJGd+l9gamMYUAI0iNGwqX4ZLt9NTpZfWhOsvaytpF NFqT05dbIST8sKOMTs3b60DyTrQOC84hu+dHt/hXivG7OzkPEYLmcfVT4kia+gOYpErX YAeaKxjA23frW9gObwBpvxAiPKvFPDporxzj1S9KjxIwshhoYQsUiGpc3bEs7jvs7PmL /VCtgETqbJvOO2WIj2I9wjzf6xPfnp9zQRhkYMbT0CGW3bKzmuwYlvDGWqzS+z2TnJ6h dGBQ== X-Gm-Message-State: AOJu0YyGJwdCJ2KHJmrSsGPawJmdzIXuKukcPIvcN2FbSqieXg/L1aAN qo7B4+RIhhBrk7h/Pmex0UdHKxC/kJ1o2ewo4ZxF9YLvYG3CTROtYxsAxahOGI7wZ2nmnZox2Lv 2uDPH27+u6a15paRSgW1yFIyD7lo= X-Google-Smtp-Source: AGHT+IHsWE/NounBhGA6iLLKsW8MYvr366834FAzy+WtKfY6cxD3WqTQ62YIzZ6tngYsLUTKiBmB10wU/nZgjiDrQ9o= X-Received: by 2002:a17:906:3b04:b0:a6f:1e1b:f9ea with SMTP id a640c23a62f3a-a6f52440a75mr149454466b.31.1718272909350; Thu, 13 Jun 2024 03:01:49 -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: Thu, 13 Jun 2024 12:01:38 +0200 Message-ID: Subject: Re: Questions on logical replication To: Justin Cc: "pgsql-generallists.postgresql.org" , Adrian Klaver Content-Type: multipart/alternative; boundary="000000000000639cf7061ac296e0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000639cf7061ac296e0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > Why? what benefit does this provide you?? Add all the tables when creating the publication and be done with it... I get this when trying to understand how this all works on test boxes, but for production NO idea what you're trying to accomplish Adding all tables at once means adding the gigantic tables as well. Disk IO and Network traffic are a serious concern, increased CPU usage affecting queries of the live system, as well as transaction wraparound. Initial sync can be a serious concern, depending on the size of the table. Here's a nice guide where people did a logical replication upgrade, explaining why they did it this way: https://knock.app/blog/zero-downtime-postgres-upgrades On Wed, Jun 12, 2024 at 7:01=E2=80=AFPM Justin wrote= : > > > On Tue, Jun 11, 2024 at 5:43=E2=80=AFPM Koen De Groote wrote: > >> > If there are any errors during the replay of WAL such as missing >> indexes for Replica Identities during an Update or Delete this will cau= se >> 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? >> > > Yes to all.... > > >> > I suggest confirming all tables have replica identities or primary key= s >> 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 a= re >> 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. >> > > Why? what benefit does this provide you?? Add all the tables when > creating the publication and be done with it... I get this when trying t= o > understand how this all works on test boxes, but for production NO idea > what you're trying to accomplish > > >> I'm not planning on using "REPLICA IDENTITY FULL" anywhere. >> > Good > --000000000000639cf7061ac296e0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
> Why?=C2=A0 what benefit=C2=A0does this provide you??= =C2=A0 =C2=A0Add all the tables=20 when creating the publication and be done with it...=C2=A0 I get this when= =20 trying to understand how this all works on test boxes, but for=20 production NO idea what you're trying to accomplish

=
Adding all tables at once means adding the gigantic tables as well. Di= sk IO and Network traffic are a serious concern, increased CPU usage affect= ing queries of the live system, as well as transaction wraparound.

Initial sync can be a serious concern, depending on the si= ze of the table.

Here's a nice guide where= people did a logical replication upgrade, explaining why they did it this = way: htt= ps://knock.app/blog/zero-downtime-postgres-upgrades

On Wed, Jun 12= , 2024 at 7:01=E2=80=AFPM Justin <zzzzz.graf@gmail.com> wrote:


On Tue, Jun 11,= 2024 at 5:43=E2=80=AFPM Koen De Groote <kdg.dev@gmail.com> wrote:
>=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?
=C2=A0
Yes to all....=C2=A0


>=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.
=C2=A0

> 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.
=C2=A0
Why?=C2=A0 what benefit=C2=A0does this provide you?= ?=C2=A0 =C2=A0Add all the tables when creating the publication and be done = with it...=C2=A0 I get this when trying to understand how this all works on= test boxes, but for production NO idea what you're trying to accomplis= h=C2=A0


I'm not planning on using "= ;REPLICA IDENTITY FULL" anywhere.
Goo= d=C2=A0
--000000000000639cf7061ac296e0--