Re: WAL accumulating, Logical Replication pg 13

Hi, here's an update on this old thread. I've found the problem, it was because of long transactions. I reproduced the problem when I added a second publication and another postgres cluster with a subscription to it (on acceptance still). This query came in handy to show me what was happening: se

Re: WAL accumulating, Logical Replication pg 13

Hi, I was going to follow up on this one, sorry for the long silence. The replication is working fine now, and I have no idea what the problem was. Not cool. If I find out, I will let you know. On Mon, May 31, 2021 at 6:06 PM Tomas Pospisek wrote: > Hi Willy-Bas Loos, > > On 31.05.21 17:32, Will

Re: WAL accumulating, Logical Replication pg 13

Hi Willy-Bas Loos, On 31.05.21 17:32, Willy-Bas Loos wrote: On Mon, May 31, 2021 at 4:24 PM Vijaykumar Jain > wrote: So I got it all wrong it seems :) Thank you for taking the time to help me! You upgraded to pg13 fine? , but while on pg13 y

Re: WAL accumulating, Logical Replication pg 13

This part. If you can read perl :), https://github.com/postgres/postgres/tree/master/src/test/subscription/t On Mon, May 31, 2021, 9:02 PM Willy-Bas Loos wrote: > > > On Mon, May 31, 2021 at 4:24 PM Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: > >> So I got it all wrong it seems

Re: WAL accumulating, Logical Replication pg 13

On Mon, May 31, 2021 at 4:24 PM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > So I got it all wrong it seems :) > Thank you for taking the time to help me! You upgraded to pg13 fine? , but while on pg13 you have issues with logical > replication ? > Yes, the upgrade went fine. So he

Re: WAL accumulating, Logical Replication pg 13

So I got it all wrong it seems :) You upgraded to pg13 fine? , but while on pg13 you have issues with logical replication ? There is a path in the postgresql source user subscription folder iirc which covers various logical replication scenarios. That may help you just in case. I have tried all k

Re: WAL accumulating, Logical Replication pg 13

Thank you for elaborating those possible causes and for the suggestions you made. 1) if you have an inactive replication slot. There is only 1 replication slot and it is active. So that is not the issue. 2) Do you have archiving enabled? No, i never turned it on and so this is in the settings of

Re: WAL accumulating, Logical Replication pg 13

WAL can be built up for reasons like 1) if you have an inactive replication slot. I mean you had a streaming replica which was turned off, but you did not remote the slot from primary. 2) Do you have archiving enabled? Are the archiving commands running fine ? if just the archiving is broken, then

Re: WAL accumulating, Logical Replication pg 13

Yeah, indexes could slow things down, thanks. Btw I'm not using logical replication for the upgrade, that's not supported for 9.3. It was more complicated but that's beside the point. I could just delete the publication and all that belongs to it and start over. But since I'm trying out logical re

Re: WAL accumulating, Logical Replication pg 13

I am not too sure with 9.3 i tried an upgrade from 9.6 to 11 using logical replication (pg_logical extension) one thing to note. logical replication initiates a copy from a snapshot, then changes from then on. I had a very high insert rate on my source tables (v9.6) and the destination (v11) coul

WAL accumulating, Logical Replication pg 13

Hi , I'm upgrading a 1.5TB database from postgres 9.3 to postgres 13 on Debian 10. This is now in an Acceptance stage (DTAP). I have encountered a problem: the WAL is not being deleted. I now have 1.4 TB of WAL in pg_wal and my disks are getting full. The oldest WAL file is 18 days old. I use Logic