Connection Management Issues and Solutions in PostgreSQL Database Deletion

Problem Background and Phenomenon Analysis

During PostgreSQL database management, users frequently encounter situations where database deletion fails with error messages: ERROR: database "pilot" is being accessed by other users, with detailed information indicating 1 other session is using the database. Even after using the pg_terminate_backend function to terminate existing connections, the system automatically establishes new connections, causing the deletion operation to fail.

Root Causes of Automatic Connection Issues

Automatic connections typically originate from several sources: application connection pool mechanisms, database monitoring tools, backup jobs, or other background processes. These components may be configured with automatic reconnection logic that immediately attempts to re-establish connections when disconnections are detected. In the referenced GitLab upgrade case, we observe similar connection issues, though in different contexts, both involving automated database connection management mechanisms.

Solutions for PostgreSQL 12 and Earlier Versions

For PostgreSQL 12 and earlier versions, the recommended approach combines permission control with connection termination:

First, revoke connection permissions for public users on the target database:

REVOKE CONNECT ON DATABASE thedb FROM public;

This operation prevents new connections from being established but does not affect existing connections. It's particularly important to check and potentially revoke connection permissions for other specific users or roles, which can be viewed using the \l+ command in psql.

Next, terminate all existing connections to the target database (excluding the current session):

SELECT pid, pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = current_database() AND pid <> pg_backend_pid();

In older PostgreSQL versions, the pid column might be named procpid, requiring corresponding query adjustments. This query returns the process ID of each terminated connection and the result of the termination operation.

Enhanced Solutions for PostgreSQL 13+

PostgreSQL 13 introduced the WITH (FORCE) option for the DROP DATABASE command, significantly simplifying the deletion of connected databases:

DROP DATABASE db_name WITH (FORCE);

This command automatically attempts to terminate all existing connections to the target database, but with the following limitations: it cannot terminate connections containing prepared transactions, active logical replication slots, or subscriptions, and cannot delete the currently connected database.

Complete Operational Workflow and Best Practices

Based on the best answer recommendations, the complete database deletion workflow is as follows:

1. Confirm current database status: Use \l+ command to view database list and connection information
2. Revoke connection permissions: Execute REVOKE CONNECT ON DATABASE target_db FROM public;
3. Terminate existing connections: Run connection termination query, ensuring current session is excluded
4. Perform deletion operation: Use DROP DATABASE target_db; command
5. Permission restoration (optional): If recreating the database, execute GRANT CONNECT ON DATABASE new_db TO public;

In practical operations, it's recommended to first validate the entire workflow in a test environment, especially when dealing with production systems. Additionally, pay attention to the特殊性 of superuser connections - if normal operations use superuser connections, this potential security issue may need to be addressed first.

Code Implementation and Example Analysis

The following complete Python script demonstrates how to safely delete a PostgreSQL database:

import psycopg2
from psycopg2 import sql

def safe_drop_database(host, port, user, password, dbname):
    # Connect to postgres database (cannot connect to database to be deleted)
    conn = psycopg2.connect(
        host=host,
        port=port,
        user=user,
        password=password,
        dbname="postgres"
    )
    conn.autocommit = True
    cursor = conn.cursor()
    
    try:
        # Revoke connection permissions
        revoke_query = sql.SQL("REVOKE CONNECT ON DATABASE {} FROM public;").format(
            sql.Identifier(dbname)
        )
        cursor.execute(revoke_query)
        
        # Terminate existing connections
        terminate_query = sql.SQL("""
            SELECT pg_terminate_backend(pid) 
            FROM pg_stat_activity 
            WHERE datname = {} AND pid <> pg_backend_pid();
        """).format(sql.Literal(dbname))
        cursor.execute(terminate_query)
        
        # Delete database
        drop_query = sql.SQL("DROP DATABASE {};").format(sql.Identifier(dbname))
        cursor.execute(drop_query)
        
        print(f"Database {dbname} successfully deleted")
        
    except Exception as e:
        print(f"Operation failed: {e}")
    finally:
        cursor.close()
        conn.close()

# Usage example
safe_drop_database("localhost", 5432, "postgres", "password", "target_db")

In-depth Technical Principles Analysis

PostgreSQL's connection management is based on its multi-process architecture. Each client connection corresponds to a backend process, with information about these processes stored in the pg_stat_activity system view. The pg_terminate_backend function terminates connections by sending SIGTERM signals to target processes, closely integrated with the operating system's process management mechanisms.

Regarding permission control, the REVOKE CONNECT operation modifies the database's ACL (Access Control List), preventing new connection attempts. This permission change takes effect immediately but doesn't affect established connections, hence the need to combine it with connection termination operations.

Practical Application Scenarios and Considerations

In actual operations work, this problem commonly occurs in the following scenarios:

• Database cleanup in continuous integration/continuous deployment pipelines
• Tenant database management in multi-tenant systems
• Test database maintenance in development environments
• Old database cleanup during database migration and upgrade processes

Important considerations include:

• Ensure complete data backups before operations
• Use connection termination operations cautiously in production environments
• Consider using transaction blocks to ensure operation atomicity
• Monitor system logs to identify sources of automatic connections

Through systematic methods and deep technical understanding, connection management issues during PostgreSQL database deletion can be effectively resolved, ensuring smooth database operations and maintenance.