How to List Active Connections on Postgresql

How to list active connections on PostgreSQL?

Oh, I just found that command on PostgreSQL forum:

SELECT * FROM pg_stat_activity;

Right query to get the current number of connections in a PostgreSQL DB

Those two requires aren't equivalent. The equivalent version of the first one would be:

SELECT sum(numbackends) FROM pg_stat_database;

In that case, I would expect that version to be slightly faster than the second one, simply because it has fewer rows to count. But you are not likely going to be able to measure a difference.

Both queries are based on exactly the same data, so they will be equally accurate.

How can I find the number of connections currently active in PostgreSQL?

It's in the pg_catalog.pg_stat_activity view. Each row is a connection.

How can you get the active users connected to a postgreSQL database via SQL?

(question) Don't you get that info in

select * from pg_user;

or using the view pg_stat_activity:

select * from pg_stat_activity;

Added:

the view says:

One row per server process, showing database OID, database name, process ID, user OID, user name, current query, query's waiting status, time at which the current query began execution, time at which the process was started, and client's address and port number. The columns that report data on the current query are available unless the parameter stats_command_string has been turned off. Furthermore, these columns are only visible if the user examining the view is a superuser or the same as the user owning the process being reported on.

can't you filter and get that information? that will be the current users on the Database, you can use began execution time to get all queries from last 5 minutes for example...

something like that.

How to drop a PostgreSQL database if there are active connections to it?

This will drop existing connections except for yours:

Query pg_stat_activity and get the pid values you want to kill, then issue SELECT pg_terminate_backend(pid int) to them.

PostgreSQL 9.2 and above:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND pid <> pg_backend_pid();

PostgreSQL 9.1 and below:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND procpid <> pg_backend_pid();

Once you disconnect everyone you will have to disconnect and issue the DROP DATABASE command from a connection from another database aka not the one your trying to drop.

Note the renaming of the procpid column to pid. See this mailing list thread.

Dropping DB when there are active connections

Postgres 13 or hgigher

SQL DDL statement while connected to a different DB of the same cluster:

 DROP DATABASE database_name WITH (FORCE);

From the shell:

dropdb database_name --force

See:

• Force drop db while others may be connected

For older versions

Connect to a different database than the one you are going to drop - in the same db cluster. Else, your own connection will be in the way. You might use the default maintenance database "postgres" for this:

psql -h localhost -U postgres postgres

Then make sure, clients don't reconnect:

UPDATE pg_database SET datallowconn = 'false' WHERE datname = :"database_name";

Finally:

SELECT pg_terminate_backend(pid)
FROM   pg_stat_activity
WHERE  datname = :"database_name";  -- escape to avoid errors / sql injection

This :"database_name" is the syntax for SQl interpolation in psql. Double quotes for identifiers.

---

Related Topics