0

I use logical replication to move changes from my primary to a replica. Today, to update the replica, I:

  • disabled the subscription on the replica (ALTER SUBSCRIPTION foo DISABLE;)
  • upgraded it to postgresql 14 (it was 10 before)
  • re-enabled the subscription on the replica (ALTER SUBSCRIPTION foo ENABLE;)

After doing that, I'm getting logs that say:

2023-05-19 01:01:09 UTC:100.20.224.120(56536):django@courtlistener:[29669]:STATEMENT: CREATE_REPLICATION_SLOT "pg_18278_sync_86449755_7234675743763347169" LOGICAL pgoutput USE_SNAPSHOT
2023-05-19 01:01:09 UTC:100.20.224.120(56550):django@courtlistener:[29670]:ERROR: replication slot "pg_18278_sync_16561_7234675743763347169" does not exist
2023-05-19 01:01:09 UTC:100.20.224.120(56550):django@courtlistener:[29670]:STATEMENT: DROP_REPLICATION_SLOT pg_18278_sync_16561_7234675743763347169 WAIT
2023-05-19 01:01:09 UTC:100.20.224.120(56550):django@courtlistener:[29670]:ERROR: all replication slots are in use
2023-05-19 01:01:09 UTC:100.20.224.120(56550):django@courtlistener:[29670]:HINT: Free one or increase max_replication_slots.

So I followed the hint and increased my max_replication_slot parameter from 30 to 80. Prior to this, I had 30 slots open on the publisher. Now I have the same error as above, but with 80 slots:

select * from pg_replication_slots ;
                 slot_name                  |  plugin  | slot_type | datoid |   database    | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn  | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase 
--------------------------------------------+----------+-----------+--------+---------------+-----------+--------+------------+------+--------------+--------------+---------------------+------------+---------------+-----------
 pg_18278_sync_86449408_7234675743763347169 | pgoutput | logical   |  16428 | courtlistener | f         | t      |       6906 |      |    859962500 | EA5/954A9F18 |                     | reserved   |               | f
 pg_18278_sync_20492279_7234675743763347169 | pgoutput | logical   |  16428 | courtlistener | f         | f      |            |      |    859962448 | EA5/9548EDF0 | EA5/9548EE28        | reserved   |               | f
 pg_18278_sync_16940_7234675743763347169    | pgoutput | logical   |  16428 | courtlistener | f         | f      |            |      |    859962448 | EA5/9548EE60 | EA5/9548EE98        | reserved   |               | f

-----8< ---- MANY MORE ROWS 8<-----

 pg_18278_sync_86449744_7234675743763347169 | pgoutput | logical   |  16428 | courtlistener | f         | f      |            |      |    859962490 | EA5/954A8980 | EA5/954A89B8        | reserved   |               | f
 pg_18278_sync_62025450_7234675743763347169 | pgoutput | logical   |  16428 | courtlistener | f         | f      |            |      |    859962467 | EA5/95498898 | EA5/954988D0        | reserved   |               | f
 pg_18278_sync_16919_7234675743763347169    | pgoutput | logical   |  16428 | courtlistener | f         | f      |            |      |    859962438 | EA5/9548D8E8 | EA5/9548D920        | reserved   |               | f
 pg_18278_sync_86449931_7234675743763347169 | pgoutput | logical   |  16428 | courtlistener | f         | f      |            |      |    859962460 | EA5/95494090 | EA5/954940C8        | reserved   |               | f
 pg_18278_sync_16561_7234675743763347169    | pgoutput | logical   |  16428 | courtlistener | f         | f      |            |      |    859962465 | EA5/95498370 | EA5/954983A8        | reserved   |               | f
 pg_18278_sync_16698_7234675743763347169    | pgoutput | logical   |  16428 | courtlistener | f         | f      |            |      |    859962456 | EA5/95493810 | EA5/95493848        | reserved   |               | f
 pg_18278_sync_86449722_7234675743763347169 | pgoutput | logical   |  16428 | courtlistener | f         | f      |            |      |    859962492 | EA5/954A8E00 | EA5/954A8E38        | reserved   |               | f
(80 rows)

I can't figure out what's going on here, but it's not great! I could keep increasing the max_replication_slots parameter, but boy that doesn't sound great. There's only one publication on this server, so why does it need 80+ slots?

EDIT:

I forgot, we actually have a web interface for this, a bit. You can see the many slots here (ignore the replica server's slots):

https://www.courtlistener.com/monitoring/replication-lag/

Improve this question
2
  • Is that section of the log you show repeated another 79 times with different slot names? All within a few seconds of each other? What does the log on the replica said show?
    – jjanes
    May 19 at 16:05
  • Yes, it was 80 slots like that. The replica logs showed that we needed more slots, so I bumped it to 200. That was finally enough (it used 150 of them), and then I saw COPY commands failing to work. I think what happened is that pg_upgrade nuked the subscription's LSN, so when I re-enabled it, it started from scratch, doing a new copy. I eventually had to nuke the subscription and start a new one with copy_data=false. I asked about this on the postgres mailing list too.
    – mlissner
    May 19 at 23:45

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.