How To Kill All Connections to a Database in PostgreSQL

Let's look at the PostgreSQL query to kill active connections in a database.

---

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client.
The PostgreSQL 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:

• 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.

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(pg_stat_activity.pid)
2 FROM pg_stat_activity
3 WHERE pg_stat_activity.datname = '<TARGET_DB_NAME>'

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

You can find the process ID of an active PostgreSQL backend in the pid column of the pg_stat_activity view. Here is what it looks like:

Retrieving all PostgreSQL backend process info in DbVisualizer.

As you can see, it contains the list of processes running on the server. In the application_name column, you can get some info about the client application the connection process refers to. If you want to list PostgreSQL databases instead, check out our guide.

If we query the database for connections that have been closed, we would get a similar result:

Note the two true records. It means that two processes have been terminated.

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(pg_stat_activity.pid)
2 FROM pg_stat_activity
3 WHERE pg_stat_activity.datname = '<TARGET_DB_NAME>' AND pid != pg_backend_pid();

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.

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

About the author

Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.