Luis Rosa wrote:thank you for the reply,
I am solving this problem in another way, which is off this topic but might help someone looking for this answer like me.
So I wanted to close all connections used by c3p0.ComboPooledDataSource and stop it from doing more connections so that I could be able to drop and restore a database, all this in production server without disturbing other applications or databases.
the best approach that I found so far, and seems even more reliable, is to do all this work just on the database server , in this case postgres
in a nutshell :
- stop database from having newer connections (only to this database) UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'database';
- get list of existing connections psql -U swoffice -t -c "select procpid from pg_stat_activity where datname='swoffice' and current_query not like 'select procpid from pg_stat_activity%';"
- kill connections (might cause data corruption, so backup first)
for(int procpid : procpidList)
psql -U swoffice -t -c "select pg_terminate_backend(procpid) from pg_stat_activity where datname='database' "
now free of connections to drop and create db using pg_dump and pg_restore
What you are talking about is after the application shut down or got hang and nothing can be done. But the OP seems to be more concerned about controlling it form the application itself which might be the case for platform/framework development.