CODEWITHSUNDEEP
javadatabasepostgresql
JSP
JSP

Reputation: 587

Check for connections before dropping DB

I'm trying to drop a DB. Before dropping the DB I just want to check if there are any clients connected to that particular DB.

If No, I can drop it.

If Yes, I want to give the user some useful information.

I know this query, so I can see the number connections made to that DB.

select count(*) from pg_stat_activity

How can I do this in Java?

Upvotes: 0

Views: 187

Answers (1)

khampson
khampson

Reputation: 15336

I would not bother checking pg_stat_activity, since even if you did, someone could access the database right afterward before you initiate the DROP DATABASE command.

Rather, I would recommend you just issue the DROP DATABASE command using whatever method you're using the interact with Postgres (JDBC, MyBatis, etc.) and surround it with a try/catch block. The DROP DATABASE command will fail if there are users connected to it, so you can simply execute the command and then handle the exception -- perhaps sleep for a few seconds and try again, or something to that effect.

However, if you wanted to force any active users off of the database, pg_stat_activity would come into play and you could terminate their backend connections, as outlined here: How to drop a PostgreSQL database if there are active connections to it? and then issue the DROP DATABASE command.

Edit in response to comment from OP:

Like I said, since the DROP command would fail if there are connections to the DB, I wouldn't bother checking first, but if you really want to see what the connections are (except for the connection you're using the check), you can use a similar query to what was in the link above, just with the pg_terminate_backend call.

SELECT *
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
  AND pid <> pg_backend_pid();

Upvotes: 2

Related Questions