PostgreSQL

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.

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.

Postgresql Settings postgresql.conf postgresql.auto.conf

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.

Postgresql Settings postgresql.conf postgresql.auto.conf

When we recheck the settings, we will have the following:

Postgresql Settings postgresql.conf postgresql.auto.conf

Do you want to try it out? You can play around with PostgreSQL and its config files using a Docker container.

Got comments or suggestions? We disabled the comments on this site to fight off spammers, but you can still contact us via our Facebook page!.


You Might Also Like