PostgreSQL Terminate Active Connections

Sometimes we need to terminate active connections to a database forcefully. Doing such requires two things, determining which active connections to target and running the command to terminate them. This post shows how to terminate active connections using some SQL commands, and we tested PostgreSQL 10.5.

Which PostgreSQL Connections To Target

Before we terminate some PostgreSQL active connections, we need to identify which ones to target. We can do it by running queries against PostgreSQL using some system tables. One table is pg_stat_activity. Consider the following codes.

When we run the codes, we get the following results on our machine.

Note that we need to use a superuser to invoke the SELECT statement. Otherwise, we will get the following error.

To create a superuser, please read this post. Once we created a superuser, sign in with the new credentials via SQL IDEs to run SQL commands.

Terminate Active Connections

From a list of rows emanating from the pg_stat_activity table, we can choose which connections to target. Then, we can use a PostgreSQL stored procedure pg_terminate_backend.

The pid field refers to the column in the pg_terminate_backend table. We can also terminate PostgreSQL active connections belonging to a particular user. Consider the following sample SQL statement.