pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLogical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size."
4+ messages / 3 participants
[nested] [flat]
* Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size."
@ 2022-11-29 08:47 Viljo Hakala <Viljo.Hakala@advania.com>
2022-11-29 08:49 ` Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size." Viljo Hakala <Viljo.Hakala@advania.com>
2022-11-29 09:41 ` Re: Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size." Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 4+ messages in thread
From: Viljo Hakala @ 2022-11-29 08:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresq.org <pgsql-admin@lists.postgresq.org>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hello lists,
We have two PostgreSQL 14.3 on Red Hat Linux 8.5 running
two different databases on VMS, where logical replicationn is used between databases.
Recently we have got bitten by a repeating issue in the databases
As we use logical replication between these two systems, we have
had to rebuilt logical replication, by dropping the subscriber as
that willl drop the logical replication slot on the primary and issue
does not occur for some time, but it will repeat.
This time it repeated after 3 days logical replication was rebuilt.
Last time it took 2-3 months until it repeated.
We started to get these warnings on the standby
2022-11-29 10:21:06.940 EET [1698404] ERROR: could not start WAL streaming: ERROR: cannot read from logical replication slot ”logs”
DETAIL: This slot has been invalidated because it exceeded the maximum reserved size.
2022-11-29 10:21:06.942 EET [1698368] LOG: background worker "logical replication worker" (PID 1698404) exited with exit code 1
Even though according to manual the max_slot_wal_keep_size is -1 and should not have a limit ?
If max_slot_wal_keep_size is -1 (the default), replication slots may retain an unlimited amount of WAL files
https://postgresqlco.nf/doc/en/param/max_slot_wal_keep_size/
psql (14.3)
Type "help" for help.
postgres=# show max_slot_wal_keep_size;
max_slot_wal_keep_size
------------------------
-1
(1 row)
Why is this happening? Is this a bug in PG 14.3 ?
Our fix for the time being is
DB=# alter subscription logs disable;
ALTER SUBSCRIPTION
SN4ReportingDB=# drop subscription logs
NOTICE: dropped replication slot ”logs” on publisher
DROP SUBSCRIPTION
SN4ReportingDB=# create subscription logs connection 'dbname=DBNAMAE host=192.168.1.1 port=5000 user=postgres' publication log pub with (copy_data=false);
NOTICE: created replication slot ”logs” on publisher
CREATE SUBSCRIPTION
But as this is a live system with a terabyte of data, some data will be lost unless we rebuilt the whole replication from scratch and this is not
bearable!
Any advice?
Regards,
Viljo Hakala
^ permalink raw reply [nested|flat] 4+ messages in thread
* Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size."
2022-11-29 08:47 Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size." Viljo Hakala <Viljo.Hakala@advania.com>
@ 2022-11-29 08:49 ` Viljo Hakala <Viljo.Hakala@advania.com>
1 sibling, 0 replies; 4+ messages in thread
From: Viljo Hakala @ 2022-11-29 08:49 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hello lists,
We have two PostgreSQL 14.3 on Red Hat Linux 8.5 running
two different databases on VMS, where logical replicationn is used between databases.
Recently we have got bitten by a repeating issue in the databases
As we use logical replication between these two systems, we have
had to rebuilt logical replication, by dropping the subscriber as
that willl drop the logical replication slot on the primary and issue
does not occur for some time, but it will repeat.
This time it repeated after 3 days logical replication was rebuilt.
Last time it took 2-3 months until it repeated.
We started to get these warnings on the standby
2022-11-29 10:21:06.940 EET [1698404] ERROR: could not start WAL streaming: ERROR: cannot read from logical replication slot ”logs”
DETAIL: This slot has been invalidated because it exceeded the maximum reserved size.
2022-11-29 10:21:06.942 EET [1698368] LOG: background worker "logical replication worker" (PID 1698404) exited with exit code 1
Even though according to manual the max_slot_wal_keep_size is -1 and should not have a limit ?
If max_slot_wal_keep_size is -1 (the default), replication slots may retain an unlimited amount of WAL files
https://postgresqlco.nf/doc/en/param/max_slot_wal_keep_size/<https://eur02.safelinks.protection.o...;
psql (14.3)
Type "help" for help.
postgres=# show max_slot_wal_keep_size;
max_slot_wal_keep_size
------------------------
-1
(1 row)
Why is this happening? Is this a bug in PG 14.3 ?
Our fix for the time being is
DB=# alter subscription logs disable;
ALTER SUBSCRIPTION
SN4ReportingDB=# drop subscription logs
NOTICE: dropped replication slot ”logs” on publisher
DROP SUBSCRIPTION
SN4ReportingDB=# create subscription logs connection 'dbname=DBNAMAE host=192.168.1.1 port=5000 user=postgres' publication log pub with (copy_data=false);
NOTICE: created replication slot ”logs” on publisher
CREATE SUBSCRIPTION
But as this is a live system with a terabyte of data, some data will be lost unless we rebuilt the whole replication from scratch and this is not
bearable!
Any advice?
Regards,
Viljo Hakala
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size."
2022-11-29 08:47 Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size." Viljo Hakala <Viljo.Hakala@advania.com>
@ 2022-11-29 09:41 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-11-29 13:16 ` Re:Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size." Sergei Kornilov <sk@zsrv.org>
1 sibling, 1 reply; 4+ messages in thread
From: Alvaro Herrera @ 2022-11-29 09:41 UTC (permalink / raw)
To: Viljo Hakala <Viljo.Hakala@advania.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On 2022-Nov-29, Viljo Hakala wrote:
> We have two PostgreSQL 14.3 on Red Hat Linux 8.5 running two
> different databases on VMS, where logical replicationn is used between
> databases.
Hmm, I don't see any bug fixes in the commit that would match this.
I only looked after May 9th 2022, which is 14.3's tag date.
> 2022-11-29 10:21:06.940 EET [1698404] ERROR: could not start WAL streaming: ERROR: cannot read from logical replication slot ”logs”
> DETAIL: This slot has been invalidated because it exceeded the maximum reserved size.
> 2022-11-29 10:21:06.942 EET [1698368] LOG: background worker "logical replication worker" (PID 1698404) exited with exit code 1
>
> Even though according to manual the max_slot_wal_keep_size is -1 and should not have a limit ?
You should see earlier messages about the slot being invalidated, during
the previous checkpoint -- and potentially the walsender being
signalled, if it was running. Can you spot those?
> postgres=# show max_slot_wal_keep_size;
>
> max_slot_wal_keep_size
> ------------------------
> -1
The only explanation of this behavior that doesn't involve a bug is that
this parameter was set to a nonzero value, then set to disabled, but the
checkpointer failed to notice the change.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"This is what I like so much about PostgreSQL. Most of the surprises
are of the "oh wow! That's cool" Not the "oh shit!" kind. :)"
Scott Marlowe, http://archives.postgresql.org/pgsql-admin/2008-10/msg00152.php
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re:Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size."
2022-11-29 08:47 Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size." Viljo Hakala <Viljo.Hakala@advania.com>
2022-11-29 09:41 ` Re: Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size." Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-11-29 13:16 ` Sergei Kornilov <sk@zsrv.org>
0 siblings, 0 replies; 4+ messages in thread
From: Sergei Kornilov @ 2022-11-29 13:16 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-bugs@lists.postgresql.org, Viljo Hakala <viljo.hakala@advania.com>
Hello
> The only explanation of this behavior that doesn't involve a bug is that
this parameter was set to a nonzero value, then set to disabled, but the
checkpointer failed to notice the change.
Or max_slot_wal_keep_size is really set on the publication database, but only the subscription database config was checked.
regards, Sergei
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-11-29 13:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-29 08:47 Logical replication halted due to "this slot has been invalidated because it exceeded the maximum reserved size." Viljo Hakala <Viljo.Hakala@advania.com>
2022-11-29 08:49 ` Viljo Hakala <Viljo.Hakala@advania.com>
2022-11-29 09:41 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-11-29 13:16 ` Sergei Kornilov <sk@zsrv.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox