Check & change the parameter setting in PostgreSQL database

Check the parameter setting in PostgreSQL

SELECT name, setting, boot_val, reset_val, unit
 FROM pg_settings
 ORDER BY name;

Check the parameter setting which need restart of the database server: F: False or T: True

select pending_restart from pg_settings where name = 'work_mem';
 pending_restart 
-----------------
 f
(1 row)

Change the parameter with Alter command in PostgreSQL:

ALTER SYSTEM SET configuration_parameter = 'Value';
Example:
ALTER SYSTEM SET wal_level = replica;

Reset the parameter value to default in PostgreSQL:

ALTER SYSTEM RESET configuration_parameter;
ALTER SYSTEM RESET ALL;

Example:
Reset one parameter:
ALTER SYSTEM SET wal_level = replica;

Reset all parameter:
ALTER SYSTEM RESET ALL;

Note: ALTER SYSTEM is used for changing server configuration parameters across the entire database cluster. It will modify the new postgresql.auto.conf file. It is the additional file maintained if you rest all parameter then it’s removed

Alter system effect parameter when server is restarted or server configuration reloaded. Reload can be done with pg_config_reload() and pg_ctl reload.