Feedback-ID: i76984098:Fastmail
Message-ID: <b5473e72-b8f8-4aa0-8759-046628e93348@aklaver.com>
Date: Sun, 24 Nov 2024 13:58:01 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting
 Up Streaming Replication
To: Ron Johnson <ronljohnsonjr@gmail.com>,
 pgsql-general <pgsql-general@postgresql.org>
References: <CAD=40Z3Rvbx46SxYBBdLvgSObXCP+Firszz++rDTXmGX795D2Q@mail.gmail.com>
 <6c498f0e-64f9-449a-9b90-5cd72d00e2ef@aklaver.com>
 <CAD=40Z1wJ7mBej9=Yqfo_1m-eOV61E=s6yDjZSaTBESbGunBUA@mail.gmail.com>
 <2a7d96ac-83a7-4ddc-a3ce-9c637f2c1c76@aklaver.com>
 <CAD=40Z3prwhLAedetocCu3jFvHSBP44V6O9d6CXjvzLTw7eOUQ@mail.gmail.com>
 <CANzqJaACBw1=jCAxGwSeu7dh4UfD2KePcNqSOC3=WwzXrg0skQ@mail.gmail.com>
 <CAD=40Z3k2dgVidM3oYuMWsauHyKF9v-0i4nOfMXd917PtNiO_g@mail.gmail.com>
 <1FBD217B-06FE-48CF-AA58-F7ADEDA5ADD2@thebuild.com>
 <CANzqJaANutDZy-bRCC1Sf0X7=LtF2HHMBfk73_MmVyXBReL4=w@mail.gmail.com>
 <60D0A828-451D-48B1-8A47-B246806A03E5@thebuild.com>
 <CANzqJaCbagpPpt6mhjA1UDCJ6xpHV-SWim6vOCWKcZjRjuSm=g@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CANzqJaCbagpPpt6mhjA1UDCJ6xpHV-SWim6vOCWKcZjRjuSm=g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/b5473e72-b8f8-4aa0-8759-046628e93348%40aklaver.com>
Precedence: bulk

On 11/24/24 13:00, Ron Johnson wrote:
> On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus <xof@thebuild.com 
> <mailto:xof@thebuild.com>> wrote:
> 
>      > On Nov 24, 2024, at 09:15, Ron Johnson <ronljohnsonjr@gmail.com
>     <mailto:ronljohnsonjr@gmail.com>> 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.
"

"
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.
"

https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION

"
Caution

Beware that replication slots can cause the server to retain so many WAL 
segments that they fill up the space allocated for pg_wal. 
max_slot_wal_keep_size can be used to limit the size of WAL files 
retained by replication slots.
"

They have their issues also, namely they may not do all the work for you.


> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!

-- 
Adrian Klaver
adrian.klaver@aklaver.com