There are places in PostgreSQL where we can still see the old and new settings from postgresql.conf and postgresql.auto.conf. One is the pg_settings table which we can use to list all the settings from those two configuration files. Note that we tested this post with PostgreSQL 10.5.
PostgreSQL Settings From Configuration Files
PostgreSQL generally has two configuration files, and we can query their values using SQL. When PostgreSQL starts up for the first time, it loads configuration from several files. Two are postgresql.conf and postgresql.auto.conf.
List Settings from postgresql.conf and .auto.conf Using SQL
Once we have those settings, we can list them using an SQL select statement. Consider the following codes.
select name, context , unit, setting, boot_val, reset_val
where name IN (
order by context, name;
When we run it, we get the following result.
Alter PostgreSQL .conf and .auto.conf values
We can also alter the settings that PostgreSQL read from postgresql.conf and postgresql.auto.conf. However, the values may not be persistent, and PostgreSQL reverts the settings after a restart.
-- Change from 4096 to 5120
ALTER SYSTEM set work_mem = 5120;
If the context is equal to the postmaster, changing this parameter requires restarting the PostgreSQL service. If it’s similar to the user, changes require a reload to take effect globally. Note that restarting PostgreSQL terminates active connections.
Reloading after Changing PostgreSQL settings in .conf files
Alternatively, we could reload the changes without restarting PostgreSQL. We can reload List settings defined in postgresql.conf and overridden by postgresql.auto.conf.
When we recheck the settings, we will have the following:
Do you want to try it out? You can play around with PostgreSQL and its config files using a Docker container.