pg-pool-cloudsql-reconnect

pg-pool CloudSQL Reconnection

Problem

Node.js applications using pg-pool with CloudSQL (or other managed PostgreSQL) fail to recover from connection resets. Retry logic appears to work but keeps failing with the same ECONNRESET error because the pool caches dead connections.

Context / Trigger Conditions

• ECONNRESET errors that persist despite retry logic
• Error messages like:
  • read ECONNRESET
  • Connection terminated
  • connection already closed
  • remaining connection slots are reserved for non-replication superuser connections
• High-concurrency applications (parallel batch processing)
• CloudSQL with small instance tiers (db-f1-micro, db-g1-small)
• Long-running scripts with periods of inactivity followed by bursts

Root Cause

pg-pool maintains a pool of database connections. When CloudSQL resets connections (due to timeout, maintenance, or connection limits), the pool still holds references to those dead connections. Simply retrying the operation uses the same dead connection from the pool.

Solution

Instead of just retrying, recreate the entire pool on connection reset errors:

export class PostgresDatabase {
  private pool: Pool;
  private config: PostgresConfig;

  constructor(config: PostgresConfig) {
    this.config = config;
    this.pool = this.createPool();
  }

  private createPool(): Pool {
    const pool = new Pool({
      ...this.config,
      max: 25,                          // Adequate for concurrency
      idleTimeoutMillis: 30000,         // Close idle connections
      connectionTimeoutMillis: 10000,   // Timeout for new connections
      keepAlive: true,                  // TCP keepalive
    });

    pool.on('error', (err) => {
      console.error('Pool error:', err.message);
    });

    return pool;
  }

  private async reconnect(): Promise<void> {
    console.log("Reconnecting to database...");
    try {
      await this.pool.end();
    } catch {
      // Ignore cleanup errors
    }
    this.pool = this.createPool();
    await this.pool.query("SELECT 1"); // Verify connection
    console.log("Database reconnected");
  }

  private async withRetry<T>(
    operation: () => Promise<T>,
    maxRetries = 3
  ): Promise<T> {
    for (let attempt = 0; attempt < maxRetries; attempt++) {
      try {
        return await operation();
      } catch (error) {
        const msg = error instanceof Error ? error.message : String(error);
        const needsReconnect =
          msg.includes("ECONNRESET") ||
          msg.includes("Connection terminated") ||
          msg.includes("connection already closed") ||
          msg.includes("remaining connection slots");

        if (!needsReconnect || attempt === maxRetries - 1) {
          throw error;
        }

        const delay = 1000 * Math.pow(2, attempt);
        console.log(`Connection error, reconnecting in ${delay}ms...`);
        await new Promise(r => setTimeout(r, delay));

        // KEY: Recreate the pool, don't just retry
        await this.reconnect();
      }
    }
    throw new Error("Max retries exceeded");
  }
}