Postgres Logical Replication - how to see what subscriber is doing with received data?

Question

I've got two Postgres 13 databases on AWS RDS.

• One is a master, the other a slave using logical replication.
• Replication has fallen behind by about 350Gb.
• The slave was maxed out in terms of CPU for the past four days because of some jobs that were ongoing so I'm not sure what logical replication was able to replicate during that time.
• I killed those jobs and now CPU on the master and slave are both low.
• I look at the subscriber via select * from pg_stat_subscription; and see that latest_end_lsn is advancing albeit very slowly.
• The publisher says write/flush/replay lags are all 13 minutes behind but it's been like that for most of the day.
• I see no errors in the logs on either the publisher or subscriber outside of some simple SQL errors that users have been making.

Is there anything I can do here? Previously I set wal_receiver_timeout timeout to 0 because I had replication issues, and that helped things. I wish I had some visibility here to get any kind of confidence that it's going to pull through, but other than these lsn values and database logs, I'm not sure what to check.

Answer 1

I figured out what my problem was: triggers on the subscriber.

I saw in pg_locks that a table was constantly being hit. There was an exclusive lock on the table, and no other query wanted to use it which was good. It was just busy all the time.

Somehow I found pg_stat_user_functions which says how much time a function (like a trigger function) executes for. There were a pair of functions that had an extremely large amount of time running and it kept rising.

I did an alter table foo disable trigger x, and once that was done, replication immediately started flowing. My data was now out-of-date because the trigger wasn't running, but that's fine as long as my publisher doesn't crash due to running out of space. After replication was caught up, I re-enabled the triggers, and things have been running okay.