Reload PostgreSQL Configuration

PostgreSQL allows for many settings in their postgresql.conf file. Depending on your database you may need to tweak any of these settings to find the optimal performance or to work the way you want it in your environment. The easy way to apply any changes is just by restarting the database. But that would be counter productive, if your blog or application runs on PostgreSQL, then that would leave your users seeing an “Unable to connect to database.” error until the database starts up.

Luckily PostgreSQL has two options for applying changes made to the postgresql.conf without restarting.

1. With an SQL command

If you have a superuser credentials you can just execute the pg_reload_conf() function. This will apply any changes that have been made to the postgresql.conf.

2. With the terminal

It is possible to load changes done to the postgresql.conf file via the terminal. You will need to login as the postgres user or su into it if you have root access. Then execute the pg_ctl command with the reload parameter.

If your data folder is not in the default location, pg_ctl will complain

In that case you will need to give it the location. That is done by passing the -D flag followed b the location of the data folder

The current version of PostgreSQL I am using is 9.3.5. Not all versions of PostgreSQL offer the same settings, they may change over version numbers. Also not all settings are available to be changed on the fly. Some require that PostgreSQL be restarted to take effect. There are not many of these settings, but they are settings that you usually only set once. For example the ip and port number.

Below are all the settings available for PostgreSQL v9.3.5 on Centos 6.5 as returned by the SHOW ALL command. I have marked the ones that require a restart to take effect. All other can modified and applied either via the pg_reload_conf() function or the pg_ctl command.

 

Leave a Reply