0

I've been accessing a postgreSQL database with python3 and psycopg2.

I encountered an error that implied I had open connections. When I ran SELECT * FROM pg_stat_activity a bunch of connections under my username came up. They all have a state of 'idle', and many of them show a "query" value that I never ran (ex: select 1, show search_path).

I've seen this on how to close them automatically. And, I've also read that using pg_terminate_backend is dangerous and will cause the database to restart. How do I close these connections naturally with python3 or SQL? It is a company production database, so restarting it or being the admin isn't an option. Let me know if I must be an admin to solve this issue.

klin
  • 112,967
  • 15
  • 204
  • 232
singmotor
  • 3,930
  • 12
  • 45
  • 79
  • i have the same problem, i'm using c3p0 and there's lots of `idle` connection in `pg_stat_activity` and the number keeps growing. Did you solve this problem? – Zip Aug 04 '18 at 19:10

1 Answers1

2

You can safely terminate your own idle backend processes using pg_terminate_backend(pid int). If you try to terminate a process you haven't access to, you'll get an error message and nothing special will happen. Don't try to terminate active processes.

Per the documentation:

pg_terminate_backend(pid int)

Terminate a backend. This is also allowed if the calling role is a member of the role whose backend is being terminated or the calling role has been granted pg_signal_backend, however only superusers can terminate superuser backends.

Community
  • 1
  • 1
klin
  • 112,967
  • 15
  • 204
  • 232