Adding a Postgres kill-switch for firefighting

A lot of Postgres firefighting situations are related to connection limits.

When debugging, and doing changes, it would be nice to just “terminate all connections”, but this is not always possible.

Trying to terminate all connections might give you this message:

db error: FATAL: remaining connection slots are reserved for non-replication superuser connections

All connections get exhausted, and you can’t even access the db yourself it to do the necessary operations to fix the problem.

On a hosted service (like DigitalOcean or AWS), you don’t have access to any “superuser” slots. You are left helpless. There is nothing you can do.

The solution

In the firefighting situation, we can’t trust there to be any free slots available for us. The solution is to have a connection that is always connected. A sole Postgres user and connection, only for the purpose of terminating and liberating other connections if necessary.

Create the user:

-- Create the user
CREATE USER connection_terminator WITH PASSWORD 'XXX';

-- Grant permissions
GRANT pg_signal_backend TO connection_terminator;
GRANT CONNECT ON DATABASE layer3_app TO connection_terminator;

The query to terminate all connections:

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE pid != pg_backend_pid()AND usename != current_user;;

We’ll need to have this connected and ready all the time. For this, we need to deploy some code on a server.

I made a simple repo to deploy a server that makes the connection, and displays a single button “terminate all connections”. https://github.com/larskarbo/postgres-terminator

This should be useful for future firefighting situations!