Docu review done: Fri 26 Jan 2024 04:38:51 PM CET

Table of content

• Intern commands
• Maintaining objects in psql
• Shows db size with select command
• Show connected users for all DBs
  • with pg_user
  • with pg_stat_activity
• Special cases
  • user Table

Intern commands

Maintaining objects in psql

Shows db size with select command

$ select pg_size_pretty(pg_database_size('databasename'));
 databasename
 ----------------
  15 MB
  (1 row)

Show connected users for all DBs

pg_user will list you all current configured users with a small permissions overview

with pg_user

$ select * from pg_user;
  usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
-----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 username1 |    11111 | f           | f        | f       | f            | ******** |          |
 username2 |    22222 | f           | f        | f       | f            | ******** |          |
 username3 |    33333 | t           | f        | f       | f            | ******** |          |
 username4 |    44444 | t           | t        | t       | t            | ******** |          |
 username5 |    55555 | f           | f        | f       | f            | ******** |          |
 username6 |    66666 | f           | f        | f       | f            | ******** |          |
(6 rows)

with pg stat activity

gp_stat_activity shows you which user is performing which action right now (or was the last action)

$ \x
$ select * from pg_stat_activity
-[ RECORD 1 ]----+---------------------------------
datid            |
datname          |
pid              | 24624
usesysid         |
usename          |
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2020-07-16 21:38:59.563585+02
xact_start       |
query_start      |
state_change     |
wait_event_type  | Activity
wait_event       | AutoVacuumMain
state            |
backend_xid      |
backend_xmin     |
query            |
backend_type     | autovacuum launcher
-[ RECORD 2 ]----+---------------------------------
datid            |
datname          |
pid              | 24626
usesysid         | 10
usename          | postgres
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2020-07-16 21:38:59.564255+02
xact_start       |
query_start      |
state_change     |
wait_event_type  | Activity
wait_event       | LogicalLauncherMain
state            |
backend_xid      |
backend_xmin     |
query            |
backend_type     | logical replication launcher
-[ RECORD n ]----+---------------------------------
...

List blocking queries

To display blocking queries, you can run the following psql command SELECT activity.pid, activity.usename, activity.query, blocking.pid AS blocking_id, blocking.query AS blocking_query FROM pg_stat_activity AS activity JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

 pid | usename | query | blocking_id | blocking_query
-----+---------+-------+-------------+----------------
1337 | gustaf  | <...> | 7331        | <...>
(1 rows)

Special cases

user Table

Lets assume some application created a table with the name user like gitea does and you want to query the data from it. If you just run the sql comand select * from user; it will only return you one result:

gitea=$ select * from user;
   user
----------
 postgres
(1 row)

Of course you konw, that this can not be the real output, because you have other users working in the application without issues. The reason, why you just get one result is, that the table user exists also in postgres as well.

There are two option you can do to prevent the query from asking the wrong table.

1. Use doublequoats around the table name, like that: "user"
2. Specify the schema + doublequoats, like that: public."user"

This will give you the real result which will look like that:

gitea=$ select name from "user";
          name
-------------------------
 maxmusermansusername
 franzderkanns
 gustaf
 sepplmeier
 ...
(n rows)

gitea=# select name from public."user";
          name
-------------------------
 maxmusermansusername
 franzderkanns
 gustaf
 sepplmeier
 ...
(n rows)