PostgreSQL postgresql.conf And postgresql.auto.conf

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
  from pg_settings 
 where name IN (
          'listen_addresses', 
          'port',
          'dynamic_shared_memory_type',
          'log_destination',
          'max_connections', 
          'shared_buffers', 
          'effective_cache_size', 
          'work_mem', 
          'maintenance_work_mem'
)
order by context, name;

select name, context , unit, setting, boot_val, reset_val

from pg_settings

where name IN (

'listen_addresses',

'port',

'dynamic_shared_memory_type',

'log_destination',

'max_connections',

'shared_buffers',

'effective_cache_size',

'work_mem',

'maintenance_work_mem'

)

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;

1 2	-- 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.