How to update the PostgreSQL config in the local environment?

[vinch]

Hello,

I tried to setup a local environment and copying my hosted database to my local environment but I encountered some kind of memory issues that you can see here when doing supabase db reset:

Resetting local database...
Recreating database...
Setting up initial schema...
Applying migration 20240813070343_remote_schema.sql...
ERROR: out of shared memory (SQLSTATE 53200)
At statement 843: SECURITY LABEL FOR "pgsodium" ON COLUMN "public"."abc"."cdf" IS 'ENCRYPT WITH KEY ID xyz'
Try rerunning the command with --debug to troubleshoot the error.

When I troubleshoot the issue with the --debug flag, it tells me to update the max_locks_per_transaction value in the PostgreSQL config. By default it's 64 and I would like to upgrade to 512 like I did on my hosted database.

On my hosted database, I was able to do it with the supabase postgres-config update command (documented here https://supabase.com/docs/guides/platform/custom-postgres-config) but it does not seem to work locally as it requires a project-ref that I don't have on my local environment.

So, I decided to go inside the docker instance for my database, find the postgresql.conf file located in /etc/postgresql but if I update the max_locks_per_transaction there and then restart the instance (by doing supabase stop then supabase start), my changes in the postgresql.conf file are erased.

So my question is: what's the best way to update the PostgreSQL config when using a local environment?

Thank you.

[omaiboroda]

@vinch , we have overcome this with

docker exec -it <container_name> bash -c "echo \"maintenance_work_mem = '128MB'\" >> /etc/postgresql/postgresql.conf && su postgres -c '/usr/lib/postgresql/15/bin/pg_ctl reload'"