intro
Follow this tutorial and learn how to kill all connections in a PostgreSQL server through different approaches!
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.
In this guide, you will dig into the process of closing active connections in PostgreSQL, exploring use cases and different approaches.
Let’s dive in!
What Does It Mean to Kill a PostgreSQL Database Connection?
Killing a PostgreSQL database connection means forcefully terminating an active session connected to the database. Each connection consumes server resources, so this action is useful for resolving conflicts, stopping problematic queries, and reducing server load.
When a connection is killed, any ongoing transactions are rolled back, and the session is closed, freeing up resources and preventing potential issues.
Kill All Connections in a PostgreSQL Server: Use Cases
In PostgreSQL, killing all connections refers to terminating all active sessions established by clients to a database. Here are some scenarios where this operation may be required:
Time to see how to kill connections in Postgres!
Insights Into the pg_stat_activity View
Before seeing how to drop connections to a database in a PostgreSQL server, you need to learn more 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.
The most important columns to focus your attention on are:
You are ready to use this view to kill all connections to a Postgres database. If you want to list PostgreSQL databases instead, read 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' AND leader_pid IS NULL;
Replace TARGET_DB_NAME with the name of the database you want to close sessions for.
Warning: The above query will also terminate all the queries running on the specified database.
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 processes on the database server based on their ID.
Note: Use pg_cancel_backend() if you want to terminate a process–such as a query–but keep the connection alive.
Note that the IS NULL condition on leader_pid in the WHERE clause is an optimization to avoid sending too many kill signals. The reason is that, by killing the parent process, parallel workers will die as a result. So, you can ignore processes associated with parallel workers by forcing the IS NULL condition.
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:
As you can see, the two connections to the games database have been terminated. Be sure to run that query from a database connection that is not in TARGET_DB_NAME. Otherwise, the operation will fail with the error message below:
1
1) [Code: 0, SQL State: 57P01] FATAL: terminating connection due to administrator command
2
2) [Code: 0, SQL State: 08006] 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() AND leader_pid IS NULL;
pg_backend_pid() is a system information function that returns the process ID of the current session.
This time, you will achieve the target outcome with no errors:
Et voilà! You know now how to terminate connections in PostgreSQL.
Conclusion
In this article, you learned what it means to kill a connection to a database in a PostgreSQL server. In detail, killing all connections is a powerful technique required in 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 feature-rich database client with full PostgreSQL support such as DbVisualizer comes in. From the same tool, you can connect to dozens of database technologies, visually explore their table structure, optimize queries, and generate ERD-like schemas with a single click. Download DbVisualizer for free today!
FAQ
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 kill all 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 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 datname rows with NULL values 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 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 in this article, 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.
How to kill all other active connections to your database in PostgreSQL?
To terminate all other database connections to the db_name Postgres database except for yours, run:
1
SELECT pg_terminate_backend(pg_stat_activity.pid)
2
FROM pg_stat_activity
3
WHERE pg_stat_activity.datname = 'db_name' AND pid != pg_backend_pid() AND leader_pid IS NULL;
Or, equivalently, execute:
1
SELECT pg_terminate_backend(pid)
2
FROM pg_stat_get_activity(NULL::integer)
3
WHERE datid = (
4
SELECT oid
5
FROM pg_database
6
WHERE datname = 'db_name'
7
) AND pid != pg_backend_pid() AND leader_pid IS NULL;
The second query uses an SQL subquery. Find out more about that mechanism in our guide on SQL subqueries.
What is the difference between pg_cancel_backend() and pg_terminate_backend()?
The difference between pg_cancel_backend() and pg_terminate_backend() in PostgreSQL is that pg_cancel_backend() attempts to cancel an active process (such as a long query) without terminating the connection, allowing the session to continue. In contrast, pg_terminate_backend() forcefully ends the entire session, terminating the connection and rolling back any ongoing transactions.
