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 to a PostgreSQL Database: Use Cases and Scenarios
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.
pg_stat_activity: Column Table
These are all the columns contained in the pg_stat_activity system view:
| Column | Data Type | Description |
|---|---|---|
datid | oid | OID of the database this backend is connected to |
datname | name | Name of the connected database |
pid | integer | Process ID of this backend |
leader_pid | integer | Process ID of the parallel group leader or leader apply worker. NULL if the process is not in a parallel operation |
usesysid | oid | OID of the user logged into this backend |
usename | name | Name of the user logged into this backend |
application_name | text | Name of the application connected to this backend |
client_addr | inet | IP address of the connected client. NULL for Unix socket connections or internal processes |
client_hostname | text | Hostname of the client (resolved via reverse DNS). Only available for IP connections when log_hostname is enabled |
client_port | integer | TCP port used by the client, or -1 if using a Unix socket. NULL for internal processes |
backend_start | timestamp with time zone | Timestamp when the backend process started (i.e., when the client connected) |
xact_start | timestamp with time zone | Timestamp when the current transaction started, or NULL if no transaction is active |
query_start | timestamp with time zone | Timestamp when the currently active query (or last executed query) started |
state_change | timestamp with time zone | Timestamp when the backend state was last changed |
wait_event_type | text | Type of event the backend is waiting for (if any) |
wait_event | text | Specific event name the backend is waiting on (if any) |
state | text | Current state of the backend: active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, or disabled |
backend_xid | xid | Top-level transaction ID of this backend, if any |
backend_xmin | xid | The backend’s xmin horizon, used for tracking transaction visibility |
query_id | bigint | Identifier of the most recent query. Only available if compute_query_id is enabled |
query | text | Text of the backend’s most recent query (truncated by default at 1024 bytes, configurable via track_activity_query_size) |
backend_type | text | Type of backend process, such as autovacuum worker, logical replication worker, client backend, walwriter, etc. Extension-defined background workers may have additional types |
If you are not familiar with some of the types mentioned above, discover all PostgreSQL data types.
Dropping All 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.
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.
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.
Warning: The above query will also terminate all the queries running on the specified database. That is because each PostgreSQL backend process is typically responsible for maintaining a connection for a client or executing a single query. So, when the process is terminated, the query associated with that process is stopped. In particular, any transaction associated with that process is rolled back.
If you want to kill only active connections, enforce the state = 'active' condition:
1
SELECT pg_terminate_backend(pid)
2
FROM pg_stat_activity
3
WHERE datname = 'TARGET_DB_NAME' AND leader_pid IS NULL AND state = 'active';
Keep in mind that only users with a SUPERUSER role can run these operations. That reason is that these queries should be used sparingly and only when truly required.
Terminate All Queries but Keep Connections Alive
If you want to terminate a process–such as a query—but keep the connection alive, use pg_cancel_backend():
1
SELECT pg_cancel_backend(pid)
2
FROM pg_stat_activity
3
WHERE datname = 'TARGET_DB_NAME' AND leader_pid IS NULL;
Remember that an app connected to the database can recover from a failed query, but if the session is closed, it will need to open a new cursor to re-establish the connection and continue operations.
Killing All Connections in PostgreSQL: Complete Example
Try to launch the query for closing all PostgreSQL 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 kill/stop a query in PostgreSQL?
First, check the running processes by executing:
1
SELECT * FROM pg_stat_activity
2
WHERE state = 'active';
Find the PID (<pid_of_the_process>) of the process you want to kill. Then, you have two options:
Option #1: Launch the query below to initiate a request to terminate the query gracefully, which may take some time to complete, though the query will return immediately:
1
SELECT pg_cancel_backend(<pid_of_the_process>);
Option #2: Execute this query to forcefully terminate the backend process:
1
SELECT pg_terminate_backend(<pid of the process>);
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. So, pg_cancel_backend()may take some time to complete, returning true in case of a gaceful cleanup. On the other hand, pg_terminate_backend() acts like a "hard kill," forcibly terminating the backend process without waiting for cleanup.
