How to Terminate a PostgreSQL Connection

To end a PostgreSQL connection, first identify its backend in pg_stat_activity, then call pg_terminate_backend(pid) for that specific session. If you only need to stop its current query and keep the connection available, use pg_cancel_backend(pid) instead. Avoid operating-system process kills for ordinary session management, because they bypass PostgreSQL’s session controls and can affect server stability.

Inspect sessions and choose a target

Run a query against the database server to inspect active and idle sessions. The pid identifies a backend process, datname identifies its database, usename identifies its role, application_name can help identify the client, and state describes whether it is active or idle. Review query and state_change as well when deciding whether a session is safe to stop. Access to query text may itself be restricted.

Use pg_backend_pid() to identify the session running your inspection query. Exclude that PID so the command cannot terminate its own connection. Narrow the selection by database and, when useful, role or application. Before terminating a production session, confirm the owner and expected impact; terminating a session rolls back its in-progress transaction.

For example, inspect candidate sessions with this SQL expression (replace the database name and add filters appropriate to your environment): SELECT pid, usename, application_name, state, query FROM pg_stat_activity WHERE datname = 'my_database' AND pid <> pg_backend_pid();. This is a selection query only; it does not end any sessions.

This Python illustration uses sample session records, prints [4101], and does not connect to PostgreSQL.

sessions = [
    {"pid": 4101, "database": "shop", "state": "idle"},
    {"pid": 4102, "database": "shop", "state": "active"},
    {"pid": 4103, "database": "reports", "state": "idle"},
]
current_pid = 4102
target_database = "shop"
targets = [
    session["pid"]
    for session in sessions
    if session["database"] == target_database
    and session["pid"] != current_pid
]
print(targets)

[4101]

Cancel a query or terminate a session

pg_cancel_backend(pid) sends a cancellation request for the current query. The backend remains connected and may run another query. Use it when the query is the problem but the client session should continue.

pg_terminate_backend(pid) ends the selected backend session. The client is disconnected, and an open transaction is rolled back. Use it when the connection itself must end, such as clearing an abandoned session that is holding resources. A cancellation request may not stop work immediately, and a client may reconnect after termination if its software is configured to do so.

Terminate selected sessions safely with PostgreSQL functions

First run a read-only query to review candidate sessions, for example: SELECT pid, usename, application_name, state, query FROM pg_stat_activity WHERE datname = 'my_database' AND pid <> pg_backend_pid();. Confirm the owner and impact of each target, then pass one reviewed PID to pg_terminate_backend, such as SELECT pg_terminate_backend(4101); (replace 4101 with the PID you confirmed). This statement acts immediately: PostgreSQL signals that backend, disconnects its client, and rolls back any open transaction. Its Boolean result indicates whether PostgreSQL accepted the signal; check pg_stat_activity afterward to confirm the backend has disappeared. For multiple sessions, review the complete target set first and terminate only those confirmed PIDs individually or through an explicitly narrow condition whose targets you have already checked.

Check permissions and version differences

The ability to signal another backend depends on PostgreSQL version and granted privileges. Superusers can perform the operation; PostgreSQL also supports designated signaling privileges in versions that provide them. Do not assume every installation requires superuser access or that every ordinary role can terminate every session. Check the documentation for the server’s version and the privileges granted to your role.

pg_backend_pid() returns the PID for the current session and is the appropriate comparison for excluding yourself. Older releases used different column names in system views; do not substitute a legacy identifier such as procpid into a query without confirming that the target server’s version exposes it.

Verify the result and diagnose common failures

After calling pg_terminate_backend, query pg_stat_activity again using the same target filters. If the PID is no longer present, the backend has ended. If the function returns false or the row remains, check that the PID was current, that you have permission to signal it, and that the target was not already exiting. A client may establish a new connection with a different PID, so distinguish a reconnect from the original backend remaining alive.

A permission error points to the role’s privileges or a server-version rule. A successful signal can still take a short time to be observed, and the function’s Boolean reports signal handling rather than a complete application-level recovery. Check application logs and transaction effects when diagnosing the impact.

Avoid killing PostgreSQL processes directly

Tools such as ps can help an administrator inspect operating-system processes, but matching a process name does not safely identify a database session to terminate. Do not use kill or kill -9 as a routine way to disconnect a client. A direct process signal bypasses PostgreSQL’s normal session handling and can interrupt server work; forceful signals can create wider recovery problems. Use the PostgreSQL signaling functions for individual sessions.

When a server restart is appropriate

Restart the PostgreSQL service only when the server itself needs maintenance or recovery and you understand that every connected client may be disconnected. A restart is far broader than ending one backend. The command depends on how PostgreSQL was installed and managed: use the service manager or cluster-management tool configured on that host, and confirm the correct instance and data directory before acting. Homebrew service commands and pg_ctl apply only to matching installations and are not interchangeable universal commands. For a single unwanted connection, inspect and signal its backend from PostgreSQL instead.