Comprehensive Guide to Terminating PostgreSQL Database Connections

Necessity of Database Connection Termination

In PostgreSQL database management, terminating database connections is a common yet critical operation. When performing database deletion, renaming, or maintenance tasks, it's essential to ensure no other active connections interfere with the operation. A typical scenario occurs when executing rake db:drop commands and encountering "database is being accessed by other users" errors, indicating other sessions are using the target database.

Core Termination Function: pg_terminate_backend

PostgreSQL provides the specialized system function pg_terminate_backend() for terminating database connections. This function requires superuser privileges to execute and works by sending termination signals to specified backend processes, forcibly closing connections and rolling back all related transactions.

Connection Identification and Filtering

Before executing connection termination operations, accurate identification of target connections is crucial. Querying the pg_stat_activity system view provides information about all active connections, including key fields such as process ID (pid), database name (datname), username (usename), and connection state (state).

Safe Termination of All Connections

To prevent misoperations, connection termination should follow specific filtering criteria. The following code demonstrates how to safely terminate all connections to a specified database while excluding the current session:

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- Avoid terminating own connection
    pid <> pg_backend_pid()
    -- Specify target database
    AND datname = 'database_name'
    -- Exclude parallel worker processes
    AND leader_pid IS NULL;

Version Compatibility Handling

Different PostgreSQL versions have variations in system view field naming. For PostgreSQL versions 8.4-9.1, the procpid field should be used instead of pid:

SELECT 
    pg_terminate_backend(procpid) 
FROM 
    pg_stat_activity 
WHERE 
    procpid <> pg_backend_pid()
    AND datname = 'database_name'
    AND leader_pid IS NULL;

Connection Permission Management

To prevent new connections from being established immediately after termination, it's recommended to revoke database connection permissions before executing termination operations:

REVOKE CONNECT ON DATABASE database_name FROM PUBLIC, username;

Connection State Filtering

Based on actual requirements, connection states can be further filtered. For example, terminating only active state connections:

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    datname = 'database_name'
    AND pid <> pg_backend_pid()
    AND leader_pid IS NULL
    AND state = 'active';

Function Comparison Analysis

PostgreSQL provides two connection control functions: pg_cancel_backend() and pg_terminate_backend(). The former cancels currently executing queries without terminating connections, while the latter completely terminates entire connection sessions. The choice between these functions depends on specific needs: use pg_cancel_backend() to stop long-running queries while keeping connections available; use pg_terminate_backend() to completely release connection resources.

Automated Management Solutions

For environments requiring regular connection cleanup, automated scripts can be created. The following example demonstrates how to terminate active connections running for more than 10 minutes:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT pid
        FROM pg_stat_activity
        WHERE state = 'active'
        AND now() - query_start > interval '10 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

Best Practice Recommendations

When executing connection termination operations, follow these best practices: first confirm target connection information through the pg_stat_activity view; then revoke relevant connection permissions to prevent new connections; execute termination operations with appropriate filtering criteria; finally verify operation results and restore necessary connection permissions. These steps ensure operational safety and effectiveness.

Error Handling and Debugging

Various error conditions may occur during connection termination processes. Common errors include insufficient privileges, non-existent processes, or operation denials. Through detailed logging and error handling mechanisms, these issues can be promptly identified and resolved. Thorough testing and verification before executing critical operations is recommended.