intro
Let's look at the PostgreSQL query to kill active connections in a database.
Situations where you have to terminate the active connections to a database are common in the life of every DBA. For example, that is required to rename or drop a database. To perform such operations, you first have to kill all connections. Similarly, you may need to close all client sessions except for yours.
This tutorial will guide you through the process of closing active connections in PostgreSQL, exploring use cases and different approaches.
Use Cases
In PostgreSQL, killing all connections refers to terminating all active sessions established by clients to a database. Here are some scenarios where you may need to do so:
Let’s now see how to achieve the desired result.
Insights About the pg_stat_activity View
pg_stat_activity is a PostgreSQL system view that stores a row for each process running on the DBMS server. In detail, it contains useful information about the current activity of each process.
Some of the most important columns to focus on are:
- pid: The ID of the process running on the database server.
- datname: The name of the database the process is connected to.
- state: The current state of the process. The possible values are: active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, and disabled.
- query: The text of the most recent query executed by the process.
- leader_pid: The ID of the parallel group leader or apply worker. When NULL, it means that the current process is a parallel group leader or apply worker, or it does not participate in any parallel operation.
- application_name: The name of the client application the connection process refers to.
Let’s now see how to use this query to drop all connections to a Postgres database. If you want to list PostgreSQL databases instead, check out our guide.
Dropping All Active Connections to a PostgreSQL Database
To kill all active connections to a PostgreSQL database, execute the query below:
1
SELECT pg_terminate_backend(pid)
2
FROM pg_stat_activity
3
WHERE datname = '<TARGET_DB_NAME>'
4
AND leader_pid IS NULL;
Replace <TARGET_DB_NAME> with the name of the database you want to close sessions for.
pg_terminate_backend()
is a special administration function that sends the SIGINT
or SIGTERM
signal to the backend process identified by the ID passed as a parameter. In other words, it kills a process by ID on the database server. Use pg_cancel_backend() instead if you want to terminate a process–such as a query–but keep the connection alive.
Note that the leader_pid IS NULL
condition in WHERE
is an optimization. By killing the parent process, parallel workers will die as a result, so we can ignore them.
Keep in mind that only users with a SUPERUSER
role can run this operation. This is because it is a solution that should be used sparingly and only when truly required.
Try to launch the query for closing connections and you will get a similar result:
Note that the two connections to games
have been terminated. Be sure to run that query from a database connection that is not in . Otherwise, the operation will fail with the error message below:
An I/O error occurred while sending to the backend
To avoid that, you can modify the query to kill all connections except for yours, as below:
1
SELECT pg_terminate_backend(pid)
2
FROM pg_stat_activity
3
WHERE datname = '<TARGET_DB_NAME>' AND pid != pg_backend_pid();
4
AND leader_pid IS NULL;
pg_backend_pid()
is a system information function that returns the process ID of the current session.
Et voilà! You can now terminate connections in PostgreSQL.
Conclusion
In this article, you understood that killing connections to a PostgreSQL database is a powerful technique to manage several scenarios, including dropping or renaming a database and ensuring a clean environment for testing. By following the instructions provided here, you learned how to safely terminate connections and proceed with your database operations.
Managing database connections can easily become a complex task. This is where a full-featured database client with complete PostgreSQL support such as DbVisualizer comes in. From the same tool, you can connect to dozens of database technologies, visually explore their structure, and take database management to the next level. Download DbVisualizer for free!
FAQ
Let’s answer some interesting questions related to database connections.
How to get the list of active connections in PostgreSQL?
You can retrieve the list of active connections in PostgreSQL by executing the SQL query below:
1
SELECT * FROM pg_stat_activity;
This provides information about the current connections, including their process IDs (PIDs) and associated details.
How to terminate all database connections in PostgreSQL?
To terminate all connections to all databases in a Postgres server, run the following query:
1
SELECT pg_terminate_backend(pid)
2
FROM pg_stat_activity
3
WHERE pid != pg_backend_pid()
4
AND datname IS NOT NULL
5
AND leader_pid IS NULL;
In older PostgreSQL versions, pg_stat_activity only covered database connections. In newer versions, it also includes information about various processes unrelated to a specific database, like background writers and parallel workers. Excluding NULL datname rows ensures that you are killing only database-related processes.
What are the privileges or permissions required to kill processes in PostgreSQL?
To kill processes in PostgreSQL, you need to connect to the database with the postgres admin account or an account with SUPERUSER
role. These are the users that have the necessary privileges to terminate processes.
How to terminate all database connections in PostgreSQL?
To terminate all connections to all databases in a Postgres server, run the following query:
How to rename a database in PostgreSQL?
To rename a database in PostgreSQL, you can use the ALTER DATABASE
statement followed by the RENAME TO
clause. For example, to rename a table from "old_name" to "new_name", execute:
1
ALTER TABLE old_name RENAME TO new_name;
Does pg_terminate_backend() drop connections together or one by one?
pg_terminate_backend()
terminates connections individually. If you feed the command with the PIDs from pg_stat_activity
as explained earlier, it will be executed for each active connection, one at a time.
What happens to active transactions when all connections to a PostgreSQL database are terminated?
When all connections to a PostgreSQL database are terminated, any active transactions are rolled back. This ensures data integrity and prevents any partial or inconsistent updates from being committed.
If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.