The Quick Fix
To drop a slot that is currently in use, you must kill the backend process (PID) holding it open. Run these three commands in your terminal or SQL console:
-- 1. Find the PID using the slot
SELECT active_pid
FROM pg_replication_slots
WHERE slot_name = 'my_subscription_slot';
-- 2. Terminate that process
-- Replace 1234 with the PID from step 1
SELECT pg_terminate_backend(1234);
-- 3. Drop the slot immediately
SELECT pg_drop_replication_slot('my_subscription_slot');
The Root Cause
PostgreSQL locks a replication slot whenever a worker process (the Walsender) is actively streaming data. This lock prevents accidental data loss. If you try to drop the slot while a subscriber is connected, Postgres throws a protection error to keep the stream intact.
You will typically see this error in these scenarios:
- A subscriber instance is still running and attempting to pull data.
- CDC tools like Debezium or Airbyte have an active connection.
- A network glitch left a "ghost" process on the server that hasn't timed out.
- A Kubernetes pod is stuck in a crash loop, constantly reconnecting every 500ms.
Resolution Path
1. Locate the Active Process
Check which process is holding the slot hostage. The pg_replication_slots view shows the active_pid. If this column contains a number, the slot is locked.
SELECT slot_name, active, active_pid
FROM pg_replication_slots
WHERE slot_name = 'my_subscription_slot';
2. Identify the Client
Don't fly blind. It is better to know what you are about to kill. Join the slots view with pg_stat_activity to see the application name and IP address.
SELECT
s.slot_name,
a.application_name,
a.client_addr,
a.backend_start
FROM pg_replication_slots s
JOIN pg_stat_activity a ON s.active_pid = a.pid
WHERE s.slot_name = 'my_subscription_slot';
3. Terminate and Drop
If a service like Debezium is running, it will likely reconnect the moment you kill the PID. Stop the consumer application first. If you cannot stop the app, you must execute the termination and the drop command in rapid succession.
-- Kill the connection
SELECT pg_terminate_backend(active_pid)
FROM pg_replication_slots
WHERE slot_name = 'my_subscription_slot';
-- Immediately drop the slot
SELECT pg_drop_replication_slot('my_subscription_slot');
Winning the Reconnection Race
Managed services or Kubernetes pods often reconnect in milliseconds. If the slot becomes active again before you can type the next command, use these strategies.
Option A: Disable the Subscriber (Best Practice)
On the destination database, stop the subscription. This is the cleanest way to release the slot.
-- Run this on the SUBSCRIBER/DESTINATION database
ALTER SUBSCRIPTION my_sub DISABLE;
ALTER SUBSCRIPTION my_sub SET (slot_name = NONE);
Now go back to the publisher and drop the slot without any interference.
Option B: The Semicolon Shortcut
Run both commands on a single line. This minimizes the window for a client to reconnect.
SELECT pg_terminate_backend(active_pid) FROM pg_replication_slots WHERE slot_name = 'my_subscription_slot'; SELECT pg_drop_replication_slot('my_subscription_slot');
Why This Matters for Disk Space
An active but lagging replication slot is a silent killer for storage. It forces PostgreSQL to keep WAL files on disk indefinitely. We have seen cases where a single forgotten slot consumed over 200GB of pg_wal space in a few hours. Once you drop the slot, Postgres will automatically reclaim this space during the next checkpoint.
Common Pitfalls
- Permissions: You need superuser rights or the
REPLICATIONrole to manage slots. - Cloud Restrictions: On AWS RDS,
pg_drop_replication_slotworks normally, but ensure you aren't using a managed AWS DMS task that automatically recreates the slot. - Primary vs Replica: You can only drop a slot on the node where it was created. If you are on a Read Replica, the command will fail; you must run it on the Primary instance.