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 sunyucong@xxxxxxxxx <sunyucong@xxxxxxxxx> 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. > > > >
Attachment:
image.png
Description: PNG image
