How To Kill All Connections to a Database in PostgreSQL

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:

• Dropping a database: To perform a DROP DATABASE query on a PostgreSQL database, you must ensure that there are no active connections referred to it. Otherwise, the operation would result in the error database "" is being accessed by other users.
• Renaming a database: Similar to dropping a database, renaming a PostgreSQL database with an ALTER DATABASE query requires terminating all active sessions related to it.
• Testing the environment: When you want to test special conditions or situations, it can be useful to reset the state of the database by clearing all connections. This way, each test will start from a clean starting point. That is especially useful in the case of a stress test.
• Emergency situations: In critical scenarios such as security vulnerabilities, data corruption, or severe performance issues, immediately closing all connections can prevent further damage or unauthorized access.
• Database maintenance: Before performing maintenance tasks like schema changes, upgrades, or data migrations, you need to ensure that no active connections will interfere with the process. Removing all sessions enables you to safely execute those tasks without interruptions.

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:

Replace 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:

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:

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:

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 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:

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.