CONNECTION
POSTGRESQL

How To Kill All Connections to a Database in PostgreSQL

intro

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

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
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.

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.

Retrieving all PostgreSQL backend process info in DbVisualizer
Retrieving all PostgreSQL backend process info in DbVisualizer

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:

Copy
        
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 the two true records. It means that two processes have been terminated.
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:

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

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

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

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

DbVisualizer logo

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.

About the author
Antonello Zanini

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

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

A Complete Guide to pg_dump With Examples, Tips, and Tricks

author Antonello Zanini tags pg_dump POSTGRESQL 8 min 2024-01-25
title

A Comprehensive Guide to Data Types in Postgres

author Leslie S. Gyamfi tags DATA TYPES POSTGRESQL 10 min 2024-01-23
title

SQL DISTINCT: A Comprehensive Guide

author Bonnie tags DISTINCT POSTGRESQL SQL 5 MINS 2024-01-11
title

Date Formatting in Postgres: A Comprehensive Guide

author Leslie S. Gyamfi tags DATE POSTGRESQL 7 MINS 2023-12-11
title

Casting in PostgreSQL: Handling Data Type Conversions Effectively

author Leslie S. Gyamfi tags CAST POSTGRESQL 5 MINS 2023-12-07
title

PostgreSQL Materialized Views: A Beginner's Guide

author Ochuko Onojakpor tags POSTGRESQL VIEWS 7 MINS 2023-12-04
title

Postgres List Users: Two Different Approaches 

author Antonello Zanini tags LIST USERS POSTGRESQL 3 MINS 2023-11-16

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗