Enable Connection Pooler in local dev environment

[sp88011]

After a while, I'm getting this error in my local environment which I believe is related to there being too many open connections. When I restart Docker, everything works fine.

PostgresError: remaining connection slots are reserved for non-replication superuser connections at Socket.emit (node:events:519:28)

How can I enable connection pooling locally? Or is there another solution to my problem?

I followed the steps here to set up my local environment (https://supabase.com/docs/guides/cli/local-development) - but nowhere does it mention anything about connection pooling locally...

I found a docker imagine for supavisor, but honestly I don't know much about docker and how I would integrate this file into my current "stack" of docker images that are running once I do supabase init

[silentworks]

Are you using the Supabase CLI? if you are there should be a section for db.pooler in the config.toml file.

[shawnmclean]

PostgresError: Tenant or user not found

Any idea what else I need to do to connect to the db via the pooler?

My conn string looks like this: POSTGRES_URL="postgresql://postgres:postgres@localhost:54329/postgres"

All I did was swap the port. Is that correct?

[silentworks]

Yes that should do it. By any chance are you using Prisma or something like that?

[shawnmclean]

Drizzle ORM, thats throwing the PGError.

[m-shaka]

I used psql cli but got the same error: Tenant or user not found.

psql -h 0.0.0.0 -p 54329 -U postgres -W postgres

Here is my config

[db.pooler]
enabled = true
# Port to use for the local connection pooler.
port = 54329
# Specifies when a server connection can be reused by other clients.
# Configure one of the supported pooler modes: `transaction`, `session`.
pool_mode = "transaction"
# How many server connections to allow per user/database pair.
default_pool_size = 20
# Maximum number of client connections allowed.
max_client_conn = 100

[m-shaka]

@shawnmclean Hi, I found the correct connection string
supabase/cli#2440 (comment)

[sp88011]

Thank you very much! Exactly what I was looking for.

[smallStall]

This connection string works, but if you want RLS to work, you can switch the postgres user to the pgbouncer user.
postgresql://postgres.pooler-dev:postgres@localhost:54329/postgres
↓
postgresql://pgbouncer.pooler-dev:postgres@localhost:54329/postgres

The pgbouncer is then granted public etc.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to pgbouncer;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to pgbouncer;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to pgbouncer;

This hack should work as long as there is a pgbouncer in the initial _supervisor.users table.