There may come a time where you want more insight into your database; to understand who is doing what...and when.
You might already be familiar with some of Postgres' system functions and views and, in this short guide, we will take a look at the catchy Cumulative Statistics System
, providing insights into replication slots, connections, the WAL and more (that you can read about here)
All connections can be viewed using pg_stat_activity
and you can simply start with:
SELECT * FROM pg_stat_activity;
This gives you everything but you will really want some key columns, let's focus on these:
pid
- Process ID of the connectiondatname
- The database that this connection is connected tousename
- The username of the connectionapplication_name
- Name of the applicationclient_addr
- IP of the client connectedclient_port
- The port that the connection is usingquery
- The most recent query run by the connectionstate
- State of the connection (typicallyidle
oractive
)backend_type
- This is good information about the purpose/type of the connection, with options like:client backend
,background writer
,walsender
With this in mind, let's use this new knowledge to run some more in-depth queries:
SELECT * FROM pg_stat_activity WHERE state = 'active';
SELECT * FROM pg_stat_activity WHERE usename = 'pgbouncer';
Note: This is an initial response for pgbuncer.get_auth
and will not list all pgbouncer connections as pgbouncer will connect with the user specified in the connection string.
SELECT * FROM pg_stat_activity WHERE application_name ilike '%Supavisor%';
Viewing is a great start, the queries in the previous section enable us to get insights, either as a one off or continuously.
How about killing connections we do not want or need? This is where we use pg_terminate_backend
:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = '1211'; -- Replace 12345 with the actual process ID
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'pgbouncer';
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'Supavisor';