Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Apr 5 at 3:06

This question came from our site for professional and enthusiast programmers.

2  
lsof -n -P -i tcp:5432 and filtering on the remote IP might help to select the offending local programs and kill them. –  Daniel Vérité Mar 4 at 13:24

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.