Search code examples
postgresqltimescaledb

Timescaledb drizzle connection - FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute

Im trying to connect to timescaledb, which has a session based pooler on top of, but I keep getting FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute

So I was looking at few places, got a bit confused by Pool from node-postgres and what is the actual usage of it.

Currently I have


import type { Connection } from './postgres.connection';
import type { Options } from 'postgres';

import postgres from 'postgres';

export const timescaleConnection = async <T extends Connection = Connection>(
  options?: Options<T>,
) => {
  const databaseUrl = process.env['TIMESCALE_DATABASE_URL'] ?? '';

  return postgres<T>(databaseUrl, {
    idle_timeout: 20, // 20 seconds
    ...options,
  });
};

What should I be doing? Using Pool or any other method of configuration?

Solution

  • The immediate fix is to increase the config setting max_connections. You can still connect as superuser (as indicated by the error message.)
    But the setting only takes effect after restarting the Postgres server - which, of course, closes all connections. A connection pooler will open new ones as soon as the server is back online.

    Or close some (idle?) connections.
    You can kill process on the server, or more elegantly, use pg_terminate_backend() from within.

    Get an overview with the system view pg_stat_activity.

    To properly fix, determine a sane maximum for the number of connections, and configure your connection pooler accordingly, i.e. well below the set maximum to leave some as reserve.

    See: