There are places in PostgreSQL where we can still see the old and new values simultaneously. One of them pg_settings table. This post shows how to list all the settings from postgresql.conf, and postgresql.auto.conf override. Note that we tested this post with PostgreSQL 10.5.
PostgreSQL Configration Files
When PostgreSQL starts up for the first time, it loads configuration from several files. Two of them are postgresql.conf, and postgresql.auto.conf.
List Settings 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 Lpostgresql.conf and postgresql.auto.conf settings
We can also alter the settings that PostgreSQL read from postgresql.conf, and postgresql.auto.conf.
-- 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 equal to the user, changes require a reload to take effect globally.
Note that restarting PostgreSQL terminates active connections.
Reloading after Changing PostgreSQL settings
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: