How to Get the Active Users Connected to a Postgresql Database via SQL

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 list active connections on PostgreSQL?

Oh, I just found that command on PostgreSQL forum:

SELECT * FROM pg_stat_activity;

How to check connected user on psql

To get information about current connection from the psql command prompt:

\conninfo

This displays more informations, though.

To change user:

\c - a_new_user

‘-’ substitutes for the current database.

To change database and user:

\c a_new_database a_new_user

The SQL command to get this information:

SELECT current_user;

---

Examples:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432"

postgres=# \c a_new_database a_new_user
psql (12.1 (Ubuntu 12.1-1.pgdg16.04+1), server 9.5.20)
You are now connected to database "a_new_database" as user "a_new_user".

a_new_database=# SELECT current_user;
 current_user 
--------------
 a_new_user
(1 row)

---

This page list few interesting functions and variables.

https://www.postgresql.org/docs/current/static/functions-info.html

Is there a way to find active users in SQL?

I think I would just use generate_series():

select gs.d, count(*)
from (select id, min(day) as min_day, max(day) as max_day
      from t
      group by id
     ) t cross join lateral
     generate_series(t.min_day, .max_day, 1) gs(d)
group by gs.d
order by gs.d;

If you want to count everyone as active from day 1 -- but not all have a value on day 1 -- then use 1 instead of min_day.

Here is a db<>fiddle.

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 calculate an active users aggregation from an activity log in SQL?

One method is to generate the dates and then count using left join and group by or similar logic. The following uses a lateral join:

select gs.dte, al.num_accounts
from generate_series('2021-01-01'::date, '2021-01-31'::date, interval '1 day'
                    ) gs(dte) left join lateral
     (select count(distinct al.account_id) as num_accounts
      from activity_log al
      where al.created >= gs.dte - (<n - 1>) * interval '1 day' and
            al.created < gs.dte + interval '1 day'
     ) al
     on 1=1
order by gs.dte;

<n - 1> is one less than the number of days. So for one week, it would be 6.

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.

Is it possible to change the active user for a PostgreSQL connection?

You could do set role bob;, but for that to work alice must have been granted bob. Note that a non-cooperating user could just set the role back to alice again if they wanted to.

---

Related Topics