Postgresql – Close all connections to postgres without having a connection

command linepostgresqlpostgresql-9.2

I have a cron job that runs a script that connects to a remote postgres database that I do not administer. We are allotted a single connection and, on the occasion that I or a colleague log in to run some manual queries and forget to log out, my script will fail 

FATAL: too many connections for role

I need to run some command within either crontab or the script itself to close any connections for my user before attempting to run my script.

Something like 

select pg_cancel_backend(pid)
from pg_stat_activity
where pid <> pg_backend_pid();

isn't helpful because to do so, my script would have to be able to make the connection.

Any option that doesn't require being logged in already or being an administrator of the db should work as the script should take priority over anything and the only users of this role are users that should defer to the script running on time.

Thanks.

Best Answer

PostgreSQL doesn't offer anything like that. You need to keep a second connection slot spare, or have a way to request termination via a side channel provided by the server host; say a web page that runs pg_terminate_backend as superuser to kill your connection.

Alternately, you can find and kill the client side of the connection if you have local access to the client host.

A final, albeit somewhat cumbersome, option is to run a pgbouncer instance. Always connect to the pgbouncer, and have the pgbouncer instance connect to the main DB. You can then restart the pgbouncer if you have issues.

Related Question