Database Reference
In-Depth Information
SELECT
*
FROM
pg_stat_activity
;
Additionally, the command provides details of the last query running on each con‐
nection, the connected user (
usename
), the database (
datname
) in use, and the start
times of the query. You need this view to grab the process IDs of connections that
you want to terminate.
2. Now cancel all active queries on a connection:
SELECT
pg_cancel_backend
(
procid
)
This does not terminate the connection itself, though.
3. Kill the connection:
SELECT
pg_terminate_backend
(
procid
)
If you have not canceled the queries on the connection, they are all rudely termi‐
nated now. This will be your weapon of choice prior to a restore to prevent an eager
user from immediately restarting a canceled query.
PostgreSQL lets you embed functions that perform actions within a regular
SELECT
query. So, although
pg_terminate_backend
and
pg_cancel_backend
can act on only
one connection at a time, you can kill multiple connections by wrapping them in a
SELECT
. For example, let's suppose you want to kill all connections belonging to a role
with a single blow. Run this SQL command on version 9.2 and later:
SELECT
pg_terminate_backend
(
pid
)
FROM
pg_stat_activity
WHERE
usename
=
'
some_role
'
;
or before version 9.2:
SELECT
pg_terminate_backend
(
procpid
)
FROM
pg_stat_activity
WHERE
usename
=
'
some_role
'
;
The
pg_stat_activity
view has changed considerably since version 9.1 with the re‐
naming and addition of new columns.
procpid
is now
pid
.
Roles
PostgreSQL represents accounts as
roles
. Roles that can log in are called
login roles
. Roles
can be members of other roles; roles that contain other roles are called
group roles
. (And
yes, group roles can be members of other group roles and so on ad infinitum, but don't
go there unless you have a knack for hierarchical thinking.) Roles that are group and
can log in are called
group login roles
. However, for easier maintainability and security,
DBAs generally don't grant login rights to group roles. A role can be designated as
superuser
. Superuser roles have unfettered access to the PostgreSQL service.
