ccing the mailist again in case someone else would have a idea how to debug:
Here is what I see in the pg_locks on subscribe at all time: As you can see, it mostly concern following 3 tables, accounts, ledger, pending_ledger_fees, which I have tried analyze, vacuum them etc, none of them helped much: I do see in the log these tables are getting autovaccumed *very* frequently, is that a problem for logical replication? 2023-02-08 04:59:47 UTC::@:[13626]:LOG: automatic vacuum of table "dev.public.accounts": index scans: 0 pages: 0 removed, 71437 remain, 0 skipped due to pins, 11465 skipped frozen tuples: 0 removed, 3590987 remain, 3522495 are dead but not yet removable, oldest xmin: 2893907681 index scan bypassed: 4 pages from table (0.01% of total) have 5 dead item identifiers I/O timings: read: 0.000 ms, write: 0.000 ms avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 120107 hits, 0 misses, 0 dirtied WAL usage: 1 records, 0 full page images, 708 bytes system usage: CPU: user: 0.23 s, system: 0.00 s, elapsed: 1.01 s 2023-02-08 04:59:47 UTC::@:[13626]:LOG: automatic vacuum of table "dev.public.pending_fee_ledger": index scans: 0 pages: 0 removed, 199818 remain, 0 skipped due to pins, 117769 skipped frozen tuples: 0 removed, 3670095 remain, 3520000 are dead but not yet removable, oldest xmin: 2893907681 index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed I/O timings: read: 0.000 ms, write: 0.000 ms avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 164212 hits, 0 misses, 0 dirtied WAL usage: 0 records, 0 full page images, 0 bytes system usage: CPU: user: 0.27 s, system: 0.00 s, elapsed: 0.27 s Thanks On Tue, Feb 7, 2023 at 6:10 PM sunyuc...@gmail.com <sunyuc...@gmail.com> wrote: > > Hi Justin, thanks for the response! > > > REPLICA IDENTITY DEFAULT will only use primary keys, if the publisher > > includes those tables, the subscriber when replaying the WAL will stop > > throwing an error not knowing how to replay the UPDATE/DELETE. > > But I don't see any errors being thrown out in the postgresql logs? > Should I be seeing it complain there? Is postgresql falling back to > replica identity full here? > > However I checked that table, it doesn't seem to be making progress at > all: so I suspect you are right that it is the problem. > > > Logical Replication is most likely broken at this point. > > > > I suggest stopping logical replication and correcting tables that don't > > have qualifying indexes for logical replication by creating the necessary > > indexes and avoid using replica identity full. Then restart logical > > replication from the beginning. > > > >
