Re: replication primary writting infinite number of WAL files

Peter J. Holzer (2023. nov. 26., V, 12:30): > > > nov 24 10:20:19 docker02 33d8b96b9062[1200]: 2023-11-24 10:20:19.691 CET > [35] > LOG: checkpoints are occurring too frequently (23 seconds apart) > > nov 24 10:20:42 docker02 33d8b96b9062[1200]: 2023-11-24 10:20:42.938 CET > [35] > LOG: checkpo

Re: replication primary writting infinite number of WAL files

On 2023-11-24 22:05:12 +0100, Les wrote: > And there is nothing in the logs in that time period besides "checkpoints > are happening too frequently"? > > > > Here are all the logs we have from that minute. > > ╰─# journalctl CONTAINER_ID=33d8b96b9062 --since '2023-11-24 10:18:00' --unti

Re: replication primary writting infinite number of WAL files

On 11/24/23 14:01, Les wrote: A single sequence for all id columns across all tables? How is the sequence value landing in the id column? In most cases it is by using "nextval(seq_name)" in the SQL statement. But sometimes the sequence value is taken first, and then multiple inserts

Re: replication primary writting infinite number of WAL files

> > > A single sequence for all id columns across all tables? > > How is the sequence value landing in the id column? > In most cases it is by using "nextval(seq_name)" in the SQL statement. But sometimes the sequence value is taken first, and then multiple inserts are sent with fixed increasing va

Re: replication primary writting infinite number of WAL files

On 11/24/23 1:05 PM, Les wrote: >> Sequence is incremented by 100,  so for example, between 2023-11-24 >> 10:20:00 and 2023-11-24 10:21:00 it went up 62188671 steps. I think it >> is not possible to insert 62188671 rows into a table. A psql function >> might be able

Re: replication primary writting infinite number of WAL files

> > > >> Sequence is incremented by 100, so for example, between 2023-11-24 > >> 10:20:00 and 2023-11-24 10:21:00 it went up 62188671 steps. I think it > >> is not possible to insert 62188671 rows into a table. A psql function > >> might be able to increment a sequence 62M times / minute, I'm not

Re: replication primary writting infinite number of WAL files

On 11/24/23 09:51, Adrian Klaver wrote: On 11/24/23 09:32, Les wrote: Please Reply All to include list Ccing list to get information back there. Adrian Klaver > (2023. nov. 24., P, 17:50):     On 11/24/23 03:39, Les wrote: > The only exception is a se

Re: replication primary writting infinite number of WAL files

> > > > I have no explanation for that, except a coincidence. > Replication slots don't generate WAL. > The problem stopped exactly when I dropped the slot. It happened three times in a row. :-(

Re: replication primary writting infinite number of WAL files

On 11/24/23 09:32, Les wrote: Please Reply All to include list Ccing list to get information back there. Adrian Klaver > (2023. nov. 24., P, 17:50): On 11/24/23 03:39, Les wrote: > The only exception is a sequence > value that was moved millio

Re: replication primary writting infinite number of WAL files

On Fri, 2023-11-24 at 16:59 +0100, Les wrote: > > > Laurenz Albe   (2023. nov. 24., P, 16:00): > > On Fri, 2023-11-24 at 12:39 +0100, Les wrote: > > > Under normal circumstances, the number of write operations is relatively > > > low, with an > > > average of 4-5 MB/sec total write speed on the

Re: replication primary writting infinite number of WAL files

On 11/24/23 03:39, Les wrote: Hello, Yesterday, the primary server suddenly started writing to the pg_wal directory at a crazy pace, 1.5GB/sec, but sometimes it went up to over 3GB/sec. The pg_wal started fattening up and didn't stop until it ran out of disk space. It happened so fast that we

Re: replication primary writting infinite number of WAL files

On Fri, 24 Nov 2023, 17:12 Ron Johnson, wrote: > On Fri, Nov 24, 2023 at 11:00 AM Les wrote: > [snip] > >> Writing of WAL files continued after we shut down all clients, and >> restarted the primary PostgreSQL server. >> >> The order was: >> >> 1. shut down all clients >> 2. stop the primary >>

Re: replication primary writting infinite number of WAL files

On Fri, Nov 24, 2023 at 11:00 AM Les wrote: [snip] > Writing of WAL files continued after we shut down all clients, and > restarted the primary PostgreSQL server. > > The order was: > > 1. shut down all clients > 2. stop the primary > 3. start the primary > 4. primary started to write like mad ag

Re: replication primary writting infinite number of WAL files

Laurenz Albe (2023. nov. 24., P, 16:00): > On Fri, 2023-11-24 at 12:39 +0100, Les wrote: > > Under normal circumstances, the number of write operations is relatively > low, with an > > average of 4-5 MB/sec total write speed on the disk associated with the > data directory. > > Yesterday, the pr

Re: replication primary writting infinite number of WAL files

On Fri, 2023-11-24 at 12:39 +0100, Les wrote: > Under normal circumstances, the number of write operations is relatively low, > with an > average of 4-5 MB/sec total write speed on the disk associated with the data > directory. > Yesterday, the primary server suddenly started writing to the pg_wa

Re: replication primary writting infinite number of WAL files

> > > First I was also thinking about vacuum. But removing a replication > > slot should have no effect on vacuum on the primary (AFAIK). Please > > correct me if I'm wrong. > > > > yeah, depends. there are 2 processes: > > * 1 process generating the wal's, maybe a VACUUM > * an inactive slot hold

Re: replication primary writting infinite number of WAL files

Am 24.11.23 um 13:52 schrieb Les: Andreas Kretschmer wrote (2023. nov. 24., P, 13:22): Am 24.11.23 um 12:39 schrieb Les: > > Hello, > please check the database log, a VACUUM can also lead to massive wal generation. Can you find other related messages? by the wa

Re: replication primary writting infinite number of WAL files

Andreas Kretschmer wrote (2023. nov. 24., P, 13:22): > > > Am 24.11.23 um 12:39 schrieb Les: > > > > Hello, > > > > please check the database log, a VACUUM can also lead to massive wal > generation. Can you find other related messages? by the way, the picture > is hard to read, please post text i

Re: replication primary writting infinite number of WAL files

Am 24.11.23 um 12:39 schrieb Les: Hello, Yesterday we were faced with a problem that we do not understand and cannot solve ourselves. We have a postgresql cluster using repmgr, which has three members. The version of all instances (currently) is "PostgreSQL 14.5 (Debian 14.5-2.pgdg110+2)