Why does postgresql have so many replication slots open?

Question

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/

Answer 1

import psycopg2

# Database connection parameters
db_params = {
    'dbname': 'your_database',
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_host',
    'port': 'your_port'
}

def get_replication_slots():
    conn = psycopg2.connect(**db_params)
    cursor = conn.cursor()
    cursor.execute("SELECT slot_name FROM pg_replication_slots WHERE active = FALSE;")
    slots = cursor.fetchall()
    cursor.close()
    conn.close()
    return slots

def drop_replication_slot(slot_name):
    conn = psycopg2.connect(**db_params)
    cursor = conn.cursor()
    cursor.execute(f"SELECT pg_drop_replication_slot('{slot_name}');")
    conn.commit()
    cursor.close()
    conn.close()

def main():
    slots = get_replication_slots()
    for slot in slots:
        slot_name = slot[0]
        print(f"Dropping replication slot: {slot_name}")
        drop_replication_slot(slot_name)

if __name__ == '__main__':
    main()