In PostgreSQL, every database connection is a server-side process. This makes PostgreSQL a robust multi-process rather than a multi-threaded solution. However, occasionally people want to terminate database connections. Maybe something has gone wrong, maybe some kind of query is taking too long, or maybe there is a maintenance window approaching.
Table of Contents
In this blog you will learn how to terminate queries and database connections in PostgreSQL.
In PostgreSQL there are two functions we need to take into consideration when talking about cancellation or termination:
pg_cancel_backend(pid): Terminate a query but keep the connection alivepg_terminate_backend(pid): Terminate a query and kill the connectionpg_cancel_backend ist part of the standard PostgreSQL distribution and can be used quite easily:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
test=# x Expanded display is on. test=# df+ pg_cancel_backend List of functions -[ RECORD 1 ]-------+--------------------------------------- Schema | pg_catalog Name | pg_cancel_backend Result data type | boolean Argument data types | integer Type | func Volatility | volatile Parallel | safe Owner | hs Security | invoker Access privileges | Language | internal Source code | pg_cancel_backend Description | cancel a server process' current query |
As you can see, all that’s necessary is to pass the process ID (pid) to the function. The main question is therefore: How can I find the ID of a process to make sure that the right query is cancelled?
The solution to the problem is a system view: pg_stat_activity.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
test=# d pg_stat_activity View 'pg_catalog.pg_stat_activity' Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+--------- datid | oid | | | datname | name | | | pid | integer | | | leader_pid | integer | | | usesysid | oid | | | usename | name | | | application_name | text | | | client_addr | inet | | | client_hostname | text | | | client_port | integer | | | backend_start | timestamp with time zone | | | xact_start | timestamp with time zone | | | query_start | timestamp with time zone | | | state_change | timestamp with time zone | | | wait_event_type | text | | | wait_event | text | | | state | text | | | backend_xid | xid | | | backend_xmin | xid | | | query | text | | | backend_type | text | | | |
There are a couple of things to mention here: First of all, you might want to kill a query in a specific database. The “datname” field shows you which database a connection has been established to. The “query” column contains the query string. Usually this is the best way to identify the query you want to end. However, the fact that the “query” column contains a string does not mean that the affiliated command is actually active at the moment. We also need to take the “state” column into consideration. “active” means that this query is currently running. Other entries might indicate that the connection is waiting for more user input, or that nothing is happening at all.
“leader_pid” is also important: PostgreSQL supports parallel query execution. In case of a parallel query, parallelism is also executed using separate processes. To see which process belongs to which query, the “leader_pid” gives us a clue.
Once the query has been found, we can do the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
test=# SELECT pg_cancel_backend(42000); pg_cancel_backend ------------------- t (1 row) test=# SELECT pg_cancel_backend(42353); WARNING: PID 42353 is not a PostgreSQL server process pg_cancel_backend ------------------- f (1 row) |
In case the query has been cancelled properly, we will get “true”. However, if the query is not there anymore, or in case the database connection does not exist, “false” will be returned.
So far, you have seen how a query can be ended. However, how can we terminate the entire connection?
Here is the definition of the function we will need for this purpose:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
test=# df+ pg_terminate_backend List of functions -[ RECORD 1 ]-------+--------------------------- Schema | pg_catalog Name | pg_terminate_backend Result data type | boolean Argument data types | integer Type | func Volatility | volatile Parallel | safe Owner | hs Security | invoker Access privileges | Language | internal Source code | pg_terminate_backend Description | terminate a server process |
Calling it works according to the same concept as shown before. The following listing shows an example of how this can be done:
|
1 2 3 4 5 |
test=# SELECT pg_terminate_backend(87432); pg_terminate_backend ---------------------- t (1 row) |
However, sometimes kicking out a single user is not enough.
More often than not, we have to terminate all database connections except our own. Fortunately, this is reasonably easy to do. We can again use pg_stat_activity:
|
1 2 3 4 5 6 7 8 9 10 |
test=# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname IS NOT NULL AND leader_pid IS NULL; pg_terminate_backend ---------------------- t t (2 rows) |
The first thing to exclude in the WHERE clause is our own PID which can be determined using the pg_backend_pid() function;
|
1 2 3 4 5 |
test=# SELECT pg_backend_pid(); pg_backend_pid ---------------- 75151 (1 row) |
The next important filter is to exclude database names which are NULL. Why is that important? In old versions of PostgreSQL, the system view only provided us with information about database connections. Current versions also list information about other processes. Those processes are not associated with a database (e.g. the background writer) and therefore we should exclude those. The same is true for parallel worker processes. Parallel workers will die anyway if we kill the parent process.
If you are interested in security, I would like to recommend “PostgreSQL TDE” which is a more secure version of PostgreSQL, capable of encrypting data on disk. It can be downloaded from our website. In addition to that, CYBERTEC offers 24x7 product support for PostgreSQL TDE.
+43 (0) 2622 93022-0
office@cybertec.at