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 1tFKtF-00CVYb-FQ for pgsql-general@arkaria.postgresql.org; Sun, 24 Nov 2024 22:15:37 +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 1tFKtD-009rKy-B9 for pgsql-general@arkaria.postgresql.org; Sun, 24 Nov 2024 22:15:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFKtC-009rKp-Ko for pgsql-general@lists.postgresql.org; Sun, 24 Nov 2024 22:15:34 +0000 Received: from mail-oo1-xc30.google.com ([2607:f8b0:4864:20::c30]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tFKt9-003ew7-TY for pgsql-general@postgresql.org; Sun, 24 Nov 2024 22:15:34 +0000 Received: by mail-oo1-xc30.google.com with SMTP id 006d021491bc7-5f1d2448741so412553eaf.0 for ; Sun, 24 Nov 2024 14:15:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732486529; x=1733091329; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=TubIuCAac/WERyAQMx6oAWfVHimGVJgv7Th6dXufr/4=; b=eDMZSupPLzYMrwvB78TcblEuQzYaN+JIe9c0kIuAp36UFngIF/WntKFSg8NJp5Ptmc qAmra7KckFgkQRdo19I/pr+2VNUrzLqQuFz9q3z937pl5W5CrUmk5Pr42G6aXw+quyy1 nlzwO9+07BTT20ZbH+hBHOwg6t/s/lPvBaTybvN0eBR5gKQg2n/fP8xazR8TGcrpedLC Ql58nbQO3GLiIHDAn2AOctc18tuEf6ro5YOxzflg21eQT0HBYYxVnyYNEwfWOjFLwPF4 i3GeRichwyptutBFzRPtaDW1wkDMYhvqz2kyxGbwDD2gSwJMTnOIGZs12Tth4HrsPm+I MOzg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732486529; x=1733091329; h=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=TubIuCAac/WERyAQMx6oAWfVHimGVJgv7Th6dXufr/4=; b=MxIoZLkBHYOlsu0Rd2bZ15CD/Pa1TvRdEEFNgM6aA0ZZuVQQl4wcj/zgjLW0n0/c4i DwlC38PqUD9igyLHGgYBR/iOIJZIwhl/ejDYgWsTrYzfTu9eJ6NWeNwlKQs36jy1R3Kp 9Y9cctLcI+m2iptwywL/jH2s6R95gLQ3WgKp/y1OYjbno8PiHeEme7sy52vIAaE9eixP 7rPIxKqZ4W9d3ftlTBPgCcu+wlE4A8l6VIUN+A1JvBu38SJQnJ6jVit7+ct7qULtJgvZ ErWUCLzHO5/SACTRcN4vI8/jd2Mh1F24bOwxuJO6iGs7aMa9gBL85jTMN1fV2lBcJmHk Z9Ww== X-Gm-Message-State: AOJu0YyY+HEpE4sJMgl28p8ciSYIQolPOwxAYeH/8GuFT5HwXN6MpTfB Y29t5gfAlTjQ6zzrrqBZjey+DtznwAqv7os/vit/89sLgXBaU479Xe80xLJCKgJishiwQruInGm 04iBpzKHlFD8WHmhUAXmnHDtWnrp1bm/H X-Gm-Gg: ASbGncudsr6AAvV5Te/taIWrvHxTkG7FyCCnSsVUHudF6qPA2DduKcNv+dh5EBjicq5 N2OhzI570fiETGO9SoBMcmmOTFyeoPZaIf21wPM2GBko3h8ey2a7jfT4SkRHm6y5cvw== X-Google-Smtp-Source: AGHT+IGxubtmw++Fz4nTe+hEvHFBKr0sRH18m9L4B94Pzqpb0QboKCAtGod583URkqzu1cBInSN69HiaAsri2mY7ClE= X-Received: by 2002:a9d:6248:0:b0:71a:7603:d2d2 with SMTP id 46e09a7af769-71c04b9bad7mr7127224a34.18.1732486528975; Sun, 24 Nov 2024 14:15:28 -0800 (PST) MIME-Version: 1.0 References: <6c498f0e-64f9-449a-9b90-5cd72d00e2ef@aklaver.com> <2a7d96ac-83a7-4ddc-a3ce-9c637f2c1c76@aklaver.com> <1FBD217B-06FE-48CF-AA58-F7ADEDA5ADD2@thebuild.com> <60D0A828-451D-48B1-8A47-B246806A03E5@thebuild.com> In-Reply-To: From: Ron Johnson Date: Sun, 24 Nov 2024 17:15:18 -0500 Message-ID: Subject: Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication To: pgsql-general Content-Type: multipart/alternative; boundary="00000000000023531f0627aff482" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000023531f0627aff482 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, Nov 24, 2024 at 4:58=E2=80=AFPM Adrian Klaver wrote: > On 11/24/24 13:00, Ron Johnson wrote: > > On Sun, Nov 24, 2024 at 2:55=E2=80=AFPM Christophe Pettus > > wrote: > > > > > On Nov 24, 2024, at 09:15, Ron Johnson > > wrote: > > > > > > Doesn't the existence of a replication slot force PG to retain > > WAL files when replication is broken? > > > > It does. I don't recall if the OP said that they were using a > > persistent replication slot or not; it's not as common with binary > > replication as with logical replication. > > > > > > Really? I wonder why people fight with configuring max_wal_size and > > wal_keep_size, when replication slots do all the work for you. > > https://www.postgresql.org/docs/current/logicaldecoding-explanation.html > > " > Caution > > Replication slots persist across crashes and know nothing about the > state of their consumer(s). They will prevent removal of required > resources even when there is no connection using them. This consumes > storage because neither required WAL nor required rows from the system > catalogs can be removed by VACUUM as long as they are required by a > replication slot. In extreme cases this could cause the database to shut > down to prevent transaction ID wraparound (see Section 24.1.5). So if a > slot is no longer required it should be dropped. > " > Nagios has built-in disk space monitoring, and if it doesn't also have built-in replication monitoring, you can write a plug-in. Or write your own bash script that periodically runs "SELECT * from pg_replication_slots;" and "SELECT * FROM pg_stat_replication;" on the primary and "SELECT * FROM pg_stat_wal_receiver;" on the secondary. Whichever you do, some monitoring should always be in place. " > Caution > > There is a chance that the old primary is up again during the promotion > and if subscriptions are not disabled, the logical subscribers may > continue to receive data from the old primary server even after > promotion until the connection string is altered. This might result in > data inconsistency issues, preventing the logical subscribers from being > able to continue replication from the new primary server. > " > Logical replication is off-topic for this problem, no? --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000023531f0627aff482 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sun, Nov 24, 2024 at 4:58=E2=80=AFPM A= drian Klaver <adrian.klaver= @aklaver.com> wrote:
On 11/24/24 13:00, Ron Johnson wrote: > On Sun, Nov 24, 2024 at 2:55=E2=80=AFPM Christophe Pettus <xof@thebuild.com
> <mailto:xof@t= hebuild.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0 > On Nov 24, 2024, at 09:15, Ron Johnson <ronljohnsonjr@gm= ail.com
>=C2=A0 =C2=A0 =C2=A0<mailto:ronljohnsonjr@gmail.com>> wrote:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Doesn't the existence of a replication sl= ot force PG to retain
>=C2=A0 =C2=A0 =C2=A0WAL files when replication is broken?
>
>=C2=A0 =C2=A0 =C2=A0It does.=C2=A0 I don't recall if the OP said th= at they were using a
>=C2=A0 =C2=A0 =C2=A0persistent replication slot or not; it's not as= common with binary
>=C2=A0 =C2=A0 =C2=A0replication as with logical replication.
>
>
> Really? I wonder why people fight with configuring max_wal_size and > wal_keep_size, when replication slots do all the work for you.

https://www.postgresql.org/d= ocs/current/logicaldecoding-explanation.html

"
Caution

Replication slots persist across crashes and know nothing about the
state of their consumer(s). They will prevent removal of required
resources even when there is no connection using them. This consumes
storage because neither required WAL nor required rows from the system
catalogs can be removed by VACUUM as long as they are required by a
replication slot. In extreme cases this could cause the database to shut down to prevent transaction ID wraparound (see Section 24.1.5). So if a slot is no longer required it should be dropped.
"

Nagios has built-in disk space m= onitoring, and if it doesn't also have built-in replication monitoring,= you can write a plug-in.=C2=A0 Or write your own bash script that periodic= ally runs=C2=A0"SELECT * from pg_replication_slots;" and=C2=A0&qu= ot;SELECT * FROM pg_stat_replication;" on the primary and=C2=A0"S= ELECT * FROM pg_stat_wal_receiver;" on the secondary.
=C2=A0=
Whichever you do, some monitoring should always be in place.

"
Caution

There is a chance that the old primary is up again during the promotion and if subscriptions are not disabled, the logical subscribers may
continue to receive data from the old primary server even after
promotion until the connection string is altered. This might result in
data inconsistency issues, preventing the logical subscribers from being able to continue replication from the new primary server.
"

Logical replication is of= f-topic for this problem, no?

--
Death to <Redacted>, and butter sauce.
Don't boi= l me, I'm still alive.
<Redacted> lobster!
--00000000000023531f0627aff482--