Home > Mobile >  SELECT pg_reload_conf() not working after config changes using ALTER SYSTEM SET (postgresql 14)
SELECT pg_reload_conf() not working after config changes using ALTER SYSTEM SET (postgresql 14)

Time:10-30

In the past, when I've made some config changes (e.g. to increase max_connections) I have been able to make those changes take effect without restarting the db server by using SELECT pg_reload_conf()... as per the docs.

But now, it isn't working... I've recently upgraded to postgresql 14 so maybe that's it? Any ideas? The config change is making it's way into the postgresql.auto.conf file, and it does take effect after restarting the server, but I'd like to know why pg_reload_conf() isn't working.

Example flow below:

# SHOW max_connections;
 max_connections 
-----------------
 300
(1 row)

# ALTER SYSTEM SET max_connections = '301';
ALTER SYSTEM

# SHOW max_connections;
 max_connections 
-----------------
 300
(1 row)

# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

# SHOW max_connections;
 max_connections 
-----------------
 300
(1 row)


# more /var/lib/postgresql/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
shared_buffers = '2GB'
wal_buffers = '64MB'
work_mem = '8MB'
maintenance_work_mem = '128MB'
max_connections = '301'

CodePudding user response:

Quote from the manual

Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.

(emphasis mine)

So this is expected, max_connections can't be changed without a restart of the server.

  • Related