Technology Insights

Postgres Table pg_stat_activity Table Fields Explained

InsightsLeave a comment

The pg_stat_activity table will show you what activity is currently happening on your PostgreSQL database server. The information will detail the database the activity is occuring on, the username initiating the activity, the length of time the acitivy has been occuring for, and much more.

Below is example output from running a SELECT query against the pg_stat_activity table. The pg_stat_activity table is located by default in the postgres database and can be accessed as shown below.

Technology Errors

Postgres ERROR: database “dev” is being accessed by other users

Errors16 Comments

If you are having issues dropping a postgres database the issue is more than likely that a user is accessing the database you are attempting to drop. There isn’t technically a way to force drop the postgres database however there are steps you can take to prevent others from accessing the database and if needed kick the users whom are accessing the database.

If you receive an error similar to the below the issue with your attempt to drop the database is the fact that either a user is accessing the database or an application of some sort has an open connection to the database. In the second scenario PostgreSQL considers the application the same as a user. In the code below the name of the example database is dev.

Please note that dropping the database will destroy all data located within the database. I strongly suggest always backing up your data before dropping the database. You can backup the data with pg_dump and regardless of if you think you will need the data or not it is best to keep a copy just in case.