Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 4:58 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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

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?

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